Bug #92994 please evaluate unions lazily
Submitted: 29 Oct 2018 18:17 Modified: 30 Oct 2018 13:55
Reporter: Domas Mituzas Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:* OS:Any
Assigned to: CPU Architecture:Any

[29 Oct 2018 18:17] Domas Mituzas
Description:
( SELECT ... LIMIT 10000)
UNION ALL
( SELECT ... LIMIT 10000)
LIMIT 1000

will materialize both unions fully, even if first one fully satisfies outer LIMIT

please use lazier execution for it. 

How to repeat:
mysql> explain ( select *, 'a' from t1 limit 1000 ) UNION ALL ( select *, 'b' from t1 limit 10000 ) limit 10
    -> ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 522614 |   100.00 | NULL  |
|  2 | UNION       | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 522614 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> ( select *, 'a' from t1 limit 1000 ) UNION ALL ( select *, 'b' from t1 limit 10000 ) limit 10
    -> ;
+----+------+------+------+---+
| a  | b    | c    | d    | a |
+----+------+------+------+---+
|  1 |    0 |    1 | NULL | a |
|  2 |    0 |    2 | NULL | a |
|  3 |    0 |    3 | NULL | a |
|  4 |    0 |    4 | NULL | a |
|  6 |    0 |    6 | NULL | a |
|  7 |    0 |    7 | NULL | a |
|  8 |    0 |    8 | NULL | a |
|  9 |    0 |    9 | NULL | a |
| 13 |    0 |   13 | NULL | a |
| 14 |    0 |   14 | NULL | a |
+----+------+------+------+---+
10 rows in set (0.01 sec)

mysql> show status like '%handler%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 2     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 8     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 2     |
| Handler_read_key           | 2     |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 11000 |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.01 sec)

Suggested fix:
evaluate unions lazily.
[29 Oct 2018 18:18] Domas Mituzas
this is a followup to https://bugs.mysql.com/bug.php?id=79040 too
[30 Oct 2018 9:03] Guilhem Bichot
Duplicate of https://bugs.mysql.com/bug.php?id=79340
"UNION ALL ... LIMIT 1 evaluates too many rows in 5.5/6 and twice more in 5.7"
?
[30 Oct 2018 13:55] Sinisa Milivojevic
Hi Domas,

Actually, this was the optimisation that we discussed back in Prague. I think, back in 2006, but it was never implemented.

This is a fully valid feature request and I am verifying it as such.