MySQL Bugs: #10624: Views with multiple UNION and UNION ALL produce incorrect results

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

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)

Thank you for the bug report.

st_select_lex_unit::print() was losing UNION ALL if in statement were present
UNION DISTINCT.

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

Fix will go into 5.0.13, cset 1.1977.10.1

Fix will be in 5.0.12, not in 5.0.13.

Documented in 5.0.12 changelog:

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