"pushdown" of MIN/MAX inside UNION

There is min/max optimization in case of single SELECT, i.e:

SELECT MAX(field) FROM table;

We check only index for max (or min) value.

But if we have (probably via VIEW)

SELECT MAX(field) FROM

   (SELECT field1 as field FROM table1

    UNION

    SELECT field2  FROM table2);

we get all tables in temporary table than scan it for max (min).

If we transform the above query to :

SELECT MAX(field) FROM

  (SELECT MAX(field1) as field FROM table1

   UNION

   SELECT MAX(field2)  FROM table2);

optimisation will be still possible.

The idea of optimization is to push MIN/MAX into each part of the UNION if possible (It might not be possible for some SELECTs in the UNION)

Optimization is not possible in case:

• underlying SELECT has:
  • GROUP BY
  • HAVING
  • ORDER BY/LIMIT
  • aggregate functions
  • non numeric type of the MAX/MIN field
• outer SELECT has:
  • WHERE (see MENT-694)
  • GROUP BY (see MENT-694)
  • EXCEPT/INTERSECT in addition to UNIONs
  • Only one "derived" table with the UNION

Also possible such transformation for COUNT (only for ALL UNION):

SELECT COUNT(*) FROM

   (SELECT field1 as field FROM table1

    UNION ALL

    SELECT field2  FROM table2);

to

SELECT SUM(count) FROM

   (SELECT COUNT(*) as count  FROM table1

    UNION ALL

    SELECT COUNT(*)  FROM table2);