MySQL Bugs: #50136: Union execution order wrong ?

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

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) ;

What exact version of MySQL server, x.y.z, do you use and what results do you get?

I get this result on 5.1.15

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

But I think, result should have 3 tuples.

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. "

Noted in 5.1.69, 5.5.31, 5.6.11, 5.7.1 changelogs.

UNION ALL on BLOB columns could produce incorrect results.