[Commits] Rev 3167: Fix bug lp:827416 in file:///home/tsk/mprog/src/5.3/

timour at askmonty.org timour at askmonty.org
Sat Aug 27 00:09:31 EEST 2011


At file:///home/tsk/mprog/src/5.3/

------------------------------------------------------------
revno: 3167
revision-id: timour at askmonty.org-20110826210922-cs3wu2orve8fk0vb
parent: timour at askmonty.org-20110823125147-65w156utjsku0dj7
fixes bug(s): https://launchpad.net/bugs/827416
committer: timour at askmonty.org
branch nick: 5.3
timestamp: Sat 2011-08-27 00:09:22 +0300
message:
  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.
-------------- next part --------------
=== modified file 'mysql-test/r/group_min_max.result'
--- a/mysql-test/r/group_min_max.result	2011-08-05 18:01:49 +0000
+++ b/mysql-test/r/group_min_max.result	2011-08-26 21:09:22 +0000
@@ -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 )

=== modified file 'mysql-test/r/subselect_innodb.result'
--- a/mysql-test/r/subselect_innodb.result	2011-07-08 14:46:47 +0000
+++ b/mysql-test/r/subselect_innodb.result	2011-08-26 21:09:22 +0000
@@ -247,4 +247,28 @@ NULL
 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;

=== modified file 'mysql-test/r/subselect_no_mat.result'
--- a/mysql-test/r/subselect_no_mat.result	2011-08-20 04:02:05 +0000
+++ b/mysql-test/r/subselect_no_mat.result	2011-08-26 21:09:22 +0000
@@ -372,12 +372,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 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 FR
 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 @@ id	select_type	table	type	possible_keys
 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);

=== modified file 'mysql-test/r/subselect_no_opts.result'
--- a/mysql-test/r/subselect_no_opts.result	2011-08-20 04:02:05 +0000
+++ b/mysql-test/r/subselect_no_opts.result	2011-08-26 21:09:22 +0000
@@ -368,12 +368,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 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 FR
 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 @@ id	select_type	table	type	possible_keys
 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);

=== modified file 'mysql-test/r/subselect_no_semijoin.result'
--- a/mysql-test/r/subselect_no_semijoin.result	2011-08-20 04:02:05 +0000
+++ b/mysql-test/r/subselect_no_semijoin.result	2011-08-26 21:09:22 +0000
@@ -368,12 +368,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 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 FR
 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 @@ id	select_type	table	type	possible_keys
 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);

=== modified file 'mysql-test/r/subselect_scache.result'
--- a/mysql-test/r/subselect_scache.result	2011-08-20 04:02:05 +0000
+++ b/mysql-test/r/subselect_scache.result	2011-08-26 21:09:22 +0000
@@ -371,12 +371,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 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 FR
 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 @@ id	select_type	table	type	possible_keys
 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);

=== modified file 'mysql-test/suite/pbxt/r/subselect.result'
--- a/mysql-test/suite/pbxt/r/subselect.result	2011-08-09 07:28:57 +0000
+++ b/mysql-test/suite/pbxt/r/subselect.result	2011-08-26 21:09:22 +0000
@@ -362,12 +362,12 @@ INSERT INTO t8 (pseudo,email) VALUES ('j
 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 FR
 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 @@ id	select_type	table	type	possible_keys
 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);

=== modified file 'mysql-test/suite/pbxt/t/subselect.test'
--- a/mysql-test/suite/pbxt/t/subselect.test	2011-07-23 04:39:55 +0000
+++ b/mysql-test/suite/pbxt/t/subselect.test	2011-08-26 21:09:22 +0000
@@ -278,8 +278,9 @@ SELECT (SELECT numeropost FROM t1 HAVING
 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;

=== modified file 'mysql-test/t/subselect.test'
--- a/mysql-test/t/subselect.test	2011-08-20 04:02:05 +0000
+++ b/mysql-test/t/subselect.test	2011-08-26 21:09:22 +0000
@@ -292,8 +292,9 @@ SELECT (SELECT numeropost FROM t1 HAVING
 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;

=== modified file 'mysql-test/t/subselect_innodb.test'
--- a/mysql-test/t/subselect_innodb.test	2011-07-08 14:46:47 +0000
+++ b/mysql-test/t/subselect_innodb.test	2011-08-26 21:09:22 +0000
@@ -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;

=== modified file 'sql/sql_select.cc'
--- a/sql/sql_select.cc	2011-08-22 11:38:32 +0000
+++ b/sql/sql_select.cc	2011-08-26 21:09:22 +0000
@@ -2993,6 +2993,7 @@ make_join_statistics(JOIN *join, List<TA
   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<TA
           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<TA
             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.



More information about the commits mailing list