diff options
| -rw-r--r-- | mysql-test/r/group_min_max.result | 2 | ||||
| -rw-r--r-- | mysql-test/r/subselect.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/subselect4.result | 87 | ||||
| -rw-r--r-- | mysql-test/r/subselect_mat.result | 8 | ||||
| -rw-r--r-- | mysql-test/r/subselect_mat_cost_bugs.result | 70 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_mat.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_opts.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/subselect_no_semijoin.result | 4 | ||||
| -rw-r--r-- | mysql-test/r/windows.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/pbxt/r/group_min_max.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/pbxt/r/subselect.result | 4 | ||||
| -rw-r--r-- | mysql-test/t/subselect4.test | 87 | ||||
| -rw-r--r-- | mysql-test/t/subselect_mat_cost_bugs.test | 83 | ||||
| -rw-r--r-- | sql/item.h | 2 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 22 | ||||
| -rw-r--r-- | sql/item_subselect.cc | 2 | ||||
| -rw-r--r-- | sql/item_subselect.h | 3 | ||||
| -rw-r--r-- | sql/opt_subselect.cc | 74 | ||||
| -rw-r--r-- | sql/sql_lex.cc | 2 | ||||
| -rw-r--r-- | sql/sql_list.h | 2 | ||||
| -rw-r--r-- | sql/sql_select.cc | 47 | ||||
| -rw-r--r-- | sql/sql_select.h | 24 |
22 files changed, 317 insertions, 222 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 20b0c054480..0b3df689089 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2412,7 +2412,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t1_outer1 ref a a 5 const 1 Using index 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer (flat, BNL join) 2 SUBQUERY t1 range NULL a 5 NULL 8 Using index for group-by EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index f289e46c42c..c19e8413255 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -370,7 +370,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index Warnings: -Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1)) SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -552,7 +552,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1')) +Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1') and ('3' = 3)) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/subselect4.result b/mysql-test/r/subselect4.result index 87903694230..a8102486f6b 100644 --- a/mysql-test/r/subselect4.result +++ b/mysql-test/r/subselect4.result @@ -1317,7 +1317,7 @@ EXPLAIN SELECT i FROM t1 WHERE (1) NOT IN (SELECT i FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 -2 DEPENDENT SUBQUERY t2 ref_or_null k k 5 const 2 Using where; Using index +2 DEPENDENT SUBQUERY t2 index_subquery k k 5 const 2 Using index DROP TABLE t2; DROP TABLE t1; # @@ -1655,52 +1655,6 @@ f1b f2b f3b set @@optimizer_switch=@save_optimizer_switch; drop table t0,t1,t2; # -# LP BUG#718593 Crash in substitute_for_best_equal_field -> eliminate_item_equal -> -# Item_field::find_item_equal -> Item_equal::contains -# -set @save_optimizer_switch=@@optimizer_switch; -SET @@optimizer_switch = 'semijoin=off'; -CREATE TABLE t1 ( f3 int(11), f10 varchar(1), f11 varchar(1)) ; -INSERT IGNORE INTO t1 VALUES (6,'f','f'),(2,'d','d'); -CREATE TABLE t2 ( f12 int(11), f13 int(11)) ; -insert into t2 values (1,2), (3,4); -EXPLAIN -SELECT * FROM t2 -WHERE ( f12 ) IN ( -SELECT alias2.f3 -FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 -WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 -); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY alias1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -SELECT * FROM t2 -WHERE ( f12 ) IN ( -SELECT alias2.f3 -FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 -WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 -); -f12 f13 -EXPLAIN -SELECT * FROM t2 -WHERE ( f12 ) IN ( -SELECT alias2.f3 -FROM t1 AS alias1, t1 AS alias2 -WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); -id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY alias1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) -SELECT * FROM t2 -WHERE ( f12 ) IN ( -SELECT alias2.f3 -FROM t1 AS alias1, t1 AS alias2 -WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); -f12 f13 -set @@optimizer_switch=@save_optimizer_switch; -drop table t1, t2; -# # LP BUG#715759 Wrong result with in_to_exists=on in maria-5.3-mwl89 # set @save_optimizer_switch=@@optimizer_switch; @@ -1743,3 +1697,42 @@ SELECT f2 FROM t1 WHERE ( f2 ) IN ( SELECT t3.f2 FROM t3 JOIN t2 ON t2.f1 = 1 ) ) NULL drop table t1, t2, t3; +# +# LP BUG#777597 Wrong result with multipart keys, in_to_exists=on, NOT IN in MWL#89 +# +CREATE TABLE t1 ( f4 int); +INSERT IGNORE INTO t1 VALUES (2),(2); +CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3) ); +INSERT IGNORE INTO t2 VALUES (6, 1), (6, 1); +CREATE TABLE t3 ( f10 int ); +INSERT IGNORE INTO t3 VALUES (1); +SET SESSION optimizer_switch='in_to_exists=on,materialization=off'; +EXPLAIN +SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 +2 DEPENDENT SUBQUERY t3 system NULL NULL NULL NULL 1 +2 DEPENDENT SUBQUERY t2 ref_or_null f10 f10 10 const,const 2 Using where; Using index +SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); +f4 +drop table t1,t2,t3; +# +# LP BUG#778413 Third crash in select_describe() in maria-5.3-mwl89 +# +CREATE TABLE t1 ( f11 int) ; +INSERT INTO t1 VALUES (1),(1); +CREATE TABLE t2 ( f1 int NOT NULL) ; +INSERT INTO t2 VALUES (20); +CREATE TABLE t3 (f3 int) ; +INSERT INTO t3 VALUES (2),(2); +EXPLAIN SELECT * FROM t2 +WHERE t2.f1 = ( +SELECT MAX( f3 ) FROM t3 +WHERE EXISTS ( +SELECT DISTINCT f11 +FROM t1)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 system NULL NULL NULL NULL 1 +2 SUBQUERY t3 ALL NULL NULL NULL NULL 2 +3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using temporary +drop table t1, t2, t3; diff --git a/mysql-test/r/subselect_mat.result b/mysql-test/r/subselect_mat.result index f475b251235..144519878c1 100644 --- a/mysql-test/r/subselect_mat.result +++ b/mysql-test/r/subselect_mat.result @@ -1395,7 +1395,7 @@ SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 -2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t2 WHERE (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); f2 EXPLAIN @@ -1403,7 +1403,7 @@ SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1); (SELECT f3a FROM t3) NOT IN (SELECT f1a FROM t1) NULL @@ -1412,7 +1412,7 @@ SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 3 SUBQUERY t1 ALL NULL NULL NULL NULL 2 -2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT * FROM t2 WHERE (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); f2 EXPLAIN @@ -1420,7 +1420,7 @@ SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used 3 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 2 Using where -2 SUBQUERY t3 system NULL NULL NULL NULL 0 const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table SELECT (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1); (SELECT f3a, f3b FROM t3) NOT IN (SELECT f1a, f1b FROM t1) NULL diff --git a/mysql-test/r/subselect_mat_cost_bugs.result b/mysql-test/r/subselect_mat_cost_bugs.result index cd0a6c716bb..18f2513a76d 100644 --- a/mysql-test/r/subselect_mat_cost_bugs.result +++ b/mysql-test/r/subselect_mat_cost_bugs.result @@ -229,6 +229,72 @@ FROM t1 STRAIGHT_JOIN t2 ON t2.f3 = t1.f3 WHERE t2.f3 = 'c'); f2 f3 drop table t1,t2,t3; +# +# LP BUG#718593 Crash in substitute_for_best_equal_field -> eliminate_item_equal -> +# Item_field::find_item_equal -> Item_equal::contains # -# LP BUG#718593 Crash in substitute_for_best_equal_field -> -# eliminate_item_equal -> Item_field::find_item_equal -> Item_equal::contains +set @save_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch = 'semijoin=off'; +CREATE TABLE t1 ( f3 int(11), f10 varchar(1), f11 varchar(1)) ; +INSERT IGNORE INTO t1 VALUES (6,'f','f'),(2,'d','d'); +CREATE TABLE t2 ( f12 int(11), f13 int(11)) ; +insert into t2 values (1,2), (3,4); +EXPLAIN +SELECT * FROM t2 +WHERE ( f12 ) IN ( +SELECT alias2.f3 +FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 +WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 +); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY alias1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t2 +WHERE ( f12 ) IN ( +SELECT alias2.f3 +FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 +WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 +); +f12 f13 +EXPLAIN +SELECT * FROM t2 +WHERE ( f12 ) IN ( +SELECT alias2.f3 +FROM t1 AS alias1, t1 AS alias2 +WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY alias1 ALL NULL NULL NULL NULL 2 Using where +2 SUBQUERY alias2 ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +SELECT * FROM t2 +WHERE ( f12 ) IN ( +SELECT alias2.f3 +FROM t1 AS alias1, t1 AS alias2 +WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); +f12 f13 +set @@optimizer_switch=@save_optimizer_switch; +drop table t1, t2; +# +# MWL#89: test introduced after Sergey Petrunia's review - test that +# keyparts wihtout index prefix are used with the IN-EXISTS strategy. +# +create table t1 (c1 int); +insert into t1 values (1), (2), (3); +create table t2 (kp1 int, kp2 int, c2 int, filler char(100)); +insert into t2 values (0,0,0,'filler'),(0,1,1,'filler'),(0,2,2,'filler'),(0,3,3,'filler'); +create index key1 on t2 (kp1, kp2); +create index key2 on t2 (kp1); +create index key3 on t2 (kp2); +set session optimizer_switch='default'; +analyze table t2; +Table Op Msg_type Msg_text +test.t2 analyze status OK +explain +select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +2 DEPENDENT SUBQUERY t2 index_subquery key1,key2,key3 key1 10 func,const 1 Using where +select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; +c1 +drop table t1, t2; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index 1aa45d467e6..2bc53a81fdb 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -374,7 +374,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index Warnings: -Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1)) SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -556,7 +556,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1')) +Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1') and ('3' = 3)) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/subselect_no_opts.result b/mysql-test/r/subselect_no_opts.result index d1aaebedcb5..5e0df3bf788 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -371,7 +371,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index Warnings: -Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1)) SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -553,7 +553,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1')) +Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1') and ('3' = 3)) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/subselect_no_semijoin.result b/mysql-test/r/subselect_no_semijoin.result index 1d25894c8ce..69b6afc9333 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -371,7 +371,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index Warnings: -Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1)) SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -553,7 +553,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1')) +Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1') and ('3' = 3)) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/windows.result b/mysql-test/r/windows.result index d0cdd858d4a..dc624a07f4f 100644 --- a/mysql-test/r/windows.result +++ b/mysql-test/r/windows.result @@ -16,7 +16,7 @@ CREATE TABLE t1 (a int, b int); INSERT INTO t1 VALUES (1,1); EXPLAIN SELECT * FROM t1 WHERE b = (SELECT max(2)); 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 +1 PRIMARY t1 system NULL NULL NULL NULL 1 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used DROP TABLE t1; CREATE DATABASE `TESTDB`; diff --git a/mysql-test/suite/pbxt/r/group_min_max.result b/mysql-test/suite/pbxt/r/group_min_max.result index 04f14a9f5e7..ab00cdeff59 100644 --- a/mysql-test/suite/pbxt/r/group_min_max.result +++ b/mysql-test/suite/pbxt/r/group_min_max.result @@ -2267,7 +2267,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JOIN t1 AS t1_outer2 ON t1_outer1.a = (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) AND t1_outer1.b = t1_outer2.b; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1_outer1 ref a a 5 const 1 Using where; Using index +1 PRIMARY t1_outer1 ref a a 5 const 1 Using index 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index; Using join buffer (flat, BNL join) 2 SUBQUERY t1 index NULL a 10 NULL 15 Using index EXPLAIN SELECT (SELECT (SELECT max(b) FROM t1 GROUP BY a HAVING a < 2) x diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index daa13af72bc..399635a2536 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -367,7 +367,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 100.00 Using index Warnings: -Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where 1) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where 1 +Note 1003 select 'joce' AS `pseudo`,(select 'test' from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1))) AS `(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'))` from `test`.`t8` where ('joce' = (select 'joce' from `test`.`t8` where 1)) SELECT pseudo FROM t8 WHERE pseudo=(SELECT pseudo,email FROM t8 WHERE pseudo='joce'); ERROR 21000: Operand should contain 1 column(s) @@ -546,7 +546,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1')) +Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = '1') and ('3' = 3)) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/t/subselect4.test b/mysql-test/t/subselect4.test index c57cdf27444..768f37c1b6c 100644 --- a/mysql-test/t/subselect4.test +++ b/mysql-test/t/subselect4.test @@ -1328,48 +1328,6 @@ set @@optimizer_switch=@save_optimizer_switch; drop table t0,t1,t2; ---echo # ---echo # LP BUG#718593 Crash in substitute_for_best_equal_field -> eliminate_item_equal -> ---echo # Item_field::find_item_equal -> Item_equal::contains ---echo # - -set @save_optimizer_switch=@@optimizer_switch; -SET @@optimizer_switch = 'semijoin=off'; - -CREATE TABLE t1 ( f3 int(11), f10 varchar(1), f11 varchar(1)) ; -INSERT IGNORE INTO t1 VALUES (6,'f','f'),(2,'d','d'); - -CREATE TABLE t2 ( f12 int(11), f13 int(11)) ; -insert into t2 values (1,2), (3,4); - -EXPLAIN -SELECT * FROM t2 -WHERE ( f12 ) IN ( - SELECT alias2.f3 - FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 - WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 -); -SELECT * FROM t2 -WHERE ( f12 ) IN ( - SELECT alias2.f3 - FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 - WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 -); - -EXPLAIN -SELECT * FROM t2 -WHERE ( f12 ) IN ( - SELECT alias2.f3 - FROM t1 AS alias1, t1 AS alias2 - WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); -SELECT * FROM t2 -WHERE ( f12 ) IN ( - SELECT alias2.f3 - FROM t1 AS alias1, t1 AS alias2 - WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); - -set @@optimizer_switch=@save_optimizer_switch; -drop table t1, t2; --echo # --echo # LP BUG#715759 Wrong result with in_to_exists=on in maria-5.3-mwl89 @@ -1412,3 +1370,48 @@ SELECT STRAIGHT_JOIN ( ); drop table t1, t2, t3; + + +--echo # +--echo # LP BUG#777597 Wrong result with multipart keys, in_to_exists=on, NOT IN in MWL#89 +--echo # + +CREATE TABLE t1 ( f4 int); +INSERT IGNORE INTO t1 VALUES (2),(2); + +CREATE TABLE t2 ( f3 int, f10 int, KEY (f10,f3) ); +INSERT IGNORE INTO t2 VALUES (6, 1), (6, 1); + +CREATE TABLE t3 ( f10 int ); +INSERT IGNORE INTO t3 VALUES (1); + +SET SESSION optimizer_switch='in_to_exists=on,materialization=off'; + +EXPLAIN +SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); +SELECT * FROM t1 WHERE ( 6 ) NOT IN ( SELECT t2.f3 FROM t2 JOIN t3 ON t3.f10 = t2.f10); + +drop table t1,t2,t3; + + +--echo # +--echo # LP BUG#778413 Third crash in select_describe() in maria-5.3-mwl89 +--echo # + +CREATE TABLE t1 ( f11 int) ; +INSERT INTO t1 VALUES (1),(1); + +CREATE TABLE t2 ( f1 int NOT NULL) ; +INSERT INTO t2 VALUES (20); + +CREATE TABLE t3 (f3 int) ; +INSERT INTO t3 VALUES (2),(2); + +EXPLAIN SELECT * FROM t2 +WHERE t2.f1 = ( + SELECT MAX( f3 ) FROM t3 + WHERE EXISTS ( + SELECT DISTINCT f11 + FROM t1)); + +drop table t1, t2, t3; diff --git a/mysql-test/t/subselect_mat_cost_bugs.test b/mysql-test/t/subselect_mat_cost_bugs.test index bc052ea04b1..0240c9203b3 100644 --- a/mysql-test/t/subselect_mat_cost_bugs.test +++ b/mysql-test/t/subselect_mat_cost_bugs.test @@ -258,54 +258,71 @@ WHERE ( f2 ) IN (SELECT t1.f1 drop table t1,t2,t3; +--echo # +--echo # LP BUG#718593 Crash in substitute_for_best_equal_field -> eliminate_item_equal -> +--echo # Item_field::find_item_equal -> Item_equal::contains --echo # ---echo # LP BUG#718593 Crash in substitute_for_best_equal_field -> ---echo # eliminate_item_equal -> Item_field::find_item_equal -> Item_equal::contains ---disable_parsing # not yet fixed +set @save_optimizer_switch=@@optimizer_switch; +SET @@optimizer_switch = 'semijoin=off'; CREATE TABLE t1 ( f3 int(11), f10 varchar(1), f11 varchar(1)) ; INSERT IGNORE INTO t1 VALUES (6,'f','f'),(2,'d','d'); CREATE TABLE t2 ( f12 int(11), f13 int(11)) ; - -set @save_optimizer_switch=@@optimizer_switch; -set @@optimizer_switch='materialization=off,in_to_exists=on,semijoin=off'; +insert into t2 values (1,2), (3,4); EXPLAIN SELECT * FROM t2 -WHERE (f12) IN ( - SELECT alias2.f3 - FROM t1 AS alias1, t1 AS alias2 - WHERE (alias2.f10 = alias1.f11) AND - (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); - +WHERE ( f12 ) IN ( + SELECT alias2.f3 + FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 + WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 +); SELECT * FROM t2 -WHERE (f12) IN ( - SELECT alias2.f3 - FROM t1 AS alias1, t1 AS alias2 - WHERE (alias2.f10 = alias1.f11) AND - (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); - -insert into t2 values (1,2), (3,4); +WHERE ( f12 ) IN ( + SELECT alias2.f3 + FROM t1 AS alias1 JOIN t1 AS alias2 ON alias2.f10 = alias1.f11 + WHERE alias1.f11 OR alias1.f3 = 50 AND alias1.f10 +); EXPLAIN SELECT * FROM t2 -WHERE (f12) IN ( - SELECT alias2.f3 - FROM t1 AS alias1, t1 AS alias2 - WHERE (alias2.f10 = alias1.f11) AND - (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); - +WHERE ( f12 ) IN ( + SELECT alias2.f3 + FROM t1 AS alias1, t1 AS alias2 + WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); SELECT * FROM t2 -WHERE (f12) IN ( - SELECT alias2.f3 - FROM t1 AS alias1, t1 AS alias2 - WHERE (alias2.f10 = alias1.f11) AND - (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); - -set session optimizer_switch=@save_optimizer_switch; +WHERE ( f12 ) IN ( + SELECT alias2.f3 + FROM t1 AS alias1, t1 AS alias2 + WHERE (alias2.f10 = alias1.f11) AND (alias1.f11 OR alias1.f3 = 50 AND alias1.f10)); +set @@optimizer_switch=@save_optimizer_switch; drop table t1, t2; ---enable_parsing + +--echo # +--echo # MWL#89: test introduced after Sergey Petrunia's review - test that +--echo # keyparts wihtout index prefix are used with the IN-EXISTS strategy. +--echo # + +create table t1 (c1 int); +insert into t1 values (1), (2), (3); + +create table t2 (kp1 int, kp2 int, c2 int, filler char(100)); +insert into t2 values (0,0,0,'filler'),(0,1,1,'filler'),(0,2,2,'filler'),(0,3,3,'filler'); + +create index key1 on t2 (kp1, kp2); +create index key2 on t2 (kp1); +create index key3 on t2 (kp2); + +set session optimizer_switch='default'; + +analyze table t2; + +explain +select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; +select c1 from t1 where c1 in (select kp1 from t2 where kp2 = 10 and c2 = 4) or c1 > 7; + +drop table t1, t2; diff --git a/sql/item.h b/sql/item.h index 9bb00c5db84..55c061690e4 100644 --- a/sql/item.h +++ b/sql/item.h @@ -536,7 +536,7 @@ public: SUBSELECT_ITEM, ROW_ITEM, CACHE_ITEM, TYPE_HOLDER, PARAM_ITEM, TRIGGER_FIELD_ITEM, DECIMAL_ITEM, XPATH_NODESET, XPATH_NODESET_CMP, - VIEW_FIXER_ITEM, EXPR_CACHE_ITEM, UNKNOWN_ITEM}; + VIEW_FIXER_ITEM, EXPR_CACHE_ITEM}; enum cond_result { COND_UNDEF,COND_OK,COND_TRUE,COND_FALSE }; diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index 9b12e5c844c..ab3c2422f30 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -421,14 +421,12 @@ static bool convert_constant_item(THD *thd, Item_field *field_item, thd->count_cuted_fields= CHECK_FIELD_IGNORE; /* - Store the value of the field/constant if it references an outer field - because the call to save_in_field below overrides that value. - Don't save field value if no data has been read yet. - Outer constant values are always saved. + Store the value of the field/constant because the call to save_in_field + below overrides that value. Don't save field value if no data has been + read yet. */ - bool save_field_value= (field_item->depended_from && - (field_item->const_item() || - !(field->table->status & STATUS_NO_RECORD))); + bool save_field_value= (field_item->const_item() || + !(field->table->status & STATUS_NO_RECORD)); if (save_field_value) orig_field_val= field->val_int(); if (!(*item)->is_null() && !(*item)->save_in_field(field, 1)) @@ -1743,7 +1741,10 @@ bool Item_in_optimizer::fix_left(THD *thd, Item **ref) with_sum_func= args[0]->with_sum_func; with_field= args[0]->with_field; if ((const_item_cache= args[0]->const_item())) + { cache->store(args[0]); + cache->cache_value(); + } return 0; } @@ -2095,13 +2096,14 @@ Item *Item_in_optimizer::transform(Item_transformer transformer, uchar *argument bool Item_in_optimizer::is_expensive_processor(uchar *arg) { - return args[1]->is_expensive_processor(arg); + return args[0]->is_expensive_processor(arg) || + args[1]->is_expensive_processor(arg); } bool Item_in_optimizer::is_expensive() { - return args[1]->is_expensive(); + return args[0]->is_expensive() || args[1]->is_expensive(); } @@ -5752,7 +5754,7 @@ void Item_equal::update_const() Item *item; while ((item= it++)) { - if (item->const_item()) + if (item->const_item() && !item->is_expensive()) { if (item == equal_items.head()) with_const= TRUE; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index c217d84ade3..a25b7dcbec9 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -1765,7 +1765,7 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN * join, *having_item= new_having; } else - DBUG_ASSERT(FALSE); + DBUG_ASSERT(false); } } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 2c357b0da20..1b1be973cf9 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -464,7 +464,7 @@ public: Item_in_subselect() :Item_exists_subselect(), left_expr_cache(0), first_execution(TRUE), optimizer(0), abort_on_null(0), - pushed_cond_guards(NULL), func(NULL), in_strategy(0), + pushed_cond_guards(NULL), func(NULL), in_strategy(SUBS_NOT_TRANSFORMED), upper_item(0) {} void cleanup(); @@ -619,7 +619,6 @@ protected: class subselect_single_select_engine: public subselect_engine { bool prepared; /* simple subselect is prepared */ - bool optimized; /* simple subselect is optimized */ bool executed; /* simple subselect is executed */ st_select_lex *select_lex; /* corresponding select_lex */ JOIN * join; /* corresponding JOIN structure */ diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index dfb7e8abfa1..c6045cdfd0e 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -205,18 +205,19 @@ int check_and_do_in_subquery_rewrites(JOIN *join) !optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION)) my_error(ER_ILLEGAL_SUBQUERY_OPTIMIZER_SWITCHES, MYF(0)); + /* + If the subquery predicate is IN/=ANY, analyse and set all possible + subquery execution strategies based on optimizer switches and syntactic + properties. + */ if (in_subs) { - /* Subquery predicate is an IN/=ANY predicate. */ - if (optimizer_flag(thd, OPTIMIZER_SWITCH_IN_TO_EXISTS)) - in_subs->in_strategy|= SUBS_IN_TO_EXISTS; - if (optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION)) - in_subs->in_strategy|= SUBS_MATERIALIZATION; - /* Check if the subquery predicate can be executed via materialization. The required conditions are: - 1. Subquery is a single SELECT (not a UNION) + 0. The materialization optimizer switch was set. + 1. Subquery is a single SELECT (not a UNION). + TODO: this is a limitation that can be fixed 2. Subquery is not a table-less query. In this case there is no point in materializing. 2A The upper query is not a table-less SELECT ... FROM DUAL. We @@ -230,7 +231,7 @@ int check_and_do_in_subquery_rewrites(JOIN *join) non-top-level queries because it cannot handle NULLs correctly. 4. Subquery is non-correlated TODO: - This is an overly restrictive condition. It can be extended to: + This condition is too restrictive (limitation). It can be extended to: (Subquery is non-correlated || Subquery is correlated to any query outer to IN predicate || (Subquery is correlated to the immediate outer query && @@ -240,30 +241,30 @@ int check_and_do_in_subquery_rewrites(JOIN *join) (*) The subquery must be part of a SELECT statement. The current condition also excludes multi-table update statements. */ - if (!(in_subs->in_strategy & SUBS_MATERIALIZATION && - !select_lex->is_part_of_union() && // 1 - parent_unit->first_select()->leaf_tables && // 2 - thd->lex->sql_command == SQLCOM_SELECT && // * - select_lex->outer_select()->leaf_tables && // 2A - subquery_types_allow_materialization(in_subs) && - // psergey-todo: duplicated_subselect_card_check: where it's done? - (in_subs->is_top_level_item() || //3 - optimizer_flag(thd, - OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE) || //3 - optimizer_flag(thd, - OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN)) && //3 - !in_subs->is_correlated)) //4 + if (optimizer_flag(thd, OPTIMIZER_SWITCH_MATERIALIZATION) && // 0 + !select_lex->is_part_of_union() && // 1 + parent_unit->first_select()->leaf_tables && // 2 + thd->lex->sql_command == SQLCOM_SELECT && // * + select_lex->outer_select()->leaf_tables && // 2A + subquery_types_allow_materialization(in_subs) && + (in_subs->is_top_level_item() || //3 + optimizer_flag(thd, + OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE) || //3 + optimizer_flag(thd, + OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN)) && //3 + !in_subs->is_correlated) //4 { - /* Materialization is not possible based on syntactic properties. */ - in_subs->in_strategy&= ~SUBS_MATERIALIZATION; + in_subs->in_strategy|= SUBS_MATERIALIZATION; } - if (!in_subs->in_strategy) + /* + IN-TO-EXISTS is the only universal strategy. Choose it if the user + allowed it via an optimizer switch, or if materialization is not + possible. + */ + if (optimizer_flag(thd, OPTIMIZER_SWITCH_IN_TO_EXISTS) || + !in_subs->in_strategy) { - /* - If neither materialization is possible, nor the user chose - IN-TO-EXISTS, choose IN-TO-EXISTS as the only universal strategy. - */ in_subs->in_strategy|= SUBS_IN_TO_EXISTS; } } @@ -3640,7 +3641,7 @@ static void remove_subq_pushed_predicates(JOIN *join, Item **where) /** - Optimize all subqueries of a query that have were flattened into a semijoin. + Optimize all subqueries of a query that were not flattened into a semijoin. @details Optimize all immediate children subqueries of a query. @@ -3699,14 +3700,13 @@ bool JOIN::optimize_unflattened_subqueries() bool JOIN::choose_subquery_plan(table_map join_tables) { - Query_plan_state save_qep; /* The original QEP of the subquery. */ + Join_plan_state save_qep; /* The original QEP of the subquery. */ enum_reopt_result reopt_result= REOPT_NONE; Item_in_subselect *in_subs; - if (select_lex->master_unit()->item && - select_lex->master_unit()->item->is_in_predicate()) + if (is_in_subquery()) { - in_subs= (Item_in_subselect*) select_lex->master_unit()->item; + in_subs= (Item_in_subselect*) unit->item; if (in_subs->create_in_to_exists_cond(this)) return true; } @@ -3943,11 +3943,10 @@ bool JOIN::choose_subquery_plan(table_map join_tables) bool JOIN::choose_tableless_subquery_plan() { DBUG_ASSERT(!tables_list || !tables); - if (select_lex->master_unit()->item) + if (unit->item) { - DBUG_ASSERT(select_lex->master_unit()->item->type() == - Item::SUBSELECT_ITEM); - Item_subselect *subs_predicate= select_lex->master_unit()->item; + DBUG_ASSERT(unit->item->type() == Item::SUBSELECT_ITEM); + Item_subselect *subs_predicate= unit->item; /* If the optimizer determined that his query has an empty result, @@ -3990,4 +3989,3 @@ bool JOIN::choose_tableless_subquery_plan() } return FALSE; } - diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 2368720f84b..b4da09c8108 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -3126,7 +3126,7 @@ bool st_select_lex::optimize_unflattened_subqueries() un->set_limit(un->global_parameters); un->thd->lex->current_select= sl; save_options= inner_join->select_options; - if (un->outer_select()->options & SELECT_DESCRIBE) + if (options & SELECT_DESCRIBE) { /* Optimize the subquery in the context of EXPLAIN. */ sl->set_explain_type(); diff --git a/sql/sql_list.h b/sql/sql_list.h index e90f16aeb99..7d9f3d26ec8 100644 --- a/sql/sql_list.h +++ b/sql/sql_list.h @@ -260,7 +260,7 @@ public: list_node *node= first; list_node *list_first= list->first; elements=0; - while (node->info && node != list_first) + while (node != &end_of_list && node != list_first) { prev= &node->next; node= node->next; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 66be0a2e4c2..bd3bc82234e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -57,7 +57,8 @@ static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse, uint tables, COND *conds, table_map table_map, SELECT_LEX *select_lex, st_sargable_param **sargables); -static bool sort_and_filter_keyuse(DYNAMIC_ARRAY *keyuse); +static bool sort_and_filter_keyuse(DYNAMIC_ARRAY *keyuse, + bool skip_unprefixed_keyparts); static int sort_keyuse(KEYUSE *a,KEYUSE *b); static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse, table_map used_tables); @@ -914,7 +915,6 @@ JOIN::optimize() "Impossible HAVING" : "Impossible WHERE"; tables= 0; error= 0; - choose_tableless_subquery_plan(); goto setup_subq_exit; } } @@ -959,13 +959,13 @@ JOIN::optimize() */ if ((res=opt_sum_query(thd, select_lex->leaf_tables, all_fields, conds))) { - if (res == HA_ERR_KEY_NOT_FOUND || res < 0) + DBUG_ASSERT(res >= 0); + if (res == HA_ERR_KEY_NOT_FOUND) { DBUG_PRINT("info",("No matching min/max row")); zero_result_cause= "No matching min/max row"; tables= 0; error=0; - choose_tableless_subquery_plan(); goto setup_subq_exit; } if (res > 1) @@ -1006,7 +1006,6 @@ JOIN::optimize() { DBUG_PRINT("info",("No tables")); error= 0; - choose_tableless_subquery_plan(); goto setup_subq_exit; } error= -1; // Error is sent to client @@ -1507,6 +1506,9 @@ JOIN::optimize() DBUG_RETURN(0); setup_subq_exit: + /* Choose an execution strategy for this JOIN. */ + if (!tables_list || !tables) + choose_tableless_subquery_plan(); /* Even with zero matching rows, subqueries in the HAVING clause may need to be evaluated if there are aggregate functions in the query. @@ -3036,7 +3038,16 @@ make_join_statistics(JOIN *join, TABLE_LIST *tables_arg, COND *conds, if (update_ref_and_keys(join->thd, keyuse_array, stat, join->tables, conds, ~outer_join, join->select_lex, &sargables)) goto error; - if (keyuse_array->elements && sort_and_filter_keyuse(keyuse_array)) + /* + Keyparts without prefixes may be useful if this JOIN is a subquery, and + if the subquery may be executed via the IN-EXISTS strategy. + */ + bool skip_unprefixed_keyparts= + !(join->is_in_subquery() && + ((Item_in_subselect*)join->unit->item)->in_strategy & SUBS_IN_TO_EXISTS); + + if (keyuse_array->elements && + sort_and_filter_keyuse(keyuse_array, skip_unprefixed_keyparts)) goto error; DBUG_EXECUTE("opt", print_keyuse_array(keyuse_array);); } @@ -4505,7 +4516,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab, Special treatment for ft-keys. */ -static bool sort_and_filter_keyuse(DYNAMIC_ARRAY *keyuse) +static bool sort_and_filter_keyuse(DYNAMIC_ARRAY *keyuse, + bool skip_unprefixed_keyparts) { KEYUSE key_end, *prev, *save_pos, *use; uint found_eq_constant, i; @@ -4532,12 +4544,12 @@ static bool sort_and_filter_keyuse(DYNAMIC_ARRAY *keyuse) { if (use->key == prev->key && use->table == prev->table) { - if (prev->keypart+1 < use->keypart || + if ((prev->keypart+1 < use->keypart && skip_unprefixed_keyparts) || (prev->keypart == use->keypart && found_eq_constant)) continue; /* remove */ } - else if (use->keypart != 0) // First found must be 0 - continue; + else if (use->keypart != 0 && skip_unprefixed_keyparts) + continue; /* remove - first found must be 0 */ } prev= use; @@ -9511,7 +9523,7 @@ static bool check_simple_equality(Item *left_item, Item *right_item, Item *orig_field_item= 0; if (left_item->type() == Item::FIELD_ITEM && !((Item_field*)left_item)->depended_from && - right_item->const_item()) + right_item->const_item() && !right_item->is_expensive()) { orig_field_item= left_item; field_item= (Item_field *) left_item; @@ -9519,7 +9531,7 @@ static bool check_simple_equality(Item *left_item, Item *right_item, } else if (right_item->type() == Item::FIELD_ITEM && !((Item_field*)right_item)->depended_from && - left_item->const_item()) + left_item->const_item() && !left_item->is_expensive()) { orig_field_item= right_item; field_item= (Item_field *) right_item; @@ -16940,8 +16952,7 @@ create_sort_index(THD *thd, JOIN *join, ORDER *order, select= tab->select; /* Currently ORDER BY ... LIMIT is not supported in subqueries. */ - DBUG_ASSERT(join->group_list || - !(join->unit->item && join->unit->item->is_in_predicate())); + DBUG_ASSERT(join->group_list || !join->is_in_subquery()); /* When there is SQL_BIG_RESULT do not sort using index for GROUP BY, @@ -20531,7 +20542,7 @@ void JOIN::set_allowed_join_cache_types() @param save_to The object into which the current query plan state is saved */ -void JOIN::save_query_plan(Query_plan_state *save_to) +void JOIN::save_query_plan(Join_plan_state *save_to) { if (keyuse.elements) { @@ -20561,7 +20572,7 @@ void JOIN::save_query_plan(Query_plan_state *save_to) @param The object from which the current query plan state is restored. */ -void JOIN::restore_query_plan(Query_plan_state *restore_from) +void JOIN::restore_query_plan(Join_plan_state *restore_from) { if (restore_from->keyuse.elements) { @@ -20610,7 +20621,7 @@ void JOIN::restore_query_plan(Query_plan_state *restore_from) JOIN::enum_reopt_result JOIN::reoptimize(Item *added_where, table_map join_tables, - Query_plan_state *save_to) + Join_plan_state *save_to) { DYNAMIC_ARRAY added_keyuse; SARGABLE_PARAM *sargables= 0; /* Used only as a dummy parameter. */ @@ -20661,7 +20672,7 @@ JOIN::reoptimize(Item *added_where, table_map join_tables, /* added_keyuse contents is copied, and it is no longer needed. */ delete_dynamic(&added_keyuse); - if (sort_and_filter_keyuse(&keyuse)) + if (sort_and_filter_keyuse(&keyuse, true)) return REOPT_ERROR; optimize_keyuse(this, &keyuse); diff --git a/sql/sql_select.h b/sql/sql_select.h index f6a68aca764..f67b79fff47 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -611,9 +611,10 @@ protected: /** The subset of the state of a JOIN that represents an optimized query - execution plan. Allows saving/restoring different plans for the same query. + execution plan. Allows saving/restoring different JOIN plans for the same + query. */ - class Query_plan_state { + class Join_plan_state { public: DYNAMIC_ARRAY keyuse; /* Copy of the JOIN::keyuse array. */ POSITION best_positions[MAX_TABLES+1]; /* Copy of JOIN::best_positions */ @@ -622,13 +623,13 @@ protected: /* Copies of JOIN_TAB::checked_keys for each JOIN_TAB. */ key_map join_tab_checked_keys[MAX_TABLES]; public: - Query_plan_state() + Join_plan_state() { keyuse.elements= 0; keyuse.buffer= NULL; } - Query_plan_state(JOIN *join); - ~Query_plan_state() + Join_plan_state(JOIN *join); + ~Join_plan_state() { delete_dynamic(&keyuse); } @@ -644,9 +645,9 @@ protected: /* Support for plan reoptimization with rewritten conditions. */ enum_reopt_result reoptimize(Item *added_where, table_map join_tables, - Query_plan_state *save_to); - void save_query_plan(Query_plan_state *save_to); - void restore_query_plan(Query_plan_state *restore_from); + Join_plan_state *save_to); + void save_query_plan(Join_plan_state *save_to); + void restore_query_plan(Join_plan_state *restore_from); /* Choose a subquery plan for a table-less subquery. */ bool choose_tableless_subquery_plan(); @@ -748,7 +749,7 @@ public: */ double best_read; /* - Estimated result rows (fanout) of the whole query. If this is a subquery + Estimated result rows (fanout) of the join operation. If this is a subquery that is reexecuted multiple times, this value includes the estiamted # of reexecutions. This value is equal to the multiplication of all join->positions[i].records_read of a JOIN. @@ -1082,6 +1083,11 @@ public: double *read_time_arg, double *record_count_arg); /* defined in opt_subselect.cc */ bool transform_max_min_subquery(); + /* True if this JOIN is a subquery under an IN predicate. */ + bool is_in_subquery() + { + return (unit->item && unit->item->is_in_predicate()); + } private: /** |