- commits - lists.mariadb.org

[Commits] c03841ec0e2: MDEV-23634: Select query hanged the server and leads to OOM ...
by psergey 08 Apr '21

08 Apr '21

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)

1 0

[Commits] 6a0d0f88015: MDEV-23634: Select query hanged the server and leads to OOM ...
by psergey 08 Apr '21

08 Apr '21

revision-id: 6a0d0f88015e60a69485beb137e2b35580cf3d89 (mariadb-10.5.4-566-g6a0d0f88015) parent(s): 7f75acc05c54bb4a2ffd0c38c7171e27c271bdb2 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-08 16:02:58 +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 consider it sargable if there is a 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 syntax so that the conditions remain sargable. --- 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_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 ++++++++++++++++------ 20 files changed, 131 insertions(+), 74 deletions(-) diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index bfa09e1c01e..7e53f957651 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -487,11 +487,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; @@ -733,17 +733,17 @@ insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; --replace_column 9 # 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'; --replace_column 9 # 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 0f09aedc22a..177f0950a77 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 range PRIMARY PRIMARY 0 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 range PRIMARY PRIMARY 3 NULL 14 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 4f32db780fd..a17f3f09c3b 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -2942,7 +2942,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 1a4906801a0..053239b7fb1 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 e35ce567573..10ddbee36b2 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 adf65780d21..27e95b51116 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 @@ -689,23 +689,23 @@ INSERT INTO t2 VALUES insert into t2 select seq from seq_1_to_100; 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 # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 f059c3f95e5..f3278bb74b1 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 @@ -682,23 +682,23 @@ INSERT INTO t2 VALUES insert into t2 select seq from seq_1_to_100; 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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 8e9b9596e1e..d97cfb2b587 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3252,6 +3252,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 8e8161cde25..6d43ad9090d 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_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 6b2c9a2f0bc..207e012b825 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 497aa3d94dc..4ac62e24940 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 34c0ccab620..6a218ed5e04 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 149258de918..aae612a0205 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 @@ -684,23 +684,23 @@ INSERT INTO t2 VALUES insert into t2 select seq from seq_1_to_100; 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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 46412fe8ad6..aa7269ab95a 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -838,11 +838,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 f1657b51bf3..9ec8781bc30 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -461,6 +461,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 @@ -7721,6 +7722,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) { @@ -7819,28 +7835,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; @@ -8540,6 +8544,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)

1 0

[Commits] 5fdcd669fa7: MDEV-25362: Incorrect name resolution for subqueries in ON expressions
by psergey 07 Apr '21

07 Apr '21

revision-id: 5fdcd669fa78962ccafc9f7ea843878118ad3d0d (mariadb-10.5.2-545-g5fdcd669fa7) parent(s): c2a63ac526bf4cd269def30a3d55ff29fdba8f86 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-07 19:26:58 +0300 message: MDEV-25362: Incorrect name resolution for subqueries in ON expressions When adding a subquery, the outer Name_resolution_context should be the top context on the context stack, not the WHERE-clause level Name_resolution_context of the parent select. This makes a difference when the subquery is in the ON expression: it should follow the name resolution rules of the ON expression, not of the WHERE. --- mysql-test/main/join_outer.result | 28 ++++++++++++++++++++++++++++ mysql-test/main/join_outer.test | 33 +++++++++++++++++++++++++++++++++ mysql-test/main/join_outer_jcl6.result | 28 ++++++++++++++++++++++++++++ sql/sql_lex.cc | 2 +- 4 files changed, 90 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index e882973043e..9f5ed79154b 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2786,3 +2786,31 @@ a b c d e f 1 4 NULL NULL NULL NULL 2 4 NULL NULL NULL NULL drop table t1,t2,t3,t4,t5,t6; +# +# MDEV-25362: Incorrect name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index f835d8af5a8..a93b865eed3 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -2287,3 +2287,36 @@ FROM LEFT JOIN t6 ON t3.c = t6.f; drop table t1,t2,t3,t4,t5,t6; + +--echo # +--echo # MDEV-25362: Incorrect name resolution for subqueries in ON expressions +--echo # +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=t1.a) + ) on (t2.c=t1.a ); + +# This must produce an error: +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=(select max(g) from t4 where t4.h=t1.a)) + ) on (t2.c=t1.a ); + +drop table t1,t2,t3,t4; diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index 9cbfa5d0fe5..89417e79003 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2793,3 +2793,31 @@ a b c d e f 1 4 NULL NULL NULL NULL 2 4 NULL NULL NULL NULL drop table t1,t2,t3,t4,t5,t6; +# +# MDEV-25362: Incorrect name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 02652dd095c..f8654c454c5 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -10080,7 +10080,7 @@ SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit) (curr_sel == NULL && current_select == &builtin_select)); if (curr_sel) { - curr_sel->register_unit(unit, &curr_sel->context); + curr_sel->register_unit(unit, context_stack.head()); curr_sel->add_statistics(unit); }

1 0

[Commits] a3a47aff718: MDEV-23634: Select query hanged the server and leads to OOM ...
by psergey 07 Apr '21

07 Apr '21

