bk commit into 5.0 tree (gkodinov:1.2456) BUG#27807

[kgeorge]

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; i<num_values; i++)
     {
       used_tables|=(value[i])->used_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