Add rudimentary `UNION` support to the `QueryBuilder`

[sbuerk]

Q	A
Type	feature
Fixed issues	#6368

Summary

The UNION operator is used to combine the result-set of two or more SELECT statements, which all database vendors supports with usual specialities for each.

Still, there is a common shared subset which works for all of them:

    SELECT column_name(s) FROM table1
    WHERE ...

    UNION <ALL | DISTINCT>

    SELECT column_name(s) FROM table2
    WHERE ...

    ORDER BY ...
    LIMIT x OFFSET y

with shared common requirements:

• Each SELECT must return the same fields in number, naming and order.

• Each SELECT must not have ORDER BY, expect MySQL allowing it to be used as sub query expression encapsulated in parentheses.

Taking the shared requirements and working behaviour, it is possible to provide a generic support to the QueryBuilder with a minimalistic surface addition now, and following methods are added:

• union(string|QueryBuilder ...$unionParts) and addUnion(string|QueryBuilder ...$unionParts) to create a UNION query retrieving unique rows
• unionAll(string|QueryBuilder ...$unionParts) and addUnionAll(string|QueryBuilder ...$unionParts) to create a UNION ALL query retrieving eventually duplicated rows.

This follows the generic logic as select(...) and addSelect(...) along with introducing new internal QueryType::UNION_DISTINCT and QueryType::UNION_ALL enum cases.

Additional to the consideration to allow SQL strings and QueryBuilder for union(), unionAll(), addUnion() and addUnionAll() and minimize the direct handling of miss-configuration to the number of provided parts and let other issues like the field (order, count, naming) or not allowed order by handling to the database itself. With that, vendor specific implementation can be done if required.

Technically, the SQL build process is dispatched to a DefaultUnionSQLBuilder along with an UnionSQLBuilder interface, which also allows application to implement custom behaviour if required.

Example:

$platform = $connection->getDatabasePlatform();
$qb       = $>connection->createQueryBuilder();
$select10 = $platform->getDummySelectSQL('2 as field_one');
$select20 = $platform->getDummySelectSQL('1 as field_one');
$qb->union($select10,    /*$select11, $select12, ... */)
   ->addUnion($select20, /*$select21, $select22, ... */)
   ->setMaxResults(1)
   ->setFirstResult(1)
   ->orderBy('field_one', 'ASC');
$rows = $qb->executeQuery()->fetchAllAssociative();

Unit and functional tests are added to demonstrate the implementation and cover it for future changes.

Resolves: #6368