diff options
Diffstat (limited to 'mysql-test/t/subselect3.test')
| -rw-r--r-- | mysql-test/t/subselect3.test | 22 |
1 files changed, 20 insertions, 2 deletions
diff --git a/mysql-test/t/subselect3.test b/mysql-test/t/subselect3.test index 65556012588..2f844c9cc21 100644 --- a/mysql-test/t/subselect3.test +++ b/mysql-test/t/subselect3.test @@ -260,11 +260,11 @@ insert into t2 values ('dd', 1, NULL); alter table t1 add index idx(ie1,ie2); ---cc 3 NULL NULL +# cc 3 NULL NULL select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ; insert into t2 values ('new1', 10,10); insert into t1 values ('new1', 1234, 10, NULL); --- new1, 10, 10, NULL, +# new1, 10, 10, NULL, select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; explain extended select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10; @@ -571,3 +571,21 @@ SELECT (t1.id IN (SELECT t2.id FROM t2,t3 FROM t1; DROP TABLE t1,t2,t3; + +# +# Bug #22855: Optimizer doesn't rewrite NOT IN subselects to a correlated +# subquery +# +CREATE TABLE t1 (a INT NOT NULL); +INSERT INTO t1 VALUES (1),(-1), (65),(66); + +CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY); +INSERT INTO t2 VALUES (65),(66); + +SELECT a FROM t1 WHERE a NOT IN (65,66); +SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); +EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2); + +DROP TABLE t1; + +--echo End of 5.0 tests |