Bug #32051 UNION within EXISTS returns incorrect result
Submitted: 2 Nov 2007 12:36 Modified: 9 Jan 15:24
Reporter: Chris Bacon
Status: Closed
Category:Server Severity:S3 (Non-critical)
Version:5.0.45, 5.1.22 OS:Microsoft Windows (XP)
Assigned to: Georgi Kodinov Target Version:
Tags: bad result, exists, UNION
Triage: D2 (Serious)

[2 Nov 2007 12: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 15:10] Hartmut Holzgraefe 
mysqltest test case

Attachment: bug32051.tgz (application/x-gtar, text), 807 bytes.
[26 Nov 2007 15:09] Georgi Kodinov 
Fixed by the fix for bug #32036
[9 Jan 15: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.