Bug #10624 Views with multiple UNION and UNION ALL produce incorrect results
Submitted: 13 May 2005 15:32 Modified: 18 Aug 2005 21:03
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.6-beta-debug OS:Linux (SUSE 9.2)
Assigned to: Evgeny Potemkin CPU Architecture:Any

[13 May 2005 15:32] Peter Gulutzan
Description:
For the series x UNION y UNION ALL z, I get the correct result when I don't use a view.
I get the wrong result if I do use a view.
 

How to repeat:
mysql> create table tm1 (s1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table tm2 (s1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tm1 values (1);
Query OK, 1 row affected (0.01 sec)

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

mysql> select * from tm1 union select * from tm2 union all select * from tm2;
+------+
| s1   |
+------+
|    1 |
|    2 |
|    2 |
+------+
3 rows in set (0.01 sec)

mysql> create view vm1 as select * from tm1 union select * from tm2 union all select * from tm2;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from vm1;
+------+
| s1   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)
[13 May 2005 15:40] Miguel Solorzano
Thank you for the bug report.
[15 Aug 2005 18:42] Evgeny Potemkin
st_select_lex_unit::print() was losing UNION ALL if in statement were present
UNION DISTINCT.
[15 Aug 2005 19:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28301
[18 Aug 2005 19:36] Evgeny Potemkin
Fix will go into 5.0.13, cset 1.1977.10.1
[18 Aug 2005 19:48] Evgeny Potemkin
Fix will be in 5.0.12, not in 5.0.13.
[18 Aug 2005 21:03] Mike Hillyer
Documented in 5.0.12 changelog:

<listitem><para>Views with multiple <literal>UNION</literal> and <literal>UNION ALL</literal> produced incorrect results.</para></listitem>