Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.0.4, 5.5.32, 5.3.12
-
None
-
None
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
|