From b917fb63a638fd117e1e52d3bc29b57a81124fea Mon Sep 17 00:00:00 2001 From: unknown Date: Fri, 14 Sep 2012 11:26:01 +0300 Subject: Fix bug lp:1009187, mdev-373, mysql bug#58628 Analysis: The queries in question use the [unique | index]_subquery execution methods. These methods reuse the ref keys constructed by create_ref_for_key(). The way create_ref_for_key() works is that it doesn't store in ref.key_copy[] store_key elements that represent constants. In particular it doesn't store the store_key for NULL constants. The execution of [unique | index]_subquery calls subselect_uniquesubquery_engine::copy_ref_key, which in addition to copy the left IN argument into a index lookup key, is supposed to detect if the left IN argument contains NULLs. Since the store_key for the NULL constant is not copied into the key array, the null is not detected, and execution erroneously proceeds as if it should look for a complete match. Solution: The solution (unlike MySQL) is to reuse already computed information about NULL presence. Item_in_optimizer::val_int already finds out if the left IN operand contains NULLs. The fix propagates this to the execution methods subselect_[unique | index]subquery_engine::exec so it knows if there were NULL values independent of the presence of keys. In addition the patch siplifies copy_ref_key() and the logic that hanldes the case of NULLs in the left IN operand. --- mysql-test/t/subselect4.test | 44 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 44 insertions(+) (limited to 'mysql-test/t/subselect4.test') diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index bb97f246488..3de2dfc394e 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -170,6 +170,50 @@ INSERT INTO t VALUES (''),(''),(''),(''),(''),(''),(''),(''),(''),(''),(''); SELECT * FROM (SELECT default(a) FROM t GROUP BY a) d; DROP TABLE t; +--echo # +--echo # LP BUG#1009187, MDEV-373, MYSQL bug#58628 +--echo # Wrong result for a query with [NOT] IN subquery predicate if +--echo # the left part of the predicate is explicit NULL +--echo # + +CREATE TABLE t1 (pk INT NOT NULL, i INT NOT NULL); +INSERT INTO t1 VALUES (0,10), (1,20), (2,30), (3,40); + +CREATE TABLE t2a (pk INT NOT NULL, i INT NOT NULL, PRIMARY KEY(i,pk)); +INSERT INTO t2a VALUES (0,0), (1,1), (2,2), (3,3); + +CREATE TABLE t2b (pk INT, i INT); +INSERT INTO t2b VALUES (0,0), (1,1), (2,2), (3,3); + +CREATE TABLE t2c (pk INT NOT NULL, i INT NOT NULL); +INSERT INTO t2c VALUES (0,0), (1,1), (2,2), (3,3); +create index it2c on t2c (i,pk); + +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +SELECT * FROM t1 WHERE 1+NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk); +SELECT * FROM t1 WHERE NULL IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) IS UNKNOWN; +SELECT t1.pk, NULL NOT IN (SELECT t2a.i FROM t2a WHERE t2a.pk = t1.pk) FROM t1; + +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk); +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk); +SELECT * FROM t1 WHERE NULL IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) IS UNKNOWN; +SELECT t1.pk, NULL NOT IN (SELECT t2b.i FROM t2b WHERE t2b.pk = t1.pk) FROM t1; + +EXPLAIN +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk); +SELECT * FROM t1 WHERE NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk); +SELECT * FROM t1 WHERE NULL IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) IS UNKNOWN; +SELECT t1.pk, NULL NOT IN (SELECT t2c.i FROM t2c WHERE t2c.pk = t1.pk) FROM t1; + +EXPLAIN +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); +SELECT * FROM t1 WHERE (NULL, 1) NOT IN (SELECT t2a.i, t2a.pk FROM t2a WHERE t2a.pk = t1.pk); + +drop table t1, t2a, t2b, t2c; + --echo # --echo # End of 5.1 tests. --echo # -- cgit v1.2.1