diff options
author | unknown <timour@askmonty.org> | 2011-08-27 00:40:29 +0300 |
---|---|---|
committer | unknown <timour@askmonty.org> | 2011-08-27 00:40:29 +0300 |
commit | 2df1914791030714196c3d829187891a97be54dc (patch) | |
tree | d24362560b461dbf256a1b427c543610548b28f1 | |
parent | ee28dec85885a1579758d9184ecb63ce810e738f (diff) | |
download | mariadb-git-2df1914791030714196c3d829187891a97be54dc.tar.gz |
Fix bug lp:827416
Analysis:
Constant table optimization of the outer query finds that
the right side of the equality is a constant that can
be used for an eq_ref access to fetch one row from t1,
and substitute t1 with a constant. Thus constant optimization
triggers evaluation of the subquery during the optimize
phase of the outer query.
The innermost subquery requires a plan with a temporary
table because with InnoDB tables the exact count of rows
is not known, and the empty tables cannot be optimzied
way. JOIN::exec for the innermost subquery substitutes
the subquery tables with a temporary table.
When EXPLAIN gets to print the tables in the innermost
subquery, EXPLAIN needs to print the name of each table
through the corresponding TABLE_LIST object. However,
the temporary table created during execution doesn't
have a corresponding TABLE_LIST, so we get a null
pointer exception.
Solution:
The solution is to forbid using expensive constant
expressions for eq_ref access for contant table
optimization. Notice that eq_ref with a subquery
providing the value is still possible during regular
execution.
-rw-r--r-- | mysql-test/r/group_min_max.result | 2 | ||||
-rw-r--r-- | mysql-test/r/subselect.result | 16 | ||||
-rw-r--r-- | mysql-test/r/subselect_innodb.result | 24 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_mat.result | 16 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_opts.result | 16 | ||||
-rw-r--r-- | mysql-test/r/subselect_no_semijoin.result | 16 | ||||
-rw-r--r-- | mysql-test/r/subselect_scache.result | 16 | ||||
-rw-r--r-- | mysql-test/suite/pbxt/r/subselect.result | 16 | ||||
-rw-r--r-- | mysql-test/suite/pbxt/t/subselect.test | 3 | ||||
-rw-r--r-- | mysql-test/t/subselect.test | 3 | ||||
-rw-r--r-- | mysql-test/t/subselect_innodb.test | 24 | ||||
-rw-r--r-- | sql/sql_select.cc | 7 |
12 files changed, 125 insertions, 34 deletions
diff --git a/mysql-test/r/group_min_max.result b/mysql-test/r/group_min_max.result index 6850d7c1993..dc078687fd3 100644 --- a/mysql-test/r/group_min_max.result +++ b/mysql-test/r/group_min_max.result @@ -2900,7 +2900,7 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); 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 x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a = (SELECT a FROM t1 WHERE a < 0); MIN( a ) diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result index f5d6fea60fc..4411d5ffda2 100644 --- a/mysql-test/r/subselect.result +++ b/mysql-test/r/subselect.result @@ -367,12 +367,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -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)) +Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (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 (`test`.`t8`.`pseudo` = (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) @@ -543,6 +543,12 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +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 where; Using index +2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = 1)))) +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -551,10 +557,10 @@ Warnings: Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`) EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); 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 +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; 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 ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/subselect_innodb.result b/mysql-test/r/subselect_innodb.result index bf97b9fc4eb..b311fa24eee 100644 --- a/mysql-test/r/subselect_innodb.result +++ b/mysql-test/r/subselect_innodb.result @@ -247,4 +247,28 @@ x NULL drop procedure p1; drop tables t1,t2,t3; +# +# LP BUG#827416: Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries +# +CREATE TABLE t3 ( b int) ENGINE=InnoDB; +CREATE TABLE t2 ( c int) ENGINE=InnoDB; +CREATE TABLE t1 ( a int NOT NULL , PRIMARY KEY (a)) ENGINE=InnoDB; +EXPLAIN SELECT * +FROM t1 +WHERE t1.a = ( +SELECT SUM( c ) +FROM t2 +WHERE (SELECT DISTINCT b FROM t3) > 0); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 const PRIMARY PRIMARY 4 const 1 Using where; Using index +2 SUBQUERY t2 ALL NULL NULL NULL NULL 1 +3 SUBQUERY t3 ALL NULL NULL NULL NULL 1 Using temporary +SELECT * +FROM t1 +WHERE t1.a = ( +SELECT SUM( c ) +FROM t2 +WHERE (SELECT DISTINCT b FROM t3) > 0); +a +DROP TABLE t1, t2, t3; set optimizer_switch=@subselect_innodb_tmp; diff --git a/mysql-test/r/subselect_no_mat.result b/mysql-test/r/subselect_no_mat.result index cecc74514c2..dd9e9dc3e0e 100644 --- a/mysql-test/r/subselect_no_mat.result +++ b/mysql-test/r/subselect_no_mat.result @@ -372,12 +372,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -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)) +Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (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 (`test`.`t8`.`pseudo` = (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) @@ -548,6 +548,12 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +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 where; Using index +2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = 1)))) +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -556,10 +562,10 @@ Warnings: Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`) EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); 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 +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; 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 ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) 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 e0604485efb..4f1b9f350f8 100644 --- a/mysql-test/r/subselect_no_opts.result +++ b/mysql-test/r/subselect_no_opts.result @@ -368,12 +368,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -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)) +Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (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 (`test`.`t8`.`pseudo` = (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) @@ -544,6 +544,12 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +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 where; Using index +2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = 1)))) +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -552,10 +558,10 @@ Warnings: Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`) EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); 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 +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; 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 ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) 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 17a4c16531d..d68bbacc7b9 100644 --- a/mysql-test/r/subselect_no_semijoin.result +++ b/mysql-test/r/subselect_no_semijoin.result @@ -368,12 +368,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -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)) +Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (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 (`test`.`t8`.`pseudo` = (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) @@ -544,6 +544,12 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +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 where; Using index +2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = 1)))) +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -552,10 +558,10 @@ Warnings: Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`) EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); 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 +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; 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 ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/r/subselect_scache.result b/mysql-test/r/subselect_scache.result index 9405482570b..1dff1ab8921 100644 --- a/mysql-test/r/subselect_scache.result +++ b/mysql-test/r/subselect_scache.result @@ -371,12 +371,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index condition 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -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)) +Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (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 (`test`.`t8`.`pseudo` = (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) @@ -547,6 +547,12 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +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 where; Using index +2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = 1)))) +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -555,10 +561,10 @@ Warnings: Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`) EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); 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 +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; 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 ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/suite/pbxt/r/subselect.result b/mysql-test/suite/pbxt/r/subselect.result index b8b63ed5850..6623251baa0 100644 --- a/mysql-test/suite/pbxt/r/subselect.result +++ b/mysql-test/suite/pbxt/r/subselect.result @@ -362,12 +362,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('joce1','test1'); INSERT INTO t8 (pseudo,email) VALUES ('2joce1','2test1'); EXPLAIN EXTENDED SELECT pseudo,(SELECT email FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce')) FROM t8 WHERE pseudo=(SELECT pseudo FROM t8 WHERE pseudo='joce'); id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index +1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where; Using index 4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 +2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using where 3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 100.00 Using index Warnings: -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)) +Note 1003 select `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (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 (`test`.`t8`.`pseudo` = (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) @@ -535,6 +535,12 @@ SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT numreponse (SELECT numeropost FROM t1 HAVING numreponse=1) INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +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 where; Using index +2 SUBQUERY t1 ref PRIMARY PRIMARY 3 const 2 100.00 Using index +Warnings: +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select 1 from `test`.`t1` where (`test`.`t1`.`numeropost` = 1)))) +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); ERROR 21000: Subquery returns more than 1 row EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; id select_type table type possible_keys key key_len ref rows filtered Extra @@ -543,10 +549,10 @@ Warnings: Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`) EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); 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 +1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 100.00 Using where; 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 ((3 = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numeropost` = 1) and (`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) from `test`.`t1` where multiple equal(1, `test`.`t1`.`numeropost`)))) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); diff --git a/mysql-test/suite/pbxt/t/subselect.test b/mysql-test/suite/pbxt/t/subselect.test index 3f4d6a9a870..3b7348ce844 100644 --- a/mysql-test/suite/pbxt/t/subselect.test +++ b/mysql-test/suite/pbxt/t/subselect.test @@ -278,8 +278,9 @@ SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); --- error 1242 EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +-- error 1242 +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); drop table t1; diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test index 6c460af6ae5..d460eb57667 100644 --- a/mysql-test/t/subselect.test +++ b/mysql-test/t/subselect.test @@ -292,8 +292,9 @@ SELECT (SELECT numeropost FROM t1 HAVING numreponse=a),numreponse FROM (SELECT * SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=a) FROM (SELECT * FROM t1) as a; SELECT numreponse, (SELECT numeropost FROM t1 HAVING numreponse=1) FROM (SELECT * FROM t1) as a; INSERT INTO t1 (numeropost,numreponse,pseudo) VALUES (1,1,'joce'),(1,2,'joce'),(1,3,'test'); --- error ER_SUBQUERY_NO_1_ROW EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); +-- error ER_SUBQUERY_NO_1_ROW +SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT 1 FROM t1 WHERE numeropost='1'); EXPLAIN EXTENDED SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'; EXPLAIN EXTENDED SELECT numreponse FROM t1 WHERE numeropost='1' AND numreponse=(SELECT MAX(numreponse) FROM t1 WHERE numeropost='1'); drop table t1; diff --git a/mysql-test/t/subselect_innodb.test b/mysql-test/t/subselect_innodb.test index da7524c1427..e108aecfd36 100644 --- a/mysql-test/t/subselect_innodb.test +++ b/mysql-test/t/subselect_innodb.test @@ -241,5 +241,27 @@ call p1(); drop procedure p1; drop tables t1,t2,t3; -set optimizer_switch=@subselect_innodb_tmp; +--echo # +--echo # LP BUG#827416: Crash in select_describe() on EXPLAIN with DISTINCT in nested subqueries +--echo # + +CREATE TABLE t3 ( b int) ENGINE=InnoDB; +CREATE TABLE t2 ( c int) ENGINE=InnoDB; +CREATE TABLE t1 ( a int NOT NULL , PRIMARY KEY (a)) ENGINE=InnoDB; + +EXPLAIN SELECT * +FROM t1 +WHERE t1.a = ( + SELECT SUM( c ) + FROM t2 + WHERE (SELECT DISTINCT b FROM t3) > 0); +SELECT * +FROM t1 +WHERE t1.a = ( + SELECT SUM( c ) + FROM t2 + WHERE (SELECT DISTINCT b FROM t3) > 0); +DROP TABLE t1, t2, t3; + +set optimizer_switch=@subselect_innodb_tmp; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 6d2c7400d3d..54c1215164e 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -2993,6 +2993,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, uint i,table_count,const_count,key; table_map found_const_table_map, all_table_map, found_ref, refs; key_map const_ref, eq_part; + bool has_expensive_keyparts; TABLE **table_vector; JOIN_TAB *stat,*stat_end,*s,**stat_ref; KEYUSE *keyuse,*start_keyuse; @@ -3314,12 +3315,17 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, refs=0; const_ref.clear_all(); eq_part.clear_all(); + has_expensive_keyparts= false; do { if (keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize) { if (!((~found_const_table_map) & keyuse->used_tables)) + { const_ref.set_bit(keyuse->keypart); + if (keyuse->val->is_expensive()) + has_expensive_keyparts= true; + } else refs|=keyuse->used_tables; eq_part.set_bit(keyuse->keypart); @@ -3341,6 +3347,7 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (table->key_info[key].flags & HA_NOSAME) { if (const_ref == eq_part && + !has_expensive_keyparts && !((outer_join & table->map) && (*s->on_expr_ref)->is_expensive())) { // Found everything for ref. |