Bug #6565 Views: Mix of UNION ALL and UNION DISTINCT creates different view
Submitted: 11 Nov 2004 1:13 Modified: 25 Feb 2005 19:57
Reporter: Shuichi Tamagawa Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:Linux (SuSE Linux 9.0)
Assigned to: Oleksandr Byelkin

[11 Nov 2004 1:13] Shuichi Tamagawa
Description:
If you create a view based on a query in which UNION DISTINCT and UNION ALL  are mixed, the created view is different from the result of original query.

How to repeat:
mysql> create table t1(a int);
Query OK, 0 rows affected (0.13 sec)

mysql> create table t2(a int);
Query OK, 0 rows affected (0.14 sec)

mysql> create table t3(a int);
Query OK, 0 rows affected (0.10 sec)

mysql> insert into t1 values(1),(1);
Query OK, 2 row affected (0.00 sec)

mysql> insert into t2 values(2),(2);
Query OK, 2 row affected (0.00 sec)

mysql> insert into t3 values(3),(3);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t1 union distinct select * from t2 union all select * from t3;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    3 |
+------+
4 rows in set (0.00 sec)

mysql> create view v1 as select * from t1 union distinct select * from t2 union all select * from t3;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from v1;
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.01 sec)

Suggested fix:
The result should be
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    3 |
+------+
[11 Nov 2004 3:20] Miguel Solorzano
Verified on latest BK source.
[13 Feb 2005 23:13] Oleksandr Byelkin
It is 4.1 bug of subqueries in the FROM clause:
--- 1,18 ----
+ create table t1(a int);
+ create table t2(a int);
+ create table t3(a int);
+ insert into t1 values(1),(1);
+ insert into t2 values(2),(2);
+ insert into t3 values(3),(3);
+ select * from t1 union distinct select * from t2 union all select * from t3;
+ a
+ 1
+ 2
+ 3
+ 3
+ select * from (select * from t1 union distinct select * from t2 union all
+ select * from t3) X;
+ a
+ 1
+ 2
+ 3
-------------------------------------------------------
[14 Feb 2005 0:07] Oleksandr Byelkin
ChangeSet
  1.2183 05/02/14 02:06:21 bell@sanja.is.com.ua +3 -0
  removed wrong distinct UNION detection (BUG#6565)
[23 Feb 2005 14:15] Oleksandr Byelkin
Thank you for bugreport!
Bugfix is pushed to 4.1.11/5.0.3 source trees.
[25 Feb 2005 19:57] Paul Dubois
Noted in 4.1.11, 5.0.3 changelogs.