[backport] Implement UNION ALL without usage of a temporary table (MWL#44)

Currently when any union operation is executed the rows received from its
operands are always sent to a temporary table. Meanwhile for a UNION ALL
operation that is used at the top level of a query without an ORDER BY clause it
is not necessary. In this case the rows could be sent directly to the client.
The goal of this task is to provide such an implementation of UNION ALL
operation that would not use temporary table at all in certain, most usable cases.

Specification

1. Handling union operations in MySQL Server

  1.1. Specifics of MySQL union operations

  1.2 Validation of union units

  1.3 Execution of union units

2. Optimizations improving performance of  UNION ALL operations 

  2.1 Execution of UNION ALL without temporary table

  2.2. Avoiding unnecessary copying

  2.3 Optimizing execution of a union unit with a mix of UNION/UNION ALL

3. Other possible optimizations for union units

1. Handling union operations in MySQL Server

1.1. Specifics of MySQL union operations

UNION and UNION ALL are the only set operations supported by MySQL Server. MySQL
allows us to use these operations in a sequence, one after another. For example
the following queries are accepted by the MySQL Server:

  (select a1,b1,c1 from t1 where a1=b1) union

    (select a2,b2,c2 from t2 where a2!=b2) union 

      (select a3,b3,c3 from t3 where a3>b3);                        (1)

  (select a1,b1,c1 from t1 where a1=b1) union all

    (select a2,b2,c2 from t2 where a2!=b2) union all 

      (select a3,b3,c3 from t3 where a3>b3);                        (2)

Any mix of UNION and UNION ALL is also acceptable:

  (select a1,b1,c3 from t1 where a1=b1) union

    (select a2,b2,c3 from t2 where a2!=b2) union all 

      (select a3,b3,c3 from t3 where a3>b3);                        (3)

  (select a1,b1,c1 from t1 where a1=b1) union all

    (select a2,b2,c2 from t2 where a2!=b2) union  

      (select a3,b3,c3 from t3 where a3>b3);                        (4)

It should be noted that query (4) is equivalent to query (1). At the same time
query (3) is not equivalent to any of the queries (1),(2),(4).
In general any UNION ALL in a sequence of union operations can be equivalently
substituted for UNION if there occur another UNION further in the sequence.
MySQL does not accept nested unions. For example the following valid SQL query
is considered by MySQL Server as erroneous:

 ((select a1,b1 from t1 where a1=b1) union (select a2,b2 from t2 where a2!=b2))  

 union all 

 ((select a3,b3 from t3 where a3=b3) union (select a4,b4 from t4 where a4!=b4))

A sequence of select constructs separated by UNION/UNION ALL is called 'union
unit' if it s not a part of another such sequence.
A union unit can be executed as a query. It also can be used as a subquery.
A union unit can be optionally appended by an ORDER BY and/or LIMIT construct.
In this case it cannot be used as a subquery.

1.2 Validation of union units

When the parser stage is over the further processing of a union unit is
performed by the function mysql_union.
The function first validate the unit in the method SELECT_LEX_UNIT::prepare.
The method first validates each of the select constructs of the unit and then it
checks that all select are compatible. The method checks that the selects return
the same number of columns and for each set of columns with the same number k
there is a type to which the types of the columns can be coerced. This type is
considered as the type of column k of the result set returned by the union unit.
For example, if in the query (1) the columns b1, b2 and b3 are of the types int,
bigint and double respectively then the second column of the union unit will be
of the type double. If the types of the columns c1,c2,c3 are specified as
varchar(10), varchar(20), varchar(10) then the type of the corresponding column
of the result set will be varchar(20). If the columns have different collations
then a collation from which all these collations can be derived is looked for
and it is assigned as the
collation of the third column in the result set.
After compatibility of the corresponding select columns has been checked and the
types of the columns from of the result set have been determined the method
SELECT_LEX_UNIT::prepare creates a temporary table to store the rows of the
result set for the union unit. Currently rows returned by the selects from the
union unit are always written into a temporary table. To force selects to send
rows to this temporary table SELECT_LEX_UNIT::prepare creates JOIN objects for
the selects such that the JOIN::result field refers to an object of the class
select_union. All selects from a union unit share the same select_union object.

1.3 Execution of union units

After SELECT_LEX_UNIT::prepare has successfully validated the union unit, has
created a temporary table as a container for rows from the result sets returned
by the selects of the unit, and has prepared all data structures needed for
execution, the function mysql_union invokes SELECT_LEX_UNIT::exec.
The method SELECT_LEX_UNIT::exec processes the selects from the union unit one
by one.
Each select first is optimized with JOIN::optimize(), then it's executed with
JOIN::exec().The result rows from each select are sent to a temporary table.
This table accumulates all rows that are to be returned by the union unit. For
UNION operations duplicate rows are not added, for UNION ALL operations all
records are added. It is achieved by enabling and disabling usage of the unique
index defined on all fields of the temporary table. The index is never used if
only UINION ALL operation occurs in the unit. Otherwise it is enabled before
the first select is executed and disabled after the last UNION operation.
To send rows to the temporary table the method select_union::send_data is used.
For a row it receives from the currently executed select the method first stores
the fields of the row in in the fields of the record buffer of the temporary
table. To do this the method calls function fill_record. All needed type
conversions of the field values are performed when they are stored the record
buffer. After this the method select_union::send_data calls the ha_write_row
handler function to write the record from the buffer to the temporary table. A
possible error on duplicate key that occurs with an attempt to write a duplicate
row is ignored.
After all rows received from all selects have been placed into the temporary
table the method SELECT_LEX_UNIT::exec calls mysql_select that reads rows
from the temporary table and sends them to the output stream (to the client). If
there is an ORDER BY clause to be applied to result of the union unit then the
rows read from the temporary table have to be sorted first.

2. Optimizations improving performance of UNION ALL operations

The following three optimizations are proposed to be implemented in the
framework of this task.

2.1 Execution of UNION ALL without temporary table

If a union unit with only UNION ALL operations is used at the top level of the
query (in other words it's not used as a subquery) and is not appended with an
ORDER BY clause then it does not make sense to send rows received from selects
to a temporary table at all. After all needed type conversions have been done
the row fields could be sent directly into the output stream. It would improve
the performance of UNION ALL operations since writing to the temporary table and
reading from it would not be needed anymore. In the cases when the result set is
big enough and the temporary table cannot be allocated in the main memory the
performance gains would be significant. Besides, the client could get the first
result rows at once as it would not have to wait until all selects have been
executed.
To make an UNION ALL operation not to send rows to a temporary table we could
provide the JOIN objects created for the selects from the union unit with an
interceptor object that differs from the one they use now. In the current code
they use an object of the class select_union derived from the
select_result_interceptor class. The new interceptor object of the class that
we'll call select_union_send (by analogy with the class select_send) shall
inherit from the select_union and shall have its own implementations of the
virtual methods send_data, send_fields, and send_eof.
The method send_data shall send fields received from selects to the record
buffer of the temporary table and then from this buffer to the output stream.
The method send_fields shall send the format of the rows to the client before it
starts getting records from the first select , while the method send_eof shall
signal about the end of the rows after the last select finishes sending records.
The method create_result_table of the class select_union shall be re-defined
as virtual. The implementation of this method for the class select_union_send
shall call select_union::create_result_table and then shall build internal
structures needed for select_unionsend::send_data. So, the definition of the
class select_union_send should look like this:

  class select_union_send :public select_union

  {

    ...                  // private structures

  public:

    select_union_send() :select_union(), ...{...}

    bool send_data(List<Item> &items);

    bool send_fields(List<Item> &list, uint flags);

    bool create_result_table(THD *thd, List<Item> *column_types,

                                   bool is_distinct, ulonglong options,

                                   const char *alias); 

  };

2.2. Avoiding unnecessary copying

If a field does not need type conversion it does not make sense to send it to a
record buffer. It can be sent directly to the output stream. Different selects
can require type conversions for different columns.
Let's provide each select from the union unit with a data structure (e.g. a
bitmap) that says what fields require conversions, and what don't . Before
execution of a select this data structure must be passed to the
select_union_send object shared by all selects from the unit. The info in this
structure will tell select_union_send::send_data what fields should be sent to
the record buffer for type conversion and what can be sent directly to the
output stream. In this case another variant of the fill_record procedure is
needed that would take as parameter the info that says what fields are to be
stored in the record buffer.

2.3 Optimizing execution of a union unit with a mix of UNION/UNION ALL

If a union unit with a mix of UNIIN/UNION ALL operations and without ORDER BY is
used at the top level of a query then any UNION ALL operation after the last
UNION operation can be executed in more efficient way than it's done in the
current implementation. More exactly, the rows from any select that follows
after the second operand of the last UNION operations could be sent directly to
the output stream. In this case two interceptor objects have to be created: one,
of the type select_union, is shared by the selects for which UNION operations
are performed, another, of the type select_union_send, is shared by the the
remaining selects. For this optimization the method SELECT_LEX_UNIT::exec is to
undergo a serious re-work.

3. Other possible optimizations for union units

The following optimizations are not supposed to be implemented in the framework
this task.
1. For a union unit containing only UNION ALL with an ORDER BY send rows from
selects directly to the sorting procedure.
2. For a union unit at the top level of the query without ORDER BY clause send
any row received from an operand of a UNION operation directly to the output
stream as soon as it has been checked by a lookup in the temporary table that
it's not a duplicate.
3. Not to use temporary table for any union unit used in EXIST or IN subquery.