From: Date: April 17 2007 4:50pm Subject: bk commit into 5.0 tree (gkodinov:1.2456) BUG#27807 List-Archive: http://lists.mysql.com/commits/24675 X-Bug: 27807 Message-Id: <200704171450.l3HEoR2o032069@magare.gmz> Below is the list of changes that have just been committed into a local 5.0 repository of kgeorge. When kgeorge does a push these changes will be propagated to the main repository and, within 24 hours after the push, to the public repository. For information on how to access the public repository see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html ChangeSet@stripped, 2007-04-17 17:50:24+03:00, gkodinov@stripped +5 -0 Bug #27807: When we execute EXPLAIN no query or subquery should actually be executed. Thus we must not consider as sargable equality predicates the predicates that contain subqueries in their right hand operator while doing EXPLAIN. Note that these predicates will be in effect when actually executing the statement. We don't have similar problem with either stored procedure or prepare because none sargable predicate selection (as part of JOIN::optimize) is not done during preparation phase. mysql-test/r/group_min_max.result@stripped, 2007-04-17 17:50:22+03:00, gkodinov@stripped +1 -1 Bug #27807: don't evaluate scalar subqueries during EXPLAIN. mysql-test/r/subselect.result@stripped, 2007-04-17 17:50:22+03:00, gkodinov@stripped +10 -10 Bug #27807: don't evaluate scalar subqueries during EXPLAIN. mysql-test/r/subselect3.result@stripped, 2007-04-17 17:50:22+03:00, gkodinov@stripped +10 -0 Bug #27807: test case mysql-test/t/subselect3.test@stripped, 2007-04-17 17:50:22+03:00, gkodinov@stripped +14 -0 Bug #27807: test case sql/sql_select.cc@stripped, 2007-04-17 17:50:23+03:00, gkodinov@stripped +10 -1 Bug #27807: don't evaluate scalar subqueries during EXPLAIN. # This is a BitKeeper patch. What follows are the unified diffs for the # set of deltas contained in the patch. The rest of the patch, the part # that BitKeeper cares about, is below these diffs. # User: gkodinov # Host: magare.gmz # Root: /home/kgeorge/mysql/work/B27807-5.0-opt --- 1.509/sql/sql_select.cc 2007-04-15 07:34:13 +03:00 +++ 1.510/sql/sql_select.cc 2007-04-17 17:50:23 +03:00 @@ -2898,7 +2898,16 @@ add_key_field(KEY_FIELD **key_fields,uin for (uint i=0; iused_tables(); - if (!((value[i])->used_tables() & (field->table->map | RAND_TABLE_BIT))) + if (!((value[i])->used_tables() & (field->table->map | RAND_TABLE_BIT)) && + /* + Don't consider predicates that contain independent scalar subqueries + in the right hand argument when calculating EXPLAIN, + because these will be evaluated by JOIN::optimize() and + we must not evaluate subqueries in EXPLAIN. + */ + !(field->table->in_use->lex->select_lex.join && + (field->table->in_use->lex->select_lex.join->select_options & + SELECT_DESCRIBE) && value[i]->with_subselect)) optimizable=1; } if (!optimizable) --- 1.184/mysql-test/r/subselect.result 2007-04-15 08:22:28 +03:00 +++ 1.185/mysql-test/r/subselect.result 2007-04-17 17:50:22 +03:00 @@ -363,12 +363,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 Extra -1 PRIMARY t8 const PRIMARY PRIMARY 37 const 1 Using index -4 SUBQUERY t8 const PRIMARY PRIMARY 37 1 Using index -2 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 -3 SUBQUERY t8 const PRIMARY PRIMARY 37 1 Using index +1 PRIMARY t8 index NULL PRIMARY 37 NULL 3 Using where; Using index +4 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 Using index +2 SUBQUERY t8 ALL NULL NULL NULL NULL 3 Using where +3 SUBQUERY t8 const PRIMARY PRIMARY 37 const 1 Using index Warnings: -Note 1003 select 'joce' AS `pseudo`,(select 'test' AS `email` 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 `test`.`t8`.`pseudo` AS `pseudo`,(select `test`.`t8`.`email` AS `email` from `test`.`t8` where (`test`.`t8`.`pseudo` = (select 'joce' AS `pseudo` 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' AS `pseudo` 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,10 +544,10 @@ Warnings: Note 1003 select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = _latin1'1') 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 Extra -1 PRIMARY t1 const PRIMARY,numreponse PRIMARY 7 const,const 1 Using index +1 PRIMARY t1 ref PRIMARY PRIMARY 3 const 1 Using where; Using index 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: -Note 1003 select '3' AS `numreponse` from `test`.`t1` where (('1' = _latin1'1')) +Note 1003 select `test`.`t1`.`numreponse` AS `numreponse` from `test`.`t1` where ((`test`.`t1`.`numreponse` = (select max(`test`.`t1`.`numreponse`) AS `MAX(numreponse)` from `test`.`t1` where (`test`.`t1`.`numeropost` = _latin1'1'))) and (`test`.`t1`.`numeropost` = _latin1'1')) drop table t1; CREATE TABLE t1 (a int(1)); INSERT INTO t1 VALUES (1); @@ -1232,7 +1232,7 @@ create table t1 (id int not null auto_in insert into t1 (salary) values (100),(1000),(10000),(10),(500),(5000),(50000); explain extended SELECT id FROM t1 where salary = (SELECT MAX(salary) FROM t1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ref salary salary 5 const 1 Using where +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 Using where 2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Select tables optimized away Warnings: Note 1003 select `test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`salary` = (select max(`test`.`t1`.`salary`) AS `MAX(salary)` from `test`.`t1`)) @@ -2945,7 +2945,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c ORDER BY t2.c DESC, t2.b DESC LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +1 PRIMARY r ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' @@ -2957,7 +2957,7 @@ ON r.a = (SELECT t2.a FROM t2 WHERE t2.c ORDER BY t2.c, t2.b LIMIT 1) WHERE t1.a = 10; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system PRIMARY NULL NULL NULL 1 -1 PRIMARY r const PRIMARY PRIMARY 4 const 1 +1 PRIMARY r ALL NULL NULL NULL NULL 3 2 DEPENDENT SUBQUERY t2 range b b 40 NULL 2 Using where SELECT sql_no_cache t1.a, r.a, r.b FROM t1 LEFT JOIN t2 r ON r.a = (SELECT t2.a FROM t2 WHERE t2.c = t1.a AND t2.b <= '359899' --- 1.8/mysql-test/r/subselect3.result 2007-04-15 07:34:13 +03:00 +++ 1.9/mysql-test/r/subselect3.result 2007-04-17 17:50:22 +03:00 @@ -692,3 +692,13 @@ a MAX(b) test 2 3 h 3 4 i DROP TABLE t1, t2; +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1,1), (2,2); +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES (1,1),(2,2); +EXPLAIN +SELECT b FROM t1 WHERE a = (SELECT MAX(a) FROM t2 WHERE a = t1.b GROUP BY b); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where +2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort +DROP TABLE t1,t2; --- 1.8/mysql-test/t/subselect3.test 2007-04-15 07:41:34 +03:00 +++ 1.9/mysql-test/t/subselect3.test 2007-04-17 17:50:22 +03:00 @@ -528,3 +528,17 @@ SELECT a, MAX(b), DROP TABLE t1, t2; + +# +# Bug #27807: Server crash when executing subquery with EXPLAIN +# +CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); +INSERT INTO t1 VALUES (1,1), (2,2); + +CREATE TABLE t2 (a int, b int); +INSERT INTO t2 VALUES (1,1),(2,2); + +EXPLAIN +SELECT b FROM t1 WHERE a = (SELECT MAX(a) FROM t2 WHERE a = t1.b GROUP BY b); + +DROP TABLE t1,t2; --- 1.28/mysql-test/r/group_min_max.result 2006-11-28 18:06:40 +02:00 +++ 1.29/mysql-test/r/group_min_max.result 2007-04-17 17:50:22 +03:00 @@ -2246,7 +2246,7 @@ EXPLAIN SELECT 1 FROM t1 AS t1_outer1 JO 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 index NULL a 10 NULL 15 Using where; Using index 1 PRIMARY t1_outer2 index NULL a 10 NULL 15 Using where; Using index 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