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
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1
• OS: Linux (SuSE Linux 9.0)
• Assigned to: Oleksandr Byelkin
• CPU Architecture: Any

[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] MySQL Verification Team

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 [e-mail] +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.