Bug #32051 UNION within EXISTS returns incorrect result
Submitted: 2 Nov 2007 11:36 Modified: 9 Jan 2008 14:24
Reporter: Chris Bacon Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.45, 5.1.22 OS:Windows (XP)
Assigned to: Georgi Kodinov CPU Architecture:Any
Tags: bad result, exists, UNION

[2 Nov 2007 11:36] Chris Bacon
Description:
When a UNION is directly inside an EXISTS clause, only the first table referenced in the UNION contributes to the result.

How to repeat:
CREATE TABLE `t0` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `t1` (
  `a` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT t0(a) VALUES(1);

SELECT EXISTS((
SELECT * FROM t1
) UNION ALL (
SELECT * FROM t0
));

The result is 0, when it should be 1.

If the two tables in the union are swapped, it correctly returns 1
[2 Nov 2007 14:10] Hartmut Holzgraefe
mysqltest test case

Attachment: bug32051.tgz (application/x-gtar, text), 807 bytes.

[26 Nov 2007 14:09] Georgi Kodinov
Fixed by the fix for bug #32036
[9 Jan 2008 14:24] Paul DuBois
Noted in 5.0.54, 5.1.23, 6.0.5 changelogs.

Within a subquery, UNION was handled differently than at the top 
level, which could result in incorrect results or a server crash.