revision-id: a3a47aff71897a44fa0819e0ff106c40c772e8d6 (mariadb-10.5.4-566-ga3a47aff718) parent(s): 7f75acc05c54bb4a2ffd0c38c7171e27c271bdb2 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-07 14:14:11 +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 consider it sargable if there is a 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 syntax so that the conditions remain sargable. --- 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_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 | 65 ++++++++++++++++------ 20 files changed, 126 insertions(+), 74 deletions(-) diff --git a/mysql-test/include/icp_tests.inc b/mysql-test/include/icp_tests.inc index bfa09e1c01e..7e53f957651 100644 --- a/mysql-test/include/icp_tests.inc +++ b/mysql-test/include/icp_tests.inc @@ -487,11 +487,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; @@ -733,17 +733,17 @@ insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; --replace_column 9 # 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'; --replace_column 9 # 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 0f09aedc22a..177f0950a77 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 range PRIMARY PRIMARY 0 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 range PRIMARY PRIMARY 3 NULL 14 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 4f32db780fd..a17f3f09c3b 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -2942,7 +2942,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 1a4906801a0..053239b7fb1 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 e35ce567573..10ddbee36b2 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 adf65780d21..27e95b51116 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 @@ -689,23 +689,23 @@ INSERT INTO t2 VALUES insert into t2 select seq from seq_1_to_100; 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 # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 f059c3f95e5..f3278bb74b1 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 @@ -682,23 +682,23 @@ INSERT INTO t2 VALUES insert into t2 select seq from seq_1_to_100; 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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 8e9b9596e1e..d97cfb2b587 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3252,6 +3252,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 8e8161cde25..6d43ad9090d 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_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 6b2c9a2f0bc..207e012b825 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 497aa3d94dc..4ac62e24940 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 34c0ccab620..6a218ed5e04 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 149258de918..aae612a0205 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 @@ -684,23 +684,23 @@ INSERT INTO t2 VALUES insert into t2 select seq from seq_1_to_100; 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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # 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 46412fe8ad6..aa7269ab95a 100644 --- a/sql/item_cmpfunc.h +++ b/sql/item_cmpfunc.h @@ -838,11 +838,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 f1657b51bf3..82be5ee214d 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -461,6 +461,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_with_unique_index(RANGE_OPT_PARAM *param, Field *field); /* SEL_IMERGE is a list of possible ways to do index merge, i.e. it is @@ -7721,6 +7722,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 a UNIQUE KEY(col1), + do not consider it sargable. A condition that excludes just one table + row is not selective. + */ + if (is_field_with_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) { @@ -7819,28 +7835,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 consider the condition sargable. The logic 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_with_unique_index(param, field)) + DBUG_RETURN(0); /* Get a SEL_TREE for "(-inf|NULL) < X < c_0" interval. */ uint i=0; @@ -8540,6 +8544,33 @@ SEL_TREE *Item_equal::get_mm_tree(RANGE_OPT_PARAM *param, Item **cond_ptr) } +/* + Check if there is a unique index over a given field +*/ +static bool is_field_with_unique_index(RANGE_OPT_PARAM *param, Field *field) +{ + DBUG_ENTER("is_field_with_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)

1 0

[Commits] ded1d8b44be: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set
by psergey 06 Apr '21

06 Apr '21

