Bug #3588 unequal where clauses mix union

• Submitted: 28 Apr 2004 13:18
• Modified: 28 Apr 2004 16:49
• Reporter: Joonas Kekoni
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: Ver 14.3 Distrib 4.1.1-alpha
• OS: pc-linux x86
• Assigned to: Sergei Golubchik
• CPU Architecture: Any

[28 Apr 2004 13:18] Joonas Kekoni

Description:
If unioned selects have different fields in where part that are not part of the from the union does not work correctly.

How to repeat:
drop table jk_delme ;
create table jk_delme (a int, b int,c int );
insert into jk_delme ( 1 , 1 , 1 );
insert into jk_delme ( 2 , 2 , 2 );
insert into jk_delme ( 3 , 3 , 3 );
( select a from jk_delme where b=1 ) union  ( select a from jk_delme where c=2 ) ;
 a
 --
 2
( select a,b,c from jk_delme where b=1 ) union ( select a,b,c from jk_delme where c=2 ) ;
 a,b,c
 -----
 1 1 1
 2 2 2

( select a, 0 as b, 0 as c from jk_delme where b=1 ) union all ( select a,  0 as b, 0 as c from jk_delme where c=2 ) ;

 a,b,c
 -----
 2 0 0

( select a from jk_delme where b=1 and c=c ) union all ( select a  from jk_delme where c=2 and b=b ) ;
 a
 -
 1
 2

Suggested fix:
fields not listed in select should not be part of union criteria.

[28 Apr 2004 16:49] Sergei Golubchik

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

works perfectly for me using the development tree:

mysql> ( select a from jk_delme where b=1 ) union  ( select a from jk_delme where c=2);
+------+
| a    |
+------+
|    1 |
|    2 |
+------+

Probably the bug was fixed since 4.1.1 release - bugfix will come with 4.1.2 then