diff options
author | Oleksandr Byelkin <sanja@mariadb.com> | 2019-10-15 12:24:23 +0200 |
---|---|---|
committer | Oleksandr Byelkin <sanja@mariadb.com> | 2019-10-15 13:20:22 +0200 |
commit | 4ba763db77a954e355cdb90a7ef30572e2a4317b (patch) | |
tree | 526f83cfc2b3dcd30605b1d61c4cdefd3df0ff88 | |
parent | c2854c7863aa01895febfb6503f542fce6825bd6 (diff) | |
download | mariadb-git-4ba763db77a954e355cdb90a7ef30572e2a4317b.tar.gz |
MDEV-13172: Wrong result / SELECT ... WHERE EXISTS ... (with UNIQUE Key)bb-5.5-MDEV-13172
IS NULL or <=> with unique field does not mean unique row,
because several NULL possible,
so we can not convert to normal join in this case.
-rw-r--r-- | mysql-test/r/subselect_sj.result | 26 | ||||
-rw-r--r-- | mysql-test/r/subselect_sj_jcl6.result | 26 | ||||
-rw-r--r-- | mysql-test/t/subselect_sj.test | 26 | ||||
-rw-r--r-- | sql/opt_subselect.cc | 12 |
4 files changed, 88 insertions, 2 deletions
diff --git a/mysql-test/r/subselect_sj.result b/mysql-test/r/subselect_sj.result index d0b8b626ba2..6f4f363326a 100644 --- a/mysql-test/r/subselect_sj.result +++ b/mysql-test/r/subselect_sj.result @@ -3186,5 +3186,31 @@ create table t1 (a1 varchar(25)); create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); drop table t1,t2; +# +# MDEV-13172: Wrong result / SELECT ... WHERE EXISTS ... (with UNIQUE Key) +# +CREATE TABLE `t1` ( +`Id` int(11) NOT NULL, +PRIMARY KEY (`Id`) +); +INSERT INTO `t1` (`Id`) VALUES (1); +CREATE TABLE `t2` ( +`t1_Id` int(11) NOT NULL DEFAULT 0, +`col1` int(11) DEFAULT NULL, +UNIQUE KEY `col1` (`col1`) +); +INSERT INTO `t2` (`t1_Id`, `col1`) VALUES (1, NULL), (1, NULL); +SELECT Id FROM t1 WHERE Id in (SELECT t1_Id FROM t2 WHERE t2.col1 IS NULL); +Id +1 +explain extended +SELECT Id FROM t1 WHERE Id in (SELECT t1_Id FROM t2 WHERE t2.col1 IS NULL); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t2 ref col1 col1 5 const 1 100.00 Using index condition; Using where +Warnings: +Note 1003 select 1 AS `Id` from (`test`.`t2`) where ((`test`.`t2`.`t1_Id` = 1) and isnull(`test`.`t2`.`col1`)) +DROP TABLE t1, t2; # End of 5.5 test set optimizer_switch=@subselect_sj_tmp; diff --git a/mysql-test/r/subselect_sj_jcl6.result b/mysql-test/r/subselect_sj_jcl6.result index e9a8b7301f7..6b5cf9dea1c 100644 --- a/mysql-test/r/subselect_sj_jcl6.result +++ b/mysql-test/r/subselect_sj_jcl6.result @@ -3200,6 +3200,32 @@ create table t1 (a1 varchar(25)); create table t2 (a2 varchar(25)) ; insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); drop table t1,t2; +# +# MDEV-13172: Wrong result / SELECT ... WHERE EXISTS ... (with UNIQUE Key) +# +CREATE TABLE `t1` ( +`Id` int(11) NOT NULL, +PRIMARY KEY (`Id`) +); +INSERT INTO `t1` (`Id`) VALUES (1); +CREATE TABLE `t2` ( +`t1_Id` int(11) NOT NULL DEFAULT 0, +`col1` int(11) DEFAULT NULL, +UNIQUE KEY `col1` (`col1`) +); +INSERT INTO `t2` (`t1_Id`, `col1`) VALUES (1, NULL), (1, NULL); +SELECT Id FROM t1 WHERE Id in (SELECT t1_Id FROM t2 WHERE t2.col1 IS NULL); +Id +1 +explain extended +SELECT Id FROM t1 WHERE Id in (SELECT t1_Id FROM t2 WHERE t2.col1 IS NULL); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 100.00 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 100.00 +2 MATERIALIZED t2 ref col1 col1 5 const 1 100.00 Using index condition; Using where +Warnings: +Note 1003 select 1 AS `Id` from (`test`.`t2`) where ((`test`.`t2`.`t1_Id` = 1) and isnull(`test`.`t2`.`col1`)) +DROP TABLE t1, t2; # End of 5.5 test set optimizer_switch=@subselect_sj_tmp; # diff --git a/mysql-test/t/subselect_sj.test b/mysql-test/t/subselect_sj.test index 6b59049bc4f..befa2c7af5a 100644 --- a/mysql-test/t/subselect_sj.test +++ b/mysql-test/t/subselect_sj.test @@ -2881,6 +2881,32 @@ insert into t1 select 'xxx' from dual where 'xxx' in (select a2 from t2); drop table t1,t2; + +--echo # +--echo # MDEV-13172: Wrong result / SELECT ... WHERE EXISTS ... (with UNIQUE Key) +--echo # + +CREATE TABLE `t1` ( + `Id` int(11) NOT NULL, + PRIMARY KEY (`Id`) +); + +INSERT INTO `t1` (`Id`) VALUES (1); + +CREATE TABLE `t2` ( + `t1_Id` int(11) NOT NULL DEFAULT 0, + `col1` int(11) DEFAULT NULL, + UNIQUE KEY `col1` (`col1`) +); + +INSERT INTO `t2` (`t1_Id`, `col1`) VALUES (1, NULL), (1, NULL); + +SELECT Id FROM t1 WHERE Id in (SELECT t1_Id FROM t2 WHERE t2.col1 IS NULL); +explain extended +SELECT Id FROM t1 WHERE Id in (SELECT t1_Id FROM t2 WHERE t2.col1 IS NULL); + +DROP TABLE t1, t2; + --echo # End of 5.5 test # The following command must be the last one the file diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index cb821325e57..f876129aed7 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -2640,9 +2640,17 @@ bool find_eq_ref_candidate(TABLE *table, table_map sj_inner_tables) { do /* For all equalities on all key parts */ { - /* Check if this is "t.keypart = expr(outer_tables) */ + /* + Check if this is "t.keypart = expr(outer_tables) + + Don't allow variants that can produce duplicates: + - Dont allow "ref or null" + - the keyuse (that is, the operation) must be null-rejecting, + unless the other expression is non-NULLable. + */ if (!(keyuse->used_tables & sj_inner_tables) && - !(keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL)) + !(keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL) && + (keyuse->null_rejecting || !keyuse->val->maybe_null)) { bound_parts |= 1 << keyuse->keypart; } |