diff options
author | Varun Gupta <varun.gupta@mariadb.com> | 2019-10-05 14:22:36 +0530 |
---|---|---|
committer | Varun Gupta <varun.gupta@mariadb.com> | 2020-02-09 20:41:05 +0530 |
commit | 6acca3f7e4c33c93e9fffc17360bdecc301dff4b (patch) | |
tree | 9ac7133b8a70c2f6a19870ed52e7241fcfa088eb | |
parent | f8349447144255bda13e9f18d2e90dd8fe50bb3a (diff) | |
download | mariadb-git-6acca3f7e4c33c93e9fffc17360bdecc301dff4b.tar.gz |
Sort nest not allowed inside dependent subqueries
-rw-r--r-- | mysql-test/main/sort_nest.result | 122 | ||||
-rw-r--r-- | mysql-test/main/sort_nest.test | 66 | ||||
-rw-r--r-- | sql/sql_sort_nest.cc | 3 |
3 files changed, 184 insertions, 7 deletions
diff --git a/mysql-test/main/sort_nest.result b/mysql-test/main/sort_nest.result index 11bfd8ad1db..1c270613999 100644 --- a/mysql-test/main/sort_nest.result +++ b/mysql-test/main/sort_nest.result @@ -1794,14 +1794,16 @@ drop table t0,t1,t2; # CREATE TABLE t0 (a int); INSERT INTO t0 SELECT seq-1 from seq_1_to_10; -CREATE TABLE t1 (a int, b int); -INSERT INTO t1 SELECT a, a from t0 where a <5; +CREATE TABLE t1 (a int, b int, c int); +INSERT INTO t1 SELECT a, a, a from t0 where a <5; CREATE TABLE t2 as SELECT * from t1 where a < 5; CREATE TABLE t3(a int, b int, c int, key(a)); INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_10; +CREATE TABLE t4 as SELECT * from t1 where a < 5; ANALYZE TABLE t1 PERSISTENT FOR ALL; ANALYZE TABLE t2 PERSISTENT FOR ALL; ANALYZE TABLE t3 PERSISTENT FOR ALL; +ANALYZE TABLE t4 PERSISTENT FOR ALL; set use_sort_nest=1; EXPLAIN SELECT t1.a, t2.b, t1.b, t1.b + t2.b FROM t1,t2, t3 @@ -2120,4 +2122,118 @@ a b b t1.b + t2.b 2 2 2 4 3 3 3 6 4 4 4 8 -drop table t0,t1,t2,t3; +# +# Dependent subqueries cannot have a sort-nest +# +set use_sort_nest=1; +EXPLAIN SELECT * +FROM t1 +WHERE +t1.b = (select t2.b from t2,t3,t4 +where t2.a=t1.a and t3.b=t4.b +ORDER BY t2.c,t3.c limit 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +2 DEPENDENT SUBQUERY t4 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) +2 DEPENDENT SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (incremental, BNL join) +SELECT * +FROM t1 +WHERE +t1.b = (select t2.b from t2,t3,t4 +where t2.a=t1.a and t3.b=t4.b +ORDER BY t2.c,t3.c limit 1); +a b c +0 0 0 +1 1 1 +2 2 2 +3 3 3 +4 4 4 +set use_sort_nest= 0; +SELECT * +FROM t1 +WHERE +t1.b = (select t2.b from t2,t3,t4 +where t2.a=t1.a and t3.b=t4.b +ORDER BY t2.c,t3.c limit 1); +a b c +0 0 0 +1 1 1 +2 2 2 +3 3 3 +4 4 4 +# +# Independent subqueries can have a sort-nest +# +set use_sort_nest=1; +EXPLAIN SELECT * +FROM t1 +WHERE +t1.b = (select t2.b from t2,t3,t4 +where t2.a=t3.a and t3.b=t4.b +ORDER BY t2.c,t3.c limit 1); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +2 SUBQUERY t2 ALL NULL NULL NULL NULL 5 Using where +2 SUBQUERY t3 ref a a 5 test.t2.a 1 +2 SUBQUERY <sort-nest> ALL NULL NULL NULL NULL 1 Using filesort +2 SUBQUERY t4 ALL NULL NULL NULL NULL 5 Using where +SELECT * +FROM t1 +WHERE +t1.b = (select t2.b from t2,t3,t4 +where t2.a=t3.a and t3.b=t4.b +ORDER BY t2.c,t3.c limit 1); +a b c +0 0 0 +set use_sort_nest= 0; +SELECT * +FROM t1 +WHERE +t1.b = (select t2.b from t2,t3,t4 +where t2.a=t3.a and t3.b=t4.b +ORDER BY t2.c,t3.c limit 1); +a b c +0 0 0 +# +# Sort nest inside a derived table +# +set use_sort_nest=1; +EXPLAIN SELECT * +FROM (SELECT t1.a as t, t2.b as u, t1.b as v, t3.a as x +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t2.b=t3.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5)q; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 +2 DERIVED t1 ALL NULL NULL NULL NULL 5 +2 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +2 DERIVED <sort-nest> ALL NULL NULL NULL NULL 5 Using filesort +2 DERIVED t3 ref a a 5 sort-nest.b 1 Using index +SELECT * +FROM (SELECT t1.a as t, t2.b as u, t1.b as v, t3.a as x +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t2.b=t3.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5)q; +t u v x +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +set use_sort_nest=0; +SELECT * +FROM (SELECT t1.a as t, t2.b as u, t1.b as v, t3.a as x +FROM t1,t2,t3 +WHERE t1.a=t2.a AND t2.b=t3.a +ORDER BY t2.b DESC, t1.b DESC +LIMIT 5)q; +t u v x +4 4 4 4 +3 3 3 3 +2 2 2 2 +1 1 1 1 +0 0 0 0 +drop table t0,t1,t2,t3,t4; diff --git a/mysql-test/main/sort_nest.test b/mysql-test/main/sort_nest.test index a26dba836fe..b604506fb42 100644 --- a/mysql-test/main/sort_nest.test +++ b/mysql-test/main/sort_nest.test @@ -575,16 +575,18 @@ drop table t0,t1,t2; CREATE TABLE t0 (a int); INSERT INTO t0 SELECT seq-1 from seq_1_to_10; -CREATE TABLE t1 (a int, b int); -INSERT INTO t1 SELECT a, a from t0 where a <5; +CREATE TABLE t1 (a int, b int, c int); +INSERT INTO t1 SELECT a, a, a from t0 where a <5; CREATE TABLE t2 as SELECT * from t1 where a < 5; CREATE TABLE t3(a int, b int, c int, key(a)); INSERT INTO t3 SELECT seq-1, seq-1, seq-1 from seq_1_to_10; +CREATE TABLE t4 as SELECT * from t1 where a < 5; --disable_result_log ANALYZE TABLE t1 PERSISTENT FOR ALL; ANALYZE TABLE t2 PERSISTENT FOR ALL; ANALYZE TABLE t3 PERSISTENT FOR ALL; +ANALYZE TABLE t4 PERSISTENT FOR ALL; --enable_result_log @@ -667,4 +669,62 @@ eval $query; set use_sort_nest=0; eval $query; -drop table t0,t1,t2,t3; +--echo # +--echo # Dependent subqueries cannot have a sort-nest +--echo # + +let $query= +SELECT * +FROM t1 +WHERE +t1.b = (select t2.b from t2,t3,t4 + where t2.a=t1.a and t3.b=t4.b + ORDER BY t2.c,t3.c limit 1); + +set use_sort_nest=1; +eval EXPLAIN $query; +eval $query; + +set use_sort_nest= 0; +eval $query; + +--echo # +--echo # Independent subqueries can have a sort-nest +--echo # + +let $query= +SELECT * +FROM t1 +WHERE +t1.b = (select t2.b from t2,t3,t4 + where t2.a=t3.a and t3.b=t4.b + ORDER BY t2.c,t3.c limit 1); + +set use_sort_nest=1; + +eval EXPLAIN $query; +eval $query; + +set use_sort_nest= 0; +eval $query; + +--echo # +--echo # Sort nest inside a derived table +--echo # + +let $query= +SELECT * +FROM (SELECT t1.a as t, t2.b as u, t1.b as v, t3.a as x + FROM t1,t2,t3 + WHERE t1.a=t2.a AND t2.b=t3.a + ORDER BY t2.b DESC, t1.b DESC + LIMIT 5)q; + +set use_sort_nest=1; +eval EXPLAIN $query; +eval $query; + +set use_sort_nest=0; +eval $query; + +drop table t0,t1,t2,t3,t4; diff --git a/sql/sql_sort_nest.cc b/sql/sql_sort_nest.cc index 857b1a172ac..e4d5a6ce492 100644 --- a/sql/sql_sort_nest.cc +++ b/sql/sql_sort_nest.cc @@ -1422,7 +1422,8 @@ bool JOIN::sort_nest_allowed() select_lex->window_specs.elements > 0 || select_lex->agg_func_used() || select_limit == HA_POS_ERROR || - thd->lex->sql_command != SQLCOM_SELECT); + thd->lex->sql_command != SQLCOM_SELECT || + select_lex->uncacheable & UNCACHEABLE_DEPENDENT); } |