Bug #50136 Union execution order wrong ?
Submitted: 7 Jan 2010 11:29 Modified: 18 Jan 2013 17:16
Reporter: Prafulla Tekawade Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.1.15, 5.1.43-bzr OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[7 Jan 2010 11:29] Prafulla Tekawade
Description:
In the following example,
Mysql seems to be executing union all before union distinct
which looks wrong
The query should be executed by doing union distinct first and then
union all with other sub-query. 

How to repeat:

create table t(s varchar(8000) COLLATE latin1_bin NOT NULL DEFAULT '&');
insert into t values("prafulla");
insert into t values("is");
insert into t values("good");
(SELECT s from t
group by s
order by s
limit 2)
UNION distinct
(SELECT s from t
group by s
order by s
limit 1)
UNION ALL 
(SELECT s from t
group by s
order by s
limit 1) ;
[7 Jan 2010 12:07] Valeriy Kravchuk
What exact version of MySQL server, x.y.z, do you use and what results do you get?
[7 Jan 2010 12:38] Prafulla Tekawade
I get this result on 5.1.15

+------+
| s    |
+------+
| good | 
| is   | 
+------+

But I think, result should have 3 tuples.
[7 Jan 2010 13:46] Valeriy Kravchuk
Indeed, even in the latest 5.1.43 we have:

mysql> (SELECT s from t group by s order by s limit 1);+------+| s    |
+------+
| good |
+------+
1 row in set (0.01 sec)

mysql> (SELECT s from t group by s order by s limit 2) UNION distinct (SELECT s from t group by s order by s limit 1);+------+
| s    |
+------+
| good |
| is   |
+------+
2 rows in set (0.01 sec)

mysql> (SELECT s from t group by s order by s limit 2) UNION distinct (SELECT s from t group by s order by s limit 1) UNION ALL  (SELECT s from t group by s order by s limit 1);
+------+
| s    |
+------+
| good |
| is   |
+------+
2 rows in set (0.02 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.1.43-debug |
+--------------+
1 row in set (0.00 sec)

That is, DISTINCT is applied "last" or also to the UNION ALL that is right to it, while our manual (http://dev.mysql.com/doc/refman/5.1/en/union.html) clearly says:

"You can mix UNION ALL and UNION DISTINCT in the same query. Mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. "
[18 Jan 2013 17:16] Paul Dubois
Noted in 5.1.69, 5.5.31, 5.6.11, 5.7.1 changelogs.

UNION ALL on BLOB columns could produce incorrect results.