Bug #20302 Union all is not 'streaming'
Submitted: 6 Jun 2006 18:15 Modified: 31 Mar 2009 18:19
Reporter: Andre Timmer Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S4 (Feature request)
Version:5.1.12, 5.0.23-BK, 5.0.18 OS:Any
Assigned to: CPU Architecture:Any
Triage: Needs Triage: D5 (Feature request)

[6 Jun 2006 18:15] Andre Timmer
Description:
Select in 'How to repeat' should gives rows back immediately.
It doesn't, it looks like virtual table aa is build first.'

When the word 'union' was used this would be correct.
For 'union all' this is not, or at least not necessairy.

How to repeat:
select 
        *
from   (
         select * from tab1
         union all
         select * from tab1
         ) aa
limit 10;

No testdata supplied, you can use some hugh table you probably have for this.

Suggested fix:
In case of 'union all' the results of the virtual table should produce rows immediately.
[7 Jun 2006 10:04] Valeriy Kravchuk
Thank you for a reasonable feature request. Even in 5.0.23 we have:

mysql> EXPLAIN EXTENDED select * from ( select * from t1 union all select * from t1) aa limit 5;
+----+--------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type  | table      | type | possible_keys | key  | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+-------+
|  1 | PRIMARY      | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |   12 |       |
|  2 | DERIVED      | t1         | ALL  | NULL          | NULL | NULL    | NULL |    6 |       |
|  3 | UNION        | t1         | ALL  | NULL          | NULL | NULL    | NULL |    6 |       |
|    | UNION RESULT | <union2,3> | ALL  | NULL          | NULL | NULL    | NULL
| NULL |       |
+----+--------------+------------+------+---------------+------+---------+------+------+-------+
4 rows in set, 1 warning (0.00 sec)

So, it looks like derived table is built first, completely. It would be nice to get results by scanning first table until LIMIT is reached, and scan second one only if it is needed. In this (unusual) case (LIMIT without ORDER BY).
[17 Aug 2007 22:56] Igor Babaev
- This problem will be resolved when WL#3485 is implemented (not started yet).

By the above reason I move the bug to 'To be fixed later' and mark it as a 'Feature request'.
Product management will decide in what version a fix for this problem appears.
[4 Nov 2008 18:41] Valeriy Kravchuk
WL#3485 should be implemented in 6.0.x. Is this problem fixed there already?
[29 Mar 2011 18:21] Roberto Spadim
bug 60691 duplicated of this bug 20302
the problem continue in mysql 5.5