revision-id: ded1d8b44befb545ea581aee1f4f84676eb7f254 (mariadb-10.5.2-568-gded1d8b44be) parent(s): 160bd1691ba718a5458c349fcf70e6bbad190829 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-06 11:27:59 +0300 message: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set - Address review input. No functional changes. --- sql/json_table.cc | 24 +++++++++++++----------- 1 file changed, 13 insertions(+), 11 deletions(-) diff --git a/sql/json_table.cc b/sql/json_table.cc index 9fd647dc6c5..dae1c694d78 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -135,18 +135,16 @@ static table_map get_disallowed_table_deps(JOIN *join, table_map table_func_bit) { table_map disallowed_tables= 0; - if (get_disallowed_table_deps_for_list(table_func_bit, join->join_list, - &disallowed_tables)) - return disallowed_tables; - else + if (!get_disallowed_table_deps_for_list(table_func_bit, join->join_list, + &disallowed_tables)) { + // We haven't found the table with table_func_bit in all tables? DBUG_ASSERT(0); - return disallowed_tables; } + return disallowed_tables; } - /* A table that produces output rows for JSON_TABLE(). */ @@ -1229,10 +1227,10 @@ void Table_function_json_table::end_nested_path() bool push_table_function_arg_context(LEX *lex, MEM_ROOT *alloc) { - // Walk the context stack until we find a context that is select-level - // context. + // Walk the context stack until we find a context that is used for resolving + // the SELECT's WHERE clause. List_iterator<Name_resolution_context> it(lex->context_stack); - Name_resolution_context *ctx= NULL; + Name_resolution_context *ctx; while ((ctx= it++)) { if (ctx->select_lex && ctx == &ctx->select_lex->context) @@ -1242,6 +1240,10 @@ bool push_table_function_arg_context(LEX *lex, MEM_ROOT *alloc) // Then, create a copy of it and return it. Name_resolution_context *new_ctx= new (alloc) Name_resolution_context; + + // Note: not all fields of *ctx are initialized yet at this point. + // We will get all of the fields filled in Table_function_json_table::setup + // (search for the "Prepare the name resolution context" comment). *new_ctx= *ctx; return lex->push_context(new_ctx); } @@ -1274,8 +1276,8 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, save_is_item_list_lookup= thd->lex->current_select->is_item_list_lookup; thd->lex->current_select->is_item_list_lookup= 0; - // Prepare the name resolution context. First, copy the context that - // is using for name resolution of the WHERE clause + // Prepare the name resolution context. First, copy the context that is + // used for name resolution of the WHERE clause *m_context= thd->lex->current_select->context; // Then, restrict it to only allow to refer to tables that come before the

1 0

[Commits] 160bd1691ba: Update result for perfschema.start_server_low_digest_sql_length
by psergey 05 Apr '21

05 Apr '21

revision-id: 160bd1691ba718a5458c349fcf70e6bbad190829 (mariadb-10.5.2-567-g160bd1691ba) parent(s): 6fd4ac82ce251c3428f2041983c760967d3651e8 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-05 16:10:46 +0300 message: Update result for perfschema.start_server_low_digest_sql_length Post-rebase fix, JSON_TABLE touched the parser. --- .../suite/perfschema/r/start_server_low_digest_sql_length.result | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result index c991efce659..9ab986a1314 100644 --- a/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result +++ b/mysql-test/suite/perfschema/r/start_server_low_digest_sql_length.result @@ -8,5 +8,5 @@ SELECT 1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1+1 #################################### SELECT event_name, digest, digest_text, sql_text FROM events_statements_history_long; event_name digest digest_text sql_text -statement/sql/select ade774bdfbc132a71810ede8ef469660 SELECT ? + ? + SELECT ... -statement/sql/truncate 0f84807fb4a75d0f391f8a93e7c3c182 TRUNCATE TABLE truncat... +statement/sql/select 36b60eb4c0a269b4d8e3c2037f5c6c36 SELECT ? + ? + SELECT ... +statement/sql/truncate eddd22a5c210f96a5160acbb5d7563b3 TRUNCATE TABLE truncat...

1 0

[Commits] 6fd4ac82ce2: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set
by psergey 05 Apr '21

05 Apr '21

revision-id: 6fd4ac82ce251c3428f2041983c760967d3651e8 (mariadb-10.5.2-566-g6fd4ac82ce2) parent(s): cee7b072c2dfaa46b46e52338be5bb1bad59a8c0 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-04-05 14:15:05 +0300 message: MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set (Also fixes MDEV-25254). Re-work Name Resolution for the argument of JSON_TABLE(json_doc, ....) function. The json_doc argument can refer to other tables, but it can only refer to the tables that precede[*] the JSON_TABLE(...) call. [*] - For queries with RIGHT JOINs, the "preceding" is determined after the query is normalized by converting RIGHT JOIN into left one. The implementation is as follows: - Table function arguments use their own Name_resolution_context. - The Name_resolution_context now has a bitmap of tables that should be ignored when searching for a field. - get_disallowed_table_deps() walks the TABLE_LIST::nested_join tree and computes a bitmap of tables that do not "precede" the given JSON_TABLE(...) invocation (according the above definition of "preceding"). --- mysql-test/suite/json/r/json_table.result | 88 +++++++++++++- mysql-test/suite/json/r/json_table_mysql.result | 21 ++-- mysql-test/suite/json/t/json_table.test | 103 ++++++++++++++++- mysql-test/suite/json/t/json_table_mysql.test | 14 ++- sql/item.cc | 5 + sql/item.h | 7 ++ sql/json_table.cc | 146 +++++++++++++++++++++++- sql/json_table.h | 7 ++ sql/sql_base.cc | 11 +- sql/sql_base.h | 1 + sql/sql_help.cc | 1 + sql/sql_lex.cc | 2 +- sql/sql_lex.h | 4 +- sql/sql_parse.cc | 4 + sql/sql_select.cc | 4 +- sql/sql_yacc.yy | 19 ++- 16 files changed, 398 insertions(+), 39 deletions(-) diff --git a/mysql-test/suite/json/r/json_table.result b/mysql-test/suite/json/r/json_table.result index e9bee553542..e1c87853379 100644 --- a/mysql-test/suite/json/r/json_table.result +++ b/mysql-test/suite/json/r/json_table.result @@ -55,7 +55,7 @@ item_name item_props color Laptop {"color": "black", "price": 1000} black Jeans {"color": "blue", "price": 50} blue select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; -ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions +ERROR 42S22: Unknown column 't1.item_props' in 'JSON_TABLE argument' DROP TABLE t1; select * from JSON_TABLE( '[ {"xa": 1, "b": [11,111]}, {"a": 2, "b": [22,222]}, {"a":3}]', '$[*]' COLUMNS( a INT PATH '$.a' default '101' on empty, NESTED PATH '$.b[*]' COLUMNS (b INT PATH '$'))) as jt; a b @@ -251,8 +251,12 @@ SELECT t1.x*2 m, jt.* FROM t1, JSON_TABLE(m, '$[*]' COLUMNS (i INT PATH '$')) jt; ERROR 42S22: Unknown column 'm' in 'JSON_TABLE argument' DROP TABLE t1; -select * from json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; -ERROR 42S02: Unknown table 'JS3' in JSON_TABLE argument +select * +from +json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, +json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, +json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; +ERROR 42S22: Unknown column 'JS3.size' in 'JSON_TABLE argument' create table t1 (json varchar(100) character set utf8); insert into t1 values ('{"value":"АБВ"}'); create table tj1 as @@ -279,7 +283,7 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze Warning Engine-independent statistics are not collected for column 'f1' test.t1 analyze status OK SELECT * FROM JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN t1 AS tt3; -ERROR 42S02: Unknown table 'tt3' in JSON_TABLE argument +ERROR 42S22: Unknown column 'tt3.f1' in 'JSON_TABLE argument' SELECT * FROM t1 as jj1, (SELECT tt2.* FROM @@ -589,5 +593,81 @@ f NULL DROP TABLE t1; # +# MDEV-25254: JSON_TABLE: Inconsistent name resolution with right joins +# +CREATE TABLE t1 (a INT); +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; +ERROR 42S22: Unknown column 't1.a' in 'JSON_TABLE argument' +CREATE VIEW v AS +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; +ERROR 42S22: Unknown column 't1.a' in 'JSON_TABLE argument' +insert into t1 values (1),(2),(3); +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; +ERROR 42S22: Unknown column 't1.a' in 'JSON_TABLE argument' +drop table t1; +# +# MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set, server crash +# +CREATE TABLE t1 (o INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (3),(4); +SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 JOIN t2; +ERROR 42S22: Unknown column 'a' in 'JSON_TABLE argument' +SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 STRAIGHT_JOIN t2; +ERROR 42S22: Unknown column 'a' in 'JSON_TABLE argument' +drop table t1,t2; +# Now, try a JSON_TABLE that has a subquery that has an outside reference: +create table t1(a int, js varchar(32)); +create table t2(a varchar(100)); +insert into t2 values(''); +explain +select * +from +t1 left join +json_table(concat('',js), +'$' columns ( color varchar(32) path '$.color') +) as JT on 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +explain +select * +from +t1 right join +json_table(concat('',js), +'$' columns ( color varchar(32) path '$.color') +) as JT on 1; +ERROR 42S22: Unknown column 'js' in 'JSON_TABLE argument' +explain +select * +from +t1 left join +json_table((select concat(a,js) from t2), +'$' columns ( color varchar(32) path '$.color') +) as JT on 1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +2 SUBQUERY t2 system NULL NULL NULL NULL 1 +explain +select * +from +t1 right join +json_table((select concat(a,js) from t2), +'$' columns ( color varchar(32) path '$.color') +) as JT on 1; +ERROR 42S22: Unknown column 'js' in 'field list' +drop table t1,t2; +# +# MDEV-25256: JSON_TABLE: Error ER_VIEW_INVALID upon running query via view +# +SELECT * FROM +JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1 +RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2 +ON(1) +RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3 +ON(1) +WHERE 0; +ERROR 42S22: Unknown column 'jt1.a' in 'JSON_TABLE argument' +# # End of 10.6 tests # diff --git a/mysql-test/suite/json/r/json_table_mysql.result b/mysql-test/suite/json/r/json_table_mysql.result index 24cc5fa64b6..ffe94da2750 100644 --- a/mysql-test/suite/json/r/json_table_mysql.result +++ b/mysql-test/suite/json/r/json_table_mysql.result @@ -640,13 +640,13 @@ DEALLOCATE PREPARE stmt; CREATE TABLE t1 (id INT, jc JSON); SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; -id jc id +ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE argument' SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id; -ERROR 42S02: Unknown table 't1' in JSON_TABLE argument +ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE argument' SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id; -ERROR 42S02: Unknown table 't1' in JSON_TABLE argument +id id jc SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; id jc id @@ -667,34 +667,33 @@ SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; -id jc id jc id +ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE argument' SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; -id jc id jc id +ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE argument' WITH qn AS (SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id) SELECT * from qn; -id +ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE argument' WITH qn AS (SELECT 1 UNION SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id) SELECT * from qn; -1 -1 +ERROR 42S22: Unknown column 't1.jc' in 'JSON_TABLE argument' SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; -id jc id jc id +ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE argument' SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; -id jc id jc id +ERROR 42S22: Unknown column 't1o.jc' in 'JSON_TABLE argument' INSERT INTO t1 VALUES(1,"1"),(2,"4"),(3,"3"); ANALYZE TABLE t1; Table Op Msg_type Msg_text @@ -717,7 +716,7 @@ LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id RIGHT JOIN JSON_TABLE(jt.id, '$' COLUMNS (id FOR ORDINALITY)) as jt1 ON jt.id=jt1.id; -ERROR 42000: Cross dependency found in OUTER JOIN; examine your ON conditions +ERROR 42S22: Unknown column 'jt.id' in 'JSON_TABLE argument' DROP TABLE t1; # # Bug#25525409: ASSERTION `TABLE_LIST->TABLE' FAILED IN SQL/SQL_BASE.CC diff --git a/mysql-test/suite/json/t/json_table.test b/mysql-test/suite/json/t/json_table.test index 151d35e1424..ad3770e84ce 100644 --- a/mysql-test/suite/json/t/json_table.test +++ b/mysql-test/suite/json/t/json_table.test @@ -25,7 +25,7 @@ insert into t1 values ('Jeans', '{"color": "blue", "price": 50}'); select * from t1 left join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; ---error ER_WRONG_OUTER_JOIN +--error ER_BAD_FIELD_ERROR select * from t1 right join json_table(t1.item_props,'$' columns( color varchar(100) path '$.color')) as T on 1; DROP TABLE t1; @@ -173,8 +173,12 @@ SELECT t1.x*2 m, jt.* FROM t1, JSON_TABLE(m, '$[*]' COLUMNS (i INT PATH '$')) jt; DROP TABLE t1; ---error ER_UNKNOWN_TABLE -select * from json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; +--error ER_BAD_FIELD_ERROR +select * +from + json_table(JS3.size, '$' columns (size INT PATH '$.size')) as JS1, + json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS2, + json_table(JS1.size, '$' columns (size INT PATH '$.size')) as JS3 where 1; create table t1 (json varchar(100) character set utf8); insert into t1 values ('{"value":"АБВ"}'); @@ -195,7 +199,7 @@ INSERT INTO t1 VALUES (6, '{\"1\": 6}'); ANALYZE TABLE t1; ---error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM JSON_TABLE(tt3.f1, "$" COLUMNS (id FOR ORDINALITY)) AS tbl STRAIGHT_JOIN t1 AS tt3; --error ER_BAD_FIELD_ERROR SELECT * FROM t1 as jj1, @@ -460,6 +464,97 @@ CREATE TABLE t1 AS SELECT * FROM JSON_TABLE('{"x":1}', '$' COLUMNS(f DATE PATH ' SELECT * FROM t1; DROP TABLE t1; +--echo # +--echo # MDEV-25254: JSON_TABLE: Inconsistent name resolution with right joins +--echo # +CREATE TABLE t1 (a INT); +--error ER_BAD_FIELD_ERROR +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; + +--error ER_BAD_FIELD_ERROR +CREATE VIEW v AS +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; + +insert into t1 values (1),(2),(3); +--error ER_BAD_FIELD_ERROR +SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.a,'$' COLUMNS(o FOR ORDINALITY)) jt ON TRUE; + +drop table t1; +--echo # +--echo # MDEV-25202: JSON_TABLE: Early table reference leads to unexpected result set, server crash +--echo # +CREATE TABLE t1 (o INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (3),(4); + +--error ER_BAD_FIELD_ERROR +SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 JOIN t2; + +--error ER_BAD_FIELD_ERROR +SELECT * FROM JSON_TABLE(a, '$' COLUMNS(o FOR ORDINALITY)) AS jt1 NATURAL JOIN t1 STRAIGHT_JOIN t2; + +drop table t1,t2; + +--echo # Now, try a JSON_TABLE that has a subquery that has an outside reference: + +create table t1(a int, js varchar(32)); + +create table t2(a varchar(100)); +insert into t2 values(''); + +# First, without subquery: +explain +select * +from + t1 left join + json_table(concat('',js), + '$' columns ( color varchar(32) path '$.color') + ) as JT on 1; + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 right join + json_table(concat('',js), + '$' columns ( color varchar(32) path '$.color') + ) as JT on 1; + +# Now, with subquery: +explain +select * +from + t1 left join + json_table((select concat(a,js) from t2), + '$' columns ( color varchar(32) path '$.color') + ) as JT on 1; + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 right join + json_table((select concat(a,js) from t2), + '$' columns ( color varchar(32) path '$.color') + ) as JT on 1; + +drop table t1,t2; + +--echo # +--echo # MDEV-25256: JSON_TABLE: Error ER_VIEW_INVALID upon running query via view +--echo # + +--error ER_BAD_FIELD_ERROR +SELECT * FROM +JSON_TABLE('[]', '$' COLUMNS(a TEXT PATH '$[*]')) AS jt1 + RIGHT JOIN JSON_TABLE(jt1.a, '$' COLUMNS(o2 FOR ORDINALITY)) AS jt2 + ON(1) + RIGHT JOIN JSON_TABLE('[]', '$' COLUMNS(o3 FOR ORDINALITY)) AS jt3 + ON(1) +WHERE 0; + --echo # --echo # End of 10.6 tests diff --git a/mysql-test/suite/json/t/json_table_mysql.test b/mysql-test/suite/json/t/json_table_mysql.test index 821d262283d..eebc4ba11ee 100644 --- a/mysql-test/suite/json/t/json_table_mysql.test +++ b/mysql-test/suite/json/t/json_table_mysql.test @@ -549,15 +549,17 @@ CREATE TABLE t1 (id INT, jc JSON); # psergey! #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id; # psergey! ---error ER_UNKNOWN_TABLE +#--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt LEFT JOIN t1 ON t1.jc=jt.id; ---error ER_UNKNOWN_TABLE +#--error ER_UNKNOWN_TABLE SELECT * FROM JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt RIGHT JOIN t1 ON t1.jc=jt.id; @@ -580,6 +582,7 @@ EXPLAIN SELECT * FROM t1 t1o RIGHT JOIN t1 ON t1o.id=t1.id # psergey: #--error ER_BAD_FIELD_ERROR +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) @@ -587,6 +590,7 @@ SELECT * FROM t1 AS t1o RIGHT JOIN # psergey: #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) @@ -594,12 +598,14 @@ SELECT * FROM t1 AS t1o RIGHT JOIN # psergey: #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR WITH qn AS (SELECT jt.* FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id) SELECT * from qn; #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR WITH qn AS (SELECT 1 UNION SELECT jt.id FROM t1 RIGHT JOIN JSON_TABLE(t1.jc, '$' COLUMNS @@ -607,12 +613,14 @@ WITH qn AS SELECT * from qn; #--error ER_BAD_FIELD_ERROR +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) ON t1o.id=t1i.id; #--error ER_UNKNOWN_TABLE +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 AS t1o RIGHT JOIN (t1 AS t1i RIGHT JOIN JSON_TABLE(t1o.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1i.jc=jt.id) @@ -628,7 +636,7 @@ SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS EXPLAIN SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id INT PATH '$')) as jt ON t1.id=jt.id; ---error ER_WRONG_OUTER_JOIN +--error ER_BAD_FIELD_ERROR SELECT * FROM t1 LEFT JOIN JSON_TABLE(t1.jc, '$' COLUMNS (id FOR ORDINALITY)) as jt ON t1.jc=jt.id diff --git a/sql/item.cc b/sql/item.cc index 3dbe3badadf..18bb26e18eb 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -5534,6 +5534,8 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) first_name_resolution_table, outer_context-> last_name_resolution_table, + outer_context-> + ignored_tables, reference, IGNORE_EXCEPT_NON_UNIQUE, TRUE, TRUE)) != @@ -5679,6 +5681,7 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) find_field_in_tables(thd, this, context->first_name_resolution_table, context->last_name_resolution_table, + context->ignored_tables, reference, REPORT_ALL_ERRORS, !any_privileges, TRUE); } @@ -5845,6 +5848,7 @@ bool Item_field::fix_fields(THD *thd, Item **reference) if ((from_field= find_field_in_tables(thd, this, context->first_name_resolution_table, context->last_name_resolution_table, + context->ignored_tables, reference, thd->lex->use_only_table_context ? REPORT_ALL_ERRORS : @@ -7904,6 +7908,7 @@ bool Item_ref::fix_fields(THD *thd, Item **reference) first_name_resolution_table, outer_context-> last_name_resolution_table, + outer_context->ignored_tables, reference, IGNORE_EXCEPT_NON_UNIQUE, TRUE, TRUE); diff --git a/sql/item.h b/sql/item.h index a1c288ab1f0..747f366e33d 100644 --- a/sql/item.h +++ b/sql/item.h @@ -232,6 +232,12 @@ struct Name_resolution_context: Sql_alloc */ bool resolve_in_select_list; + /* + Bitmap of tables that should be ignored when doing name resolution. + Normally it is {0}. Non-zero values are used by table functions. + */ + table_map ignored_tables; + /* Security context of this name resolution context. It's used for views and is non-zero only if the view is defined with SQL SECURITY DEFINER. @@ -241,6 +247,7 @@ struct Name_resolution_context: Sql_alloc Name_resolution_context() :outer_context(0), table_list(0), select_lex(0), error_processor_data(0), + ignored_tables(0), security_ctx(0) {} diff --git a/sql/json_table.cc b/sql/json_table.cc index 95ca1aae3f5..9fd647dc6c5 100644 --- a/sql/json_table.cc +++ b/sql/json_table.cc @@ -22,6 +22,7 @@ #include "item_jsonfunc.h" #include "json_table.h" #include "sql_show.h" +#include "sql_select.h" #define HA_ERR_JSON_TABLE (HA_ERR_LAST+1) @@ -40,6 +41,111 @@ class table_function_handlerton static table_function_handlerton table_function_hton; +/* + @brief + Collect a bitmap of tables that a given table function cannot have + references to. + + @detail + According to the SQL standard, a table function can refer to any table + that's "preceding" it in the FROM clause. + + The other limitation we would like to enforce is that the inner side of + an outer join cannot refer to the outer side. An example: + + SELECT * from JSON_TABLE(t1.col, ...) left join t1 on ... + + This function implements both of the above restrictions. + + Basic idea: the "join_list" contains the tables in the order that's a + reverse of the order they were specified in the query. + If we walk the join_list, we will encounter: + 1. First, the tables that table function cannot refer to (collect them in a + bitmap) + 2. Then, the table function itself (put it in the bitmap, too, as self- + references are not allowed, and stop the walk) + 3. Tables that the table function CAN refer to (we don't walk these as + we've stopped on step #2). + + The above can be applied recursively for nested joins (this covers NATURAL + JOIN, and JOIN ... USING constructs). + + Enforcing the "refer to only preceding tables" rule means that outer side + of LEFT JOIN cannot refer to the inner side. + + Handing RIGHT JOINs: There are no RIGHT JOINs in the join_list data + structures. They were converted to LEFT JOINs (see calls to st_select_lex:: + convert_right_join). This conversion changes the order of tables, but + we are ok with operating on the tables "in the left join order". + + @return + TRUE - enumeration has found the Table Function instance. The bitmap is + ready. + FALSE - Otherwise + +*/ + +static +bool get_disallowed_table_deps_for_list(table_map table_func_bit, + List<TABLE_LIST> *join_list, + table_map *disallowed_tables) +{ + TABLE_LIST *table; + NESTED_JOIN *nested_join; + List_iterator<TABLE_LIST> li(*join_list); + + while ((table= li++)) + { + if ((nested_join= table->nested_join)) + { + if (get_disallowed_table_deps_for_list(table_func_bit, + &nested_join->join_list, + disallowed_tables)) + return true; + } + else + { + *disallowed_tables |= table->table->map; + if (table_func_bit == table->table->map) + { + // This is the JSON_TABLE(...) that are we're computing dependencies + // for. + return true; + } + } + } + return false; +} + + +/* + @brief + Given a join and a table function in it (specified by its table_func_bit), + produce a bitmap of tables that the table function can NOT have references + to. + + @detail + See get_disallowed_table_deps_for_list + + @return + Bitmap of tables that table function can NOT have references to. +*/ + +static +table_map get_disallowed_table_deps(JOIN *join, table_map table_func_bit) +{ + table_map disallowed_tables= 0; + if (get_disallowed_table_deps_for_list(table_func_bit, join->join_list, + &disallowed_tables)) + return disallowed_tables; + else + { + DBUG_ASSERT(0); + return disallowed_tables; + } +} + + /* A table that produces output rows for JSON_TABLE(). @@ -1113,6 +1219,34 @@ void Table_function_json_table::end_nested_path() } +/* + @brief Create a name resolution context for doing name resolution in table + function argument. + + @seealso + push_new_name_resolution_context +*/ + +bool push_table_function_arg_context(LEX *lex, MEM_ROOT *alloc) +{ + // Walk the context stack until we find a context that is select-level + // context. + List_iterator<Name_resolution_context> it(lex->context_stack); + Name_resolution_context *ctx= NULL; + while ((ctx= it++)) + { + if (ctx->select_lex && ctx == &ctx->select_lex->context) + break; + } + DBUG_ASSERT(ctx); + + // Then, create a copy of it and return it. + Name_resolution_context *new_ctx= new (alloc) Name_resolution_context; + *new_ctx= *ctx; + return lex->push_context(new_ctx); +} + + /* @brief Perform name-resolution phase tasks @@ -1139,9 +1273,17 @@ int Table_function_json_table::setup(THD *thd, TABLE_LIST *sql_table, bool res; save_is_item_list_lookup= thd->lex->current_select->is_item_list_lookup; thd->lex->current_select->is_item_list_lookup= 0; - s_lex->end_lateral_table= sql_table; + + // Prepare the name resolution context. First, copy the context that + // is using for name resolution of the WHERE clause + *m_context= thd->lex->current_select->context; + + // Then, restrict it to only allow to refer to tables that come before the + // table function reference + m_context->ignored_tables= get_disallowed_table_deps(s_lex->join, t->map); + res= m_json->fix_fields_if_needed(thd, &m_json); - s_lex->end_lateral_table= NULL; + thd->lex->current_select->is_item_list_lookup= save_is_item_list_lookup; if (res) return TRUE; diff --git a/sql/json_table.h b/sql/json_table.h index c9df5d8322a..07d53c55638 100644 --- a/sql/json_table.h +++ b/sql/json_table.h @@ -224,6 +224,10 @@ class Table_function_json_table : public Sql_alloc void start_nested_path(Json_table_nested_path *np); void end_nested_path(); Json_table_nested_path *get_cur_nested_path() { return cur_parent; } + void set_name_resolution_context(Name_resolution_context *arg) + { + m_context= arg; + } /* SQL Parser: current column in JSON_TABLE (...) syntax */ Json_table_column *m_cur_json_table_column; @@ -232,6 +236,8 @@ class Table_function_json_table : public Sql_alloc CHARSET_INFO *m_text_literal_cs; private: + /* Context to be used for resolving the first argument. */ + Name_resolution_context *m_context; /* the JSON argument can be taken from other tables. @@ -251,6 +257,7 @@ class Table_function_json_table : public Sql_alloc Json_table_nested_path **last_sibling_hook; }; +bool push_table_function_arg_context(LEX *lex, MEM_ROOT *alloc); TABLE *create_table_for_function(THD *thd, TABLE_LIST *sql_table); diff --git a/sql/sql_base.cc b/sql/sql_base.cc index fd945ec9d5d..c30c94fb799 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -6315,6 +6315,8 @@ Field *find_field_in_table_sef(TABLE *table, const char *name) first_table list of tables to be searched for item last_table end of the list of tables to search for item. If NULL then search to the end of the list 'first_table'. + ignored_tables Bitmap of tables that should be ignored. Do not try + to find the field in those. ref if 'item' is resolved to a view field, ref is set to point to the found view field report_error Degree of error reporting: @@ -6342,6 +6344,7 @@ Field *find_field_in_table_sef(TABLE *table, const char *name) Field * find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *first_table, TABLE_LIST *last_table, + table_map ignored_tables, Item **ref, find_item_error_report_type report_error, bool check_privileges, bool register_tree_change) { @@ -6458,15 +6461,15 @@ find_field_in_tables(THD *thd, Item_ident *item, db= name_buff; } - if (first_table && first_table->select_lex && - first_table->select_lex->end_lateral_table) - last_table= first_table->select_lex->end_lateral_table; - else if (last_table) + if (last_table) last_table= last_table->next_name_resolution_table; for (; cur_table != last_table ; cur_table= cur_table->next_name_resolution_table) { + if (cur_table->table && (cur_table->table->map & ignored_tables)) + continue; + Field *cur_field= find_field_in_table_ref(thd, cur_table, name, length, item->name.str, db, table_name, ref, (thd->lex->sql_command == diff --git a/sql/sql_base.h b/sql/sql_base.h index ccfacaf9086..765bf426b45 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -195,6 +195,7 @@ bool fill_record(THD *thd, TABLE *table, Field **field, List<Item> &values, Field * find_field_in_tables(THD *thd, Item_ident *item, TABLE_LIST *first_table, TABLE_LIST *last_table, + table_map ignored_tables, Item **ref, find_item_error_report_type report_error, bool check_privileges, bool register_tree_change); Field * diff --git a/sql/sql_help.cc b/sql/sql_help.cc index ebda874d1fe..916315cf67e 100644 --- a/sql/sql_help.cc +++ b/sql/sql_help.cc @@ -99,6 +99,7 @@ static bool init_fields(THD *thd, TABLE_LIST *tables, Lex_cstring_strlen(find_fields->table_name), Lex_cstring_strlen(find_fields->field_name))); if (!(find_fields->field= find_field_in_tables(thd, field, tables, NULL, + table_map(0), 0, REPORT_ALL_ERRORS, 1, TRUE))) DBUG_RETURN(1); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index baf243484b6..c6a6d4fb92e 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -10103,7 +10103,7 @@ SELECT_LEX *LEX::parsed_subselect(SELECT_LEX_UNIT *unit) (curr_sel == NULL && current_select == &builtin_select)); if (curr_sel) { - curr_sel->register_unit(unit, &curr_sel->context); + curr_sel->register_unit(unit, context_stack.head()); curr_sel->add_statistics(unit); } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 2a3312a02f2..5ce9e0ac409 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1184,8 +1184,6 @@ class st_select_lex: public st_select_lex_node enum leaf_list_state {UNINIT, READY, SAVED}; enum leaf_list_state prep_leaf_list_state; uint insert_tables; - // Last table for LATERAL join, used by table functions - TABLE_LIST *end_lateral_table; st_select_lex *merged_into; /* select which this select is merged into */ /* (not 0 only for views/derived tables) */ @@ -1442,7 +1440,7 @@ class st_select_lex: public st_select_lex_node ha_rows get_limit(); friend struct LEX; - st_select_lex() : group_list_ptrs(NULL), end_lateral_table(NULL), braces(0), + st_select_lex() : group_list_ptrs(NULL), braces(0), automatic_brackets(0), n_sum_items(0), n_child_sum_items(0) {} void make_empty_select() diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 5659aacc6af..46bd119b106 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -8990,6 +8990,10 @@ bool st_select_lex_unit::add_fake_select_lex(THD *thd_arg) @param left_op left operand of the JOIN @param right_op rigth operand of the JOIN + @seealso + push_table_function_arg_context() serves similar purpose for table + functions + @retval FALSE if all is OK @retval diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6eb7e69343d..7ec2232a350 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -24563,8 +24563,8 @@ find_order_in_list(THD *thd, Ref_ptr_array ref_pointer_array, order_item_type == Item::REF_ITEM) { from_field= find_field_in_tables(thd, (Item_ident*) order_item, tables, - NULL, &view_ref, IGNORE_ERRORS, FALSE, - FALSE); + NULL, table_map(0), &view_ref, + IGNORE_ERRORS, FALSE, FALSE); if (!from_field) from_field= (Field*) not_found_field; } diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index e53321af3cc..8483ca5ff50 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -11674,29 +11674,38 @@ json_on_empty_response: ; table_function: - JSON_TABLE_SYM '(' expr ',' + JSON_TABLE_SYM '(' + { + push_table_function_arg_context(Lex, thd->mem_root); + //TODO: introduce IN_TABLE_FUNC_ARGUMENT? + Select->parsing_place= IN_ON; + } + expr ',' { Table_function_json_table *jt= - new (thd->mem_root) Table_function_json_table($3); + new (thd->mem_root) Table_function_json_table($4); if (unlikely(!jt)) MYSQL_YYABORT; Lex->json_table= jt; + + Select->parsing_place= NO_MATTER; + jt->set_name_resolution_context(Lex->pop_context()); } json_text_literal json_table_columns_clause ')' opt_table_alias_clause { SELECT_LEX *sel= Select; - if (unlikely($9 == NULL)) + if (unlikely($10 == NULL)) { /* Alias is not optional. */ my_error(ER_JSON_TABLE_ALIAS_REQUIRED, MYF(0)); MYSQL_YYABORT; } - if (unlikely(Lex->json_table->m_nested_path.set_path(thd, $6))) + if (unlikely(Lex->json_table->m_nested_path.set_path(thd, $7))) MYSQL_YYABORT; sel->table_join_options= 0; if (!($$= Select->add_table_to_list(thd, new (thd->mem_root) Table_ident(thd, &empty_clex_str, - $9, TRUE), + $10, TRUE), NULL, Select->get_table_join_options() | TL_OPTION_TABLE_FUNCTION,

1 0

[Commits] c0eac8af63f: MDEV-25206 Crash with CREATE VIEW .. SELECT with non-existing field
by psergey 04 Apr '21

04 Apr '21

revision-id: c0eac8af63f57b19107b9d4cde35138d2b68c200 (mariadb-10.5.2-487-gc0eac8af63f) parent(s): 0f649474adac7f0a7f1f5d1b932ca620c4ea3aa4 author: Igor Babaev committer: Sergei Petrunia timestamp: 2021-04-04 22:05:32 +0300 message: MDEV-25206 Crash with CREATE VIEW .. SELECT with non-existing field in ON condition The fix of the bug MDEV-25002 for 10.4 turned out to be incomplete. It caused crashes when executing CREATE VIEW, CREATE TABLE .. SELECT, INSERT .. SELECT statements if their SELECTs contained references to non-existing fields. This patch complements the fix for MDEV-25002 in order to avoid such crashes. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/main/view.result | 16 ++++++++++++++++ mysql-test/main/view.test | 21 +++++++++++++++++++++ sql/item.cc | 5 +++-- 3 files changed, 40 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 35e607ad43c..fc6309baa7a 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6827,3 +6827,19 @@ Drop table t1; # # End of 10.3 tests # +# +# MDEV-25206: view specification contains unknown column reference +# +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c int); +CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +INSERT INTO t3 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +CREATE TABLE t4 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +ERROR 42S22: Unknown column 't1.x' in 'on clause' +DROP TABLE t1,t2,t3; +# End of 10.4 tests diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 78ab6a98ebb..3325d4e6dd5 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6542,3 +6542,24 @@ Drop table t1; --echo # --echo # End of 10.3 tests --echo # + +--echo # +--echo # MDEV-25206: view specification contains unknown column reference +--echo # + +CREATE TABLE t1 (a int); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b int); +INSERT INTO t2 VALUES (2),(3); +CREATE TABLE t3 (c int); + +--error ER_BAD_FIELD_ERROR +CREATE VIEW v1 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +--error ER_BAD_FIELD_ERROR +INSERT INTO t3 SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; +--error ER_BAD_FIELD_ERROR +CREATE TABLE t4 AS SELECT * FROM t1 JOIN t2 ON t1.x > t2.b; + +DROP TABLE t1,t2,t3; + +--echo # End of 10.4 tests diff --git a/sql/item.cc b/sql/item.cc index 7caa9e41e2f..b075e6b9720 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -5493,8 +5493,9 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference) Name_resolution_context *outer_context= 0; SELECT_LEX *select= 0; /* Currently derived tables cannot be correlated */ - if (current_sel->master_unit()->first_select()->get_linkage() != - DERIVED_TABLE_TYPE) + if ((current_sel->master_unit()->first_select()->get_linkage() != + DERIVED_TABLE_TYPE) && + current_sel->master_unit()->outer_select()) outer_context= context->outer_context; /*

1 0

[Commits] 0f649474ada: MDEV-25002 ON expressions cannot contain outer references (Cherry-pick into 10.6-json_table)
by psergey 04 Apr '21

04 Apr '21

revision-id: 0f649474adac7f0a7f1f5d1b932ca620c4ea3aa4 (mariadb-10.5.2-486-g0f649474ada) parent(s): cbc2296a8c53d67685da97041c391a8701c34291 author: Igor Babaev committer: Sergei Petrunia timestamp: 2021-04-04 22:04:57 +0300 message: MDEV-25002 ON expressions cannot contain outer references (Cherry-pick into 10.6-json_table) A bogus error message was issued for any outer references occurred in ON expressions used in subqueries. This prevented execution of queries containing subqueries as soon as they used outer references in their ON clauses. This happened because the Name_resolution_context structure created for any ON expression erroneously had the field outer_context set to NULL. The fields select_lex of this structure was not set correctly either. The idea of the fix was taken from mysql code of the function push_new_name_resolution_context(). Approved by dmitry.shulga(a)mariadb.com --- mysql-test/main/subselect.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect.test | 35 ++++++++++++++++++++++ mysql-test/main/subselect_no_exists_to_in.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_mat.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_opts.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_scache.result | 38 ++++++++++++++++++++++++ mysql-test/main/subselect_no_semijoin.result | 38 ++++++++++++++++++++++++ sql/sql_parse.cc | 4 ++- 8 files changed, 266 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 1ee4718ef10..167382fc328 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -7342,6 +7342,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index f6333c22684..a58a08e1a58 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -6169,6 +6169,41 @@ SELECT * FROM t t1 RIGHT JOIN t t2 ON (t2.pk = t1.pk) DROP TABLE t; +--echo # +--echo # MDEV-25002: Outer reference in ON clause of subselect +--echo # + +create table t1 ( + pk int primary key, + a int +) engine=myisam; +insert into t1 values (1,1), (2,2); + +create table t2 ( + pk int primary key, + b int +) engine=myisam; +insert into t2 values (1,1), (2,3); + +create table t3 (a int); +insert into t3 values (1),(2); + +select a, + (select count(*) from t1, t2 + where t2.pk=t3.a and t1.pk=1) as sq +from t3; +select a, + (select count(*) from t1 join t2 on t2.pk=t3.a + where t1.pk=1) as sq +from t3; + +select a from t3 + where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +select a from t3 + where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); + +drop table t1,t2,t3; + --echo # End of 10.2 tests diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 1436f98d9f8..70add4d1740 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -7342,6 +7342,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 8100c1da7e5..f0c7033803c 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -7335,6 +7335,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 86c89f9bd5f..8ff79c1790b 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -7333,6 +7333,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 5dc8040698f..37ca0d4c030 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -7348,6 +7348,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index f9bbf2e00c4..1abdde5ff7f 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -7333,6 +7333,44 @@ WHERE (t2.i, t2.pk) NOT IN ( SELECT t3.i, t3.i FROM t t3, t t4 ) AND t1.c = 'foo pk i c pk i c 1 10 foo 1 10 foo DROP TABLE t; +# +# MDEV-25002: Outer reference in ON clause of subselect +# +create table t1 ( +pk int primary key, +a int +) engine=myisam; +insert into t1 values (1,1), (2,2); +create table t2 ( +pk int primary key, +b int +) engine=myisam; +insert into t2 values (1,1), (2,3); +create table t3 (a int); +insert into t3 values (1),(2); +select a, +(select count(*) from t1, t2 +where t2.pk=t3.a and t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a, +(select count(*) from t1 join t2 on t2.pk=t3.a +where t1.pk=1) as sq +from t3; +a sq +1 1 +2 1 +select a from t3 +where a in (select t2.b from t1,t2 where t2.pk=t3.a and t1.pk=1); +a +1 +select a from t3 +where a in (select t2.b from t1 join t2 on t2.pk=t3.a where t1.pk=1); +a +1 +drop table t1,t2,t3; # End of 10.2 tests # # Start of 10.4 tests diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index ed2bcb46949..a0853f13836 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -9002,7 +9002,9 @@ push_new_name_resolution_context(THD *thd, left_op->first_leaf_for_name_resolution(); on_context->last_name_resolution_table= right_op->last_leaf_for_name_resolution(); - return thd->lex->push_context(on_context); + on_context->select_lex = thd->lex->current_select; + on_context->outer_context = thd->lex->current_context()->outer_context; + return thd->lex->push_context(on_context, thd->mem_root); }

1 0

[Commits] eb2d36296eb: MDEV-25305: MyRocks: Killing server during RESET MASTER can lose last transactions
by psergey 30 Mar '21

30 Mar '21

revision-id: eb2d36296eb2f2f653ea69e78d7a471ee935d2a4 (mariadb-10.5.2-537-geb2d36296eb) parent(s): ed221980fcd391dd349d80ea5b90b84a62ce043c author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-03-30 18:47:05 +0300 message: MDEV-25305: MyRocks: Killing server during RESET MASTER can lose last transactions Followup: the test requires debug sync facility --- storage/rocksdb/mysql-test/rocksdb/t/binlog_rotate_crash.test | 1 + 1 file changed, 1 insertion(+) diff --git a/storage/rocksdb/mysql-test/rocksdb/t/binlog_rotate_crash.test b/storage/rocksdb/mysql-test/rocksdb/t/binlog_rotate_crash.test index d2261f508f8..dd42001b89e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/binlog_rotate_crash.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/binlog_rotate_crash.test @@ -1,5 +1,6 @@ --source include/have_rocksdb.inc --source include/have_log_bin.inc +--source include/have_debug_sync.inc --echo # --echo # MDEV-25305: MyRocks: Killing server during RESET MASTER can lose last transactions

1 0