revision-id: c03841ec0e2b7ac11711243d29821038b26e3edf (mariadb-10.4.11-593-gc03841ec0e2) parent(s): 4e2ca42225311f73745c9eec309bc941183aba30 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-08 17:25:02 +0300 message: MDEV-23634: Select query hanged the server and leads to OOM ... Handle "col<>const" in the same way that MDEV-21958 did for "col NOT IN(const-list)": do not use the condition for range/index_merge accesses if there is a unique UNIQUE KEY(col). The testcase is in main/range.test. The rest of test updates are due to widespread use of 'pk<>1' in the testsuite. Changed the test to use different but equivalent forms of the conditions. --- mysql-test/include/icp_tests.inc | 12 ++-- mysql-test/main/func_group.result | 2 +- mysql-test/main/func_group.test | 2 +- mysql-test/main/group_min_max.result | 2 +- mysql-test/main/index_merge_myisam.result | 3 +- mysql-test/main/index_merge_myisam.test | 4 +- mysql-test/main/innodb_icp.result | 12 ++-- mysql-test/main/myisam_icp.result | 12 ++-- mysql-test/main/range.result | 11 ++++ mysql-test/main/range.test | 11 ++++ mysql-test/main/range_mrr_icp.result | 11 ++++ mysql-test/main/range_vs_index_merge.result | 8 +-- mysql-test/main/range_vs_index_merge.test | 8 +-- mysql-test/main/range_vs_index_merge_innodb.result | 8 +-- mysql-test/main/subselect_mat_cost_bugs.result | 8 +-- mysql-test/main/subselect_mat_cost_bugs.test | 8 +-- mysql-test/suite/maria/icp.result | 12 ++-- .../suite/optimizer_unfixed_bugs/r/bug42991.result | 2 +- .../suite/optimizer_unfixed_bugs/t/bug42991.test | 4 +- sql/item_cmpfunc.h | 6 +- sql/opt_range.cc | 70 ++++++++++++++++------ 21 files changed, 142 insertions(+), 74 deletions(-) diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index aa09b0025d4..66fdc3e754c 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -484,11 +484,11 @@ CREATE TABLE t1 ( INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); -EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; +EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); SET SESSION optimizer_switch='index_condition_pushdown=off'; -SELECT pk, c1 FROM t1 WHERE pk <> 3; +SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); DROP TABLE t1; @@ -727,16 +727,16 @@ INSERT INTO t2 VALUES SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; DROP TABLE t1,t2; diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index fc6f93a938a..071c155cd6b 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -606,7 +606,7 @@ select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 3 NULL 15 Using where; Using index explain -select min(a1) from t1 where a1 != 'KKK'; +select min(a1) from t1 where (a1 < 'KKK' or a1 > 'KKK'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 3 NULL 15 Using where; Using index explain diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test index 10b92cbadca..a28b39c28f6 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -359,7 +359,7 @@ select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; explain select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX'; explain -select min(a1) from t1 where a1 != 'KKK'; +select min(a1) from t1 where (a1 < 'KKK' or a1 > 'KKK'); explain select max(a3) from t1 where a2 < 2 and a3 < 'SEA'; explain diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index a28cc418207..8d240f9f36d 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -2940,7 +2940,7 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <> NULL; id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a <> NULL; MIN( a ) NULL diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 387bab6b408..55caf051720 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -1687,7 +1687,8 @@ INSERT INTO t1 VALUES ALTER TABLE t1 ENABLE KEYS; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) -WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR pk != 1 ; +WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 +NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR (pk is not null and (pk <1 or pk>1)) ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,c1,i,c2 PRIMARY,i 0,5 NULL 69 Using sort_union(PRIMARY,i); Using where DROP TABLE t1; diff --git a/mysql-test/main/index_merge_myisam.test b/mysql-test/main/index_merge_myisam.test index 0b0d8d60c15..b77c9bc1ca2 100644 --- a/mysql-test/main/index_merge_myisam.test +++ b/mysql-test/main/index_merge_myisam.test @@ -236,9 +236,11 @@ INSERT INTO t1 VALUES ALTER TABLE t1 ENABLE KEYS; +# note: (pk is not null and (pk <1 or pk>1)) below is a sargable form of pk!=1 EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) -WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR pk != 1 ; +WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 +NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR (pk is not null and (pk <1 or pk>1)) ; DROP TABLE t1; diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index 949bc9a00d8..96ff1964ac1 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -455,11 +455,11 @@ c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); -EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; +EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; -SELECT pk, c1 FROM t1 WHERE pk <> 3; +SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 1 9 2 7 @@ -687,23 +687,23 @@ INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index 7f34b274764..68770229926 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -448,11 +448,11 @@ c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); -EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; +EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 5 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; -SELECT pk, c1 FROM t1 WHERE pk <> 3; +SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 1 9 2 7 @@ -680,23 +680,23 @@ INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 ref a a 515 const 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 ref a a 515 const 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 60c79c02c44..afd1571283f 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3244,6 +3244,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR id a b code num DROP TABLE t1, t2; # +# MDEV-23634: Select query hanged the server and leads to OOM ... +# (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN) +# +create table t1 (pk int primary key, a int); +insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +# must not use range: +explain select * from t1 force index (primary) where pk != 1 and pk!=2 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 10 Using where +drop table t1; +# # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value # create table t1 (pk int, i int, v int, primary key (pk), key(v)); diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index d06053d07e3..de2a428c49a 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2215,6 +2215,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR DROP TABLE t1, t2; +--echo # +--echo # MDEV-23634: Select query hanged the server and leads to OOM ... +--echo # (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN) +--echo # +create table t1 (pk int primary key, a int); +insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +--echo # must not use range: +explain select * from t1 force index (primary) where pk != 1 and pk!=2 ; +drop table t1; + --echo # --echo # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value --echo # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 257314539c5..3fce8b0fc23 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3241,6 +3241,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR id a b code num DROP TABLE t1, t2; # +# MDEV-23634: Select query hanged the server and leads to OOM ... +# (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN) +# +create table t1 (pk int primary key, a int); +insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +# must not use range: +explain select * from t1 force index (primary) where pk != 1 and pk!=2 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 10 Using where +drop table t1; +# # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value # create table t1 (pk int, i int, v int, primary key (pk), key(v)); diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index f0bf2224c92..286338d0433 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -1810,11 +1810,11 @@ CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b)); INSERT INTO t1 VALUES (167,9999), (168,10000); EXPLAIN SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,idx PRIMARY 0 NULL 2 Using index condition; Using where SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); a b 167 9999 168 10000 @@ -1846,12 +1846,12 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id state capital 4 Florida Tallahassee diff --git a/mysql-test/main/range_vs_index_merge.test b/mysql-test/main/range_vs_index_merge.test index 5ed5f621ab6..94210ce5dd3 100644 --- a/mysql-test/main/range_vs_index_merge.test +++ b/mysql-test/main/range_vs_index_merge.test @@ -1231,9 +1231,9 @@ INSERT INTO t1 VALUES (167,9999), (168,10000); EXPLAIN SELECT * FROM t1 - WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; + WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); SELECT * FROM t1 - WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; + WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); DROP TABLE t1; @@ -1266,10 +1266,10 @@ ANALYZE TABLE t1; EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; DROP TABLE t1; diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index 4cb4745d63a..65800e7397b 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -1816,11 +1816,11 @@ CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b)); INSERT INTO t1 VALUES (167,9999), (168,10000); EXPLAIN SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY,idx idx 5 NULL 2 Using where; Using index SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); a b 167 9999 168 10000 @@ -1852,12 +1852,12 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id state capital 4 Florida Tallahassee diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result index a18c5e608f1..ecceac27b2d 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.result +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -189,8 +189,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 -FROM t2 JOIN t1 ON t1.f1 -WHERE t1.f1 AND alias2.f10 +FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) +WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; id select_type table type possible_keys key key_len ref rows Extra @@ -203,8 +203,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 -FROM t2 JOIN t1 ON t1.f1 -WHERE t1.f1 AND alias2.f10 +FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) +WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; field1 diff --git a/mysql-test/main/subselect_mat_cost_bugs.test b/mysql-test/main/subselect_mat_cost_bugs.test index 028cdced560..ba1aad06a15 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.test +++ b/mysql-test/main/subselect_mat_cost_bugs.test @@ -217,8 +217,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 - FROM t2 JOIN t1 ON t1.f1 - WHERE t1.f1 AND alias2.f10 + FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) + WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; @@ -226,8 +226,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 - FROM t2 JOIN t1 ON t1.f1 - WHERE t1.f1 AND alias2.f10 + FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) + WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; diff --git a/mysql-test/suite/maria/icp.result b/mysql-test/suite/maria/icp.result index 96793beae8a..975c280d467 100644 --- a/mysql-test/suite/maria/icp.result +++ b/mysql-test/suite/maria/icp.result @@ -450,11 +450,11 @@ c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); -EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; +EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 5 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; -SELECT pk, c1 FROM t1 WHERE pk <> 3; +SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 1 9 2 7 @@ -682,23 +682,23 @@ INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 ref a a 515 const 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 ref a a 515 const 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 diff --git a/mysql-test/suite/optimizer_unfixed_bugs/r/bug42991.result b/mysql-test/suite/optimizer_unfixed_bugs/r/bug42991.result index f5554563a18..40f505d8260 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/r/bug42991.result +++ b/mysql-test/suite/optimizer_unfixed_bugs/r/bug42991.result @@ -245,7 +245,7 @@ UNLOCK TABLES; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -select * from `table5` where (col2 <= '6566-06-15' AND col24 <> 'd') group by `col83` order by `col83` desc ; +select * from `table5` where (col2 <= '6566-06-15' AND (col24 < 'd' or col24 > 'd')) group by `col83` order by `col83` desc ; col0 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12 col13 col14 col15 col16 col17 col18 col19 col20 col21 col22 col23 col24 col25 col26 col27 col28 col29 col30 col31 col32 col33 col34 col35 col36 col37 col38 col39 col40 col41 col42 col43 col44 col45 col46 col47 col48 col49 col50 col51 col52 col53 col54 col55 col56 col57 col58 col59 col60 col61 col62 col63 col64 col65 col66 col67 col68 col69 col70 col71 col72 col73 col74 col75 col76 col77 col78 col79 col80 col81 col82 col83 col84 col85 col86 col87 col88 col89 col90 col91 col92 col93 col94 col95 col96 col97 col98 col99 col100 col101 col102 col103 col104 col105 col106 col107 col108 col109 col110 col111 col112 col113 col114 col115 col116 col117 col118 col119 col120 col121 col122 col123 col124 col125 col126 col127 col128 col129 col130 col131 col132 col133 col134 col135 col136 col137 col138 col139 col140 col141 col142 col143 col144 col145 col146 col147 col148 col149 col150 col151 col152 col153 col154 col155 col156 col157 c ol158 col159 col160 col161 col162 col163 col164 col165 col166 col167 col168 col169 col170 col171 col172 col173 col174 col175 Warnings: Warning 1292 Truncated incorrect DOUBLE value: 'd' diff --git a/mysql-test/suite/optimizer_unfixed_bugs/t/bug42991.test b/mysql-test/suite/optimizer_unfixed_bugs/t/bug42991.test index d59e9e1fbeb..9e8e5c4b3cd 100644 --- a/mysql-test/suite/optimizer_unfixed_bugs/t/bug42991.test +++ b/mysql-test/suite/optimizer_unfixed_bugs/t/bug42991.test @@ -242,9 +242,9 @@ UNLOCK TABLES; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -#explain select * from `table5` where (col2 <= '6566-06-15' AND col24 <> 'd') group by `col83` order by `col83` desc ; +#explain select * from `table5` where (col2 <= '6566-06-15' AND (col24 < 'd' or col24 > 'd') group by `col83` order by `col83` desc ; -select * from `table5` where (col2 <= '6566-06-15' AND col24 <> 'd') group by `col83` order by `col83` desc ; +select * from `table5` where (col2 <= '6566-06-15' AND (col24 < 'd' or col24 > 'd')) group by `col83` order by `col83` desc ; drop table `table5`; SET debug_dbug= @saved_dbug; diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h index 8160557e006..d77da3fa48c 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -832,11 +832,7 @@ class Item_func_ne :public Item_bool_rowready_func2 { protected: SEL_TREE *get_func_mm_tree(RANGE_OPT_PARAM *param, - Field *field, Item *value) - { - DBUG_ENTER("Item_func_ne::get_func_mm_tree"); - DBUG_RETURN(get_ne_mm_tree(param, field, value, value)); - } + Field *field, Item *value); public: Item_func_ne(THD *thd, Item *a, Item *b): Item_bool_rowready_func2(thd, a, b) {} diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 68734bcbef8..d47aa1ee41e 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -449,6 +449,7 @@ void print_range_for_non_indexed_field(String *out, Field *field, static void print_min_range_operator(String *out, const ha_rkey_function flag); static void print_max_range_operator(String *out, const ha_rkey_function flag); +static bool is_field_an_unique_index(RANGE_OPT_PARAM *param, Field *field); /* SEL_IMERGE is a list of possible ways to do index merge, i.e. it is @@ -7689,6 +7690,21 @@ SEL_TREE *Item_bool_func::get_ne_mm_tree(RANGE_OPT_PARAM *param, } +SEL_TREE *Item_func_ne::get_func_mm_tree(RANGE_OPT_PARAM *param, + Field *field, Item *value) +{ + DBUG_ENTER("Item_func_ne::get_func_mm_tree"); + /* + If this condition is a "col1<>...", where there is a UNIQUE KEY(col1), + do not construct a SEL_TREE from it. A condition that excludes just one + row in the table is not selective (unless there are only a few rows) + */ + if (is_field_an_unique_index(param, field)) + DBUG_RETURN(NULL); + DBUG_RETURN(get_ne_mm_tree(param, field, value, value)); +} + + SEL_TREE *Item_func_between::get_func_mm_tree(RANGE_OPT_PARAM *param, Field *field, Item *value) { @@ -7787,28 +7803,16 @@ SEL_TREE *Item_func_in::get_func_mm_tree(RANGE_OPT_PARAM *param, DBUG_RETURN(0); /* - If this is "unique_key NOT IN (...)", do not consider it sargable (for - any index, not just the unique one). The logic is as follows: + if this is a "col1 NOT IN (...)", and there is a UNIQUE KEY(col1), do + not constuct a SEL_TREE from it. The rationale is as follows: - if there are only a few constants, this condition is not selective (unless the table is also very small in which case we won't gain anything) - - If there are a lot of constants, the overhead of building and + - if there are a lot of constants, the overhead of building and processing enormous range list is not worth it. */ - if (param->using_real_indexes) - { - key_map::Iterator it(field->key_start); - uint key_no; - while ((key_no= it++) != key_map::Iterator::BITMAP_END) - { - KEY *key_info= &field->table->key_info[key_no]; - if (key_info->user_defined_key_parts == 1 && - (key_info->flags & HA_NOSAME)) - { - DBUG_RETURN(0); - } - } - } + if (is_field_an_unique_index(param, field)) + DBUG_RETURN(0); /* Get a SEL_TREE for "(-inf|NULL) < X < c_0" interval. */ uint i=0; @@ -8526,6 +8530,38 @@ SEL_TREE *Item_equal::get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr) } +/* + @brief + Check if there is an one-segment unique key that matches the field exactly + + @detail + In the future we could also add "almost unique" indexes where any value is + present only in a few rows (but necessarily exactly one row) +*/ +static bool is_field_an_unique_index(RANGE_OPT_PARAM *param, Field *field) +{ + DBUG_ENTER("is_field_an_unique_index"); + + // The check for using_real_indexes is there because of the heuristics + // this function is used for. + if (param->using_real_indexes) + { + key_map::Iterator it(field->key_start); + uint key_no; + while ((key_no= it++) != key_map::Iterator::BITMAP_END) + { + KEY *key_info= &field->table->key_info[key_no]; + if (key_info->user_defined_key_parts == 1 && + (key_info->flags & HA_NOSAME)) + { + DBUG_RETURN(true); + } + } + } + DBUG_RETURN(false); +} + + SEL_TREE * Item_bool_func::get_mm_parts(RANGE_OPT_PARAM *param, Field *field, Item_func::Functype type, Item *value)