revision-id: 21f4d8674a68728874a20690b564c5f91eeee19b (mariadb-10.4.22-82-g21f4d8674a6) parent(s): b915f79e4e004fde4f6ac8f341afee980e11792b author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2022-01-31 12:27:02 +0300 message: MDEV-22377: Subquery in an UPDATE query uses full scan instead of range Item_in_subselect::inject_in_to_exists_cond injects the IN-equality into the subquery's WHERE. A SELECT's WHERE clause has certain invariants which must hold in order for other optimization to work. However, inject_in_to_exists_cond breaks them which disables further optimizations. Details: The invariant is: If the subquery is an Item_cond_and and the WHERE clause has multiple equalities, then 1. The end of Item_cond_and's argument is the list of WHERE's multiple equalities. 2. join->conds.m_cond_equal should describe WHERE's multiple equalities. The code in Item_in_subselect::inject_in_to_exists_cond maintained #1 but failed to maintain #2. The patch makes the function to use and_new_conditions_to_optimized_cond() which properly maintains all the invariants. Note that and_new_conditions_to_optimized_cond() can infer that the resulting condition is always false. The patch adds handing for this case. --- mysql-test/main/subselect.result | 4 +- mysql-test/main/subselect2.result | 2 +- mysql-test/main/subselect3.result | 4 +- mysql-test/main/subselect3_jcl6.result | 4 +- mysql-test/main/subselect4.result | 4 +- mysql-test/main/subselect_extra_no_semijoin.result | 2 +- mysql-test/main/subselect_innodb.result | 54 ++++++++++++++++++++++ mysql-test/main/subselect_innodb.test | 53 +++++++++++++++++++++ mysql-test/main/subselect_mat_cost_bugs.result | 4 +- mysql-test/main/subselect_no_exists_to_in.result | 4 +- mysql-test/main/subselect_no_mat.result | 4 +- mysql-test/main/subselect_no_opts.result | 4 +- mysql-test/main/subselect_no_scache.result | 4 +- mysql-test/main/subselect_no_semijoin.result | 4 +- mysql-test/main/subselect_sj.result | 2 +- mysql-test/main/subselect_sj2_mat.result | 4 +- mysql-test/main/subselect_sj_jcl6.result | 2 +- sql/item_subselect.cc | 49 +++++++------------- 18 files changed, 149 insertions(+), 59 deletions(-) diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 15ebaa33474..ca33ca6404e 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -1327,7 +1327,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1337,7 +1337,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', diff --git a/mysql-test/main/subselect2.result b/mysql-test/main/subselect2.result index a3d7fda7abc..c54d635230f 100644 --- a/mysql-test/main/subselect2.result +++ b/mysql-test/main/subselect2.result @@ -262,7 +262,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2c ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) Warnings: Note 1276 Field or reference 'test.t1.a' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where (`test`.`t2b`.`m` <> `test`.`t1`.`a` or `test`.`t2b`.`m` = `test`.`t2a`.`m`) and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2` or `test`.`t2a`.`c2` is null) and `test`.`t2c`.`c2` = `test`.`t2b`.`c2` and `test`.`t2b`.`n` = `test`.`t2a`.`m` having trigcond(`test`.`t2a`.`c2` is null)))) +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`c1` AS `c1` from `test`.`t1` where !<expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t2a`.`c2` from `test`.`t2` `t2a` join `test`.`t2` `t2b` join `test`.`t2` `t2c` where `test`.`t2c`.`c2` = `test`.`t2b`.`c2` and `test`.`t2b`.`n` = `test`.`t2a`.`m` and (`test`.`t2b`.`m` <> `test`.`t1`.`a` or `test`.`t2b`.`m` = `test`.`t2a`.`m`) and trigcond(<cache>(`test`.`t1`.`c1`) = `test`.`t2a`.`c2` or `test`.`t2a`.`c2` is null) having trigcond(`test`.`t2a`.`c2` is null)))) DROP TABLE t1,t2; # # MDEV-614, also MDEV-536, also LP:1050806: diff --git a/mysql-test/main/subselect3.result b/mysql-test/main/subselect3.result index 5c4544a1b05..0034f61ac23 100644 --- a/mysql-test/main/subselect3.result +++ b/mysql-test/main/subselect3.result @@ -169,7 +169,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) and `test`.`t2`.`a` = `test`.`t1`.`b` having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` drop table t1, t2, t3; create table t1 (a int NOT NULL, b int NOT NULL, key(a)); insert into t1 values @@ -197,7 +197,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) and `test`.`t2`.`a` = `test`.`t1`.`b`))) AS `Z` from `test`.`t3` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))) AS `Z` from `test`.`t3` drop table t1,t2,t3; create table t1 (oref int, grp int); insert into t1 (oref, grp) values diff --git a/mysql-test/main/subselect3_jcl6.result b/mysql-test/main/subselect3_jcl6.result index 4260676cc37..b7b18bf80e0 100644 --- a/mysql-test/main/subselect3_jcl6.result +++ b/mysql-test/main/subselect3_jcl6.result @@ -172,7 +172,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 5 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) and `test`.`t2`.`a` = `test`.`t1`.`b` having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`a` is null)))) AS `Z` from `test`.`t3` drop table t1, t2, t3; create table t1 (a int NOT NULL, b int NOT NULL, key(a)); insert into t1 values @@ -200,7 +200,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 ref a a 4 test.t1.b 1 100.00 Using where; Using join buffer (flat, BKA join); Key-ordered Rowid-ordered scan Warnings: Note 1276 Field or reference 'test.t3.oref' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`) and `test`.`t2`.`a` = `test`.`t1`.`b`))) AS `Z` from `test`.`t3` +Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`oref` AS `oref`,<expr_cache><`test`.`t3`.`a`,`test`.`t3`.`oref`>(<in_optimizer>(`test`.`t3`.`a`,<exists>(/* select#2 */ select `test`.`t1`.`a` from `test`.`t1` join `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`b` and `test`.`t2`.`b` = `test`.`t3`.`oref` and trigcond(<cache>(`test`.`t3`.`a`) = `test`.`t1`.`a`)))) AS `Z` from `test`.`t3` drop table t1,t2,t3; create table t1 (oref int, grp int); insert into t1 (oref, grp) values diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index e4e9a5c8917..8b35131b9b3 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -1741,7 +1741,7 @@ SET @@optimizer_switch = 'in_to_exists=on,materialization=off,semijoin=off'; EXPLAIN SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables SELECT * FROM t1 WHERE a1 IN (SELECT b1 FROM t2 WHERE b1 = b2); a1 a2 set @@optimizer_switch=@save_optimizer_switch; @@ -2349,7 +2349,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 100.00 2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select 3 AS `f` from dual where !<expr_cache><3>(<in_optimizer>(3,<exists>(/* select#2 */ select `test`.`t1`.`b` from `test`.`t1` where (`test`.`t1`.`c` = 'USA' or `test`.`t1`.`c` <> 'USA') and trigcond(<cache>(3) = `test`.`t1`.`b` or `test`.`t1`.`b` is null) and `test`.`t1`.`b` = `test`.`t1`.`a` having trigcond(`test`.`t1`.`b` is null)))) +Note 1003 /* select#1 */ select 3 AS `f` from dual where !<expr_cache><3>(<in_optimizer>(3,<exists>(/* select#2 */ select `test`.`t1`.`b` from `test`.`t1` where `test`.`t1`.`b` = `test`.`t1`.`a` and (`test`.`t1`.`c` = 'USA' or `test`.`t1`.`c` <> 'USA') and trigcond(<cache>(3) = `test`.`t1`.`a` or `test`.`t1`.`a` is null) having trigcond(`test`.`t1`.`b` is null)))) SELECT * FROM t2 WHERE f NOT IN (SELECT b FROM t1 WHERE 0 OR (c IN ('USA') OR c NOT IN ('USA')) AND a = b); diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result index faeaf75c590..9507670d6bf 100644 --- a/mysql-test/main/subselect_extra_no_semijoin.result +++ b/mysql-test/main/subselect_extra_no_semijoin.result @@ -454,7 +454,7 @@ WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY t2 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY <derived3> ref key1 key1 8 const,const 0 Using where +2 DEPENDENT SUBQUERY <derived3> ref key1 key1 8 const,const 0 3 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t3 WHERE t3.b IN (SELECT v1.b FROM v1, t2 diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index f1e07ce09fe..8ab5e662fbd 100644 --- a/mysql-test/main/subselect_innodb.result +++ b/mysql-test/main/subselect_innodb.result @@ -663,4 +663,58 @@ a b execute stmt; a b drop table t1,t2; +# +# MDEV-22377: Subquery in an UPDATE query uses full scan instead of range +# +CREATE TABLE t1 ( +key1 varchar(30) NOT NULL, +col1 int(11) NOT NULL, +filler char(100) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; +insert into t1 +select +seq, seq, seq +from seq_1_to_100000; +CREATE TABLE t10 ( +key1 varchar(30) NOT NULL, +col1 int, +filler char(100), +PRIMARY KEY (key1) +) ENGINE=InnoDB CHARSET=utf8; +insert into t10 +select +seq, seq, seq from seq_1_to_100000; +CREATE TABLE t11 ( +key1 varchar(30) NOT NULL, +filler char(100), +PRIMARY KEY (key1) +) ENGINE=InnoDB CHARSET=utf8; +insert into t11 +select +seq, seq from seq_1_to_100000; +explain +select * from t1 hist +where +hist.col1 NOT IN (SELECT tn.col1 +FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1 +WHERE +tn.key1 IN ('1','2','3','4','5','6','7','8','9','10') +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY hist ALL NULL NULL NULL NULL 100112 Using where +2 MATERIALIZED tms range PRIMARY PRIMARY 92 NULL 10 Using where; Using index +2 MATERIALIZED tn eq_ref PRIMARY PRIMARY 92 test.tms.key1 1 +explain update t1 hist +set filler='aaa' +WHERE +key1 IN ('1','2','3','4','5','6','7','8','9','10') AND +hist.col1 NOT IN (SELECT tn.col1 +FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1 +WHERE tn.key1 IN ('1','2','3','4','5','6','7','8','9','10') +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY hist ALL NULL NULL NULL NULL 100112 Using where +2 DEPENDENT SUBQUERY tms range PRIMARY PRIMARY 92 NULL 10 Using where; Using index +2 DEPENDENT SUBQUERY tn eq_ref PRIMARY PRIMARY 92 test.tms.key1 1 Using where +drop table t1, t10, t11; # End of 10.4 tests diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test index 37f8f40200e..d175fd87234 100644 --- a/mysql-test/main/subselect_innodb.test +++ b/mysql-test/main/subselect_innodb.test @@ -661,4 +661,57 @@ execute stmt; drop table t1,t2; +--echo # +--echo # MDEV-22377: Subquery in an UPDATE query uses full scan instead of range +--echo # +--source include/have_sequence.inc + +CREATE TABLE t1 ( + key1 varchar(30) NOT NULL, + col1 int(11) NOT NULL, + filler char(100) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +insert into t1 +select + seq, seq, seq +from seq_1_to_100000; + +CREATE TABLE t10 ( + key1 varchar(30) NOT NULL, + col1 int, + filler char(100), + PRIMARY KEY (key1) +) ENGINE=InnoDB CHARSET=utf8; + +insert into t10 +select + seq, seq, seq from seq_1_to_100000; +CREATE TABLE t11 ( + key1 varchar(30) NOT NULL, + filler char(100), + PRIMARY KEY (key1) +) ENGINE=InnoDB CHARSET=utf8; + +insert into t11 +select + seq, seq from seq_1_to_100000; +explain +select * from t1 hist +where + hist.col1 NOT IN (SELECT tn.col1 + FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1 + WHERE + tn.key1 IN ('1','2','3','4','5','6','7','8','9','10') + ); +explain update t1 hist +set filler='aaa' +WHERE + key1 IN ('1','2','3','4','5','6','7','8','9','10') AND + hist.col1 NOT IN (SELECT tn.col1 + FROM t10 tn JOIN t11 tms ON tms.key1 = tn.key1 + WHERE tn.key1 IN ('1','2','3','4','5','6','7','8','9','10') + ); +drop table t1, t10, t11; + --echo # End of 10.4 tests diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result index ecceac27b2d..0ccac8d3dd5 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.result +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -100,7 +100,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 DEPENDENT SUBQUERY t2 index c3 c3 9 NULL 2 100.00 Using where; Using index; Using join buffer (flat, BNL join) Warnings: Note 1276 Field or reference 'test.t1.pk' of SELECT #2 was resolved in SELECT #1 -Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on(`test`.`t1a`.`c2` = `test`.`t1b`.`pk` and 2) where `test`.`t1`.`pk` <> 0 and <cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1` and `test`.`t2`.`c3` = `test`.`t1b`.`c4`))) +Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` where <expr_cache><`test`.`t1`.`c1`,`test`.`t1`.`pk`>(<in_optimizer>(`test`.`t1`.`c1`,<exists>(/* select#2 */ select `test`.`t1a`.`c1` from `test`.`t1b` join `test`.`t2` left join `test`.`t1a` on(`test`.`t1a`.`c2` = `test`.`t1b`.`pk` and 2) where `test`.`t2`.`c3` = `test`.`t1b`.`c4` and `test`.`t1`.`pk` <> 0 and <cache>(`test`.`t1`.`c1`) = `test`.`t1a`.`c1`))) SELECT pk FROM t1 WHERE c1 IN @@ -364,7 +364,7 @@ AND a = SOME (SELECT b FROM t5)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where 2 DEPENDENT SUBQUERY t5 index c c 10 NULL 2 Using where; Using index; Start temporary -2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using index condition; Using where; End temporary +2 DEPENDENT SUBQUERY t4 eq_ref PRIMARY PRIMARY 4 test.t5.b 1 Using where; End temporary SELECT * FROM t3 WHERE t3.b > ALL ( diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index b1432cf0979..abf53854c55 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -1331,7 +1331,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1341,7 +1341,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index da60f98bf00..345a70169af 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -1334,7 +1334,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1344,7 +1344,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index fb99e237a1c..21078bd5959 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -1330,7 +1330,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1340,7 +1340,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 4b910009248..752556fdff3 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -1333,7 +1333,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1343,7 +1343,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index f2230718754..0031a1376fa 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -1330,7 +1330,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from dual where 0)) AS `0 IN (SELECT 1 FROM t1 a)` INSERT INTO t1 (pseudo) VALUES ('test1'); SELECT 0 IN (SELECT 1 FROM t1 a); 0 IN (SELECT 1 FROM t1 a) @@ -1340,7 +1340,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables Warnings: -Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0 = 1)) AS `0 IN (SELECT 1 FROM t1 a)` +Note 1003 /* select#1 */ select <in_optimizer>(0,<exists>(/* select#2 */ select 1 from `test`.`t1` `a` where 0)) AS `0 IN (SELECT 1 FROM t1 a)` drop table t1; CREATE TABLE `t1` ( `i` int(11) NOT NULL default '0', diff --git a/mysql-test/main/subselect_sj.result b/mysql-test/main/subselect_sj.result index f3519e1d846..29e2d01215e 100644 --- a/mysql-test/main/subselect_sj.result +++ b/mysql-test/main/subselect_sj.result @@ -3212,7 +3212,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where 2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where 1 = `test`.`t4`.`c4`)))) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where `test`.`t4`.`c4` = 1)))) where 1 # mdev-12820 SELECT * FROM t1 diff --git a/mysql-test/main/subselect_sj2_mat.result b/mysql-test/main/subselect_sj2_mat.result index e2c9adbfc37..72ddb59fe47 100644 --- a/mysql-test/main/subselect_sj2_mat.result +++ b/mysql-test/main/subselect_sj2_mat.result @@ -1646,10 +1646,10 @@ EXPLAIN EXTENDED SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where -2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 100.00 Using where 2 DEPENDENT SUBQUERY t3 hash_ALL NULL #hash#$hj 5 func 3 100.00 Using where; Using join buffer (flat, BNLH join) Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` where <expr_cache><`test`.`t1`.`i1`>(<in_optimizer>(`test`.`t1`.`i1`,<exists>(/* select#2 */ select `test`.`t3`.`i3` from `test`.`t2` join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and <cache>(`test`.`t1`.`i1`) = `test`.`t3`.`i3`))) +Note 1003 /* select#1 */ select `test`.`t1`.`i1` AS `i1` from `test`.`t1` where <expr_cache><`test`.`t1`.`i1`>(<in_optimizer>(`test`.`t1`.`i1`,<exists>(/* select#2 */ select `test`.`t3`.`i3` from `test`.`t2` join `test`.`t3` where `test`.`t3`.`i3` = `test`.`t2`.`i2` and <cache>(`test`.`t1`.`i1`) = `test`.`t2`.`i2`))) SELECT * FROM t1 WHERE i1 IN (SELECT i3 FROM t2, t3 WHERE i3 = i2 OR 1=2); i1 7 diff --git a/mysql-test/main/subselect_sj_jcl6.result b/mysql-test/main/subselect_sj_jcl6.result index 02e9a833db6..8874d85681f 100644 --- a/mysql-test/main/subselect_sj_jcl6.result +++ b/mysql-test/main/subselect_sj_jcl6.result @@ -3223,7 +3223,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (incremental, BNL join) 2 SUBQUERY t4 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where 1 = `test`.`t4`.`c4`)))) where 1 +Note 1003 /* select#1 */ select `test`.`t1`.`c1` AS `c1` from `test`.`t1` left join (`test`.`t2` join `test`.`t3`) on(`test`.`t3`.`c3` = `test`.`t1`.`c1` and <cache>(<in_optimizer>(1,<exists>(/* select#2 */ select `test`.`t4`.`c4` from `test`.`t4` where `test`.`t4`.`c4` = 1)))) where 1 # mdev-12820 SELECT * FROM t1 diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 56ab0f648ee..c341655a499 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -2777,43 +2777,26 @@ bool Item_in_subselect::inject_in_to_exists_cond(JOIN *join_arg) if (where_item) { - List<Item> *and_args= NULL; - /* - If the top-level Item of the WHERE clause is an AND, detach the multiple - equality list that was attached to the end of the AND argument list by - build_equal_items_for_cond(). The multiple equalities must be detached - because fix_fields merges lower level AND arguments into the upper AND. - As a result, the arguments from lower-level ANDs are concatenated after - the multiple equalities. When the multiple equality list is treated as - such, it turns out that it contains non-Item_equal object which is wrong. - */ - if (join_arg->conds && join_arg->conds->type() == Item::COND_ITEM && - ((Item_cond*) join_arg->conds)->functype() == Item_func::COND_AND_FUNC) - { - and_args= ((Item_cond*) join_arg->conds)->argument_list(); - if (join_arg->cond_equal) - and_args->disjoin((List<Item> *) &join_arg->cond_equal->current_level); - } + List<Item> list1; + list1.push_back(where_item); + where_item= and_new_conditions_to_optimized_cond(thd, join_arg->conds, + &join_arg->cond_equal, + list1, + &join_arg->cond_value); - where_item= and_items(thd, join_arg->conds, where_item); - if (where_item->fix_fields_if_needed(thd, 0)) - DBUG_RETURN(true); - // TIMOUR TODO: call optimize_cond() for the new where clause thd->change_item_tree(&select_lex->where, where_item); - select_lex->where->top_level_item(); - join_arg->conds= select_lex->where; + if (where_item) + { + select_lex->where->top_level_item(); + join_arg->conds= select_lex->where; + } + else + join_arg->conds= NULL; - /* Attach back the list of multiple equalities to the new top-level AND. */ - if (and_args && join_arg->cond_equal) + if (join_arg->cond_value == Item::COND_FALSE) { - /* The argument list of the top-level AND may change after fix fields. */ - and_args= ((Item_cond*) join_arg->conds)->argument_list(); - List_iterator<Item_equal> li(join_arg->cond_equal->current_level); - Item_equal *elem; - while ((elem= li++)) - { - and_args->push_back(elem, thd->mem_root); - } + join_arg->zero_result_cause= "Impossible WHERE noticed after reading const tables"; + join_arg->conds= new (thd->mem_root) Item_int(thd, 0); } }