Details
Minor
Description
The first test case returns 1 row on 5.3, 5.5, 10.0, and two rows on 5.2, MySQL 5.5, MySQL 5.6. Two rows is the correct result.
CREATE TABLE t1 (a INT, c1 VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES (2,'x'),(8,'d'); CREATE TABLE t2 (m INT, n INT, c2 VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES (0, 5, 'x'),(1, 4,'p'); SELECT * FROM t1 WHERE c1 NOT IN ( SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 0 ) AND ( t2b.m != a OR t2b.m = t2a.m ) );
Actual result:
a c1 8 d
Expected result:
a c1 2 x 8 d
The second test case is a variation of the first one, only instead of the constant '0' condition we use IN subquery from a constant (empty) table. This test case returns the correct result on 5.3, but fails on 5.5.
CREATE TABLE t1 (a INT, c1 VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t1 VALUES (2,'x'),(8,'d'); CREATE TABLE t2 (m INT, n INT, c2 VARCHAR(1)) ENGINE=MyISAM; INSERT INTO t2 VALUES (0, 5, 'x'),(1, 4,'p'); CREATE TABLE t3 (i INT) ENGINE=MyISAM; SELECT * FROM t1 WHERE c1 NOT IN ( SELECT t2a.c2 FROM t2 AS t2a, t2 AS t2b, t2 AS t2c WHERE t2c.c2 = t2b.c2 AND ( t2a.m = t2b.n OR 1 IN ( SELECT i FROM t3 ) ) AND ( t2b.m != a OR t2b.m = t2a.m ) );
5.3 result:
a c1 2 x 8 d
5.5 result:
a c1 8 d
bzr version-info revision-id: igor@askmonty.org-20130830040242-tns2ir3y9rht6n19 revno: 3687 branch-nick: 5.3
bzr version-info revision-id: psergey@askmonty.org-20130903144107-csv06zecpkp27oj0 revno: 3876 branch-nick: 5.5
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
The fix for the bug was pushed into the 5.3 tree and merged into the 5.5 tree.