bk commit into 5.0 tree (gkodinov:1.2561) BUG#32036

[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-11-06 12:42:20+02:00, gkodinov@stripped +4 -0
  Bug #32036: EXISTS within a WHERE clause with a UNION 
    crashes MySQL 5.122
  When the parser processes extra parenthesis in sub-queries, 
  e.g. SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2))
  it makes an additional derived table :
  SELECT 2 FROM t1 WHERE EXISTS (SELECT * FROM (SELECT 1 FROM t2) x);
  instead of just removing the extra set of parenthesis.
  This is done because the parser generates code. And it needs to
  know in advance that it needs to make the derived table transformation
  in the above case.
  But it may know if it really needs this transformation only at the end
  of parsing : when it parses the UNION clauses.
  This was causing code (resolving the outer references in sub-queries) 
  that doesn't expect to have these derived tables without a cause to 
  crash.
  Fixed by extending the name resolution code to account for such 
  derived tables.
  Added a TODO item in the parser to remove these.

  mysql-test/r/subselect.result@stripped, 2007-11-06 12:42:18+02:00, gkodinov@stripped +24
-0
    Bug #32036: test case

  mysql-test/t/subselect.test@stripped, 2007-11-06 12:42:18+02:00, gkodinov@stripped +20 -0
    Bug #32036: test case

  sql/item.cc@stripped, 2007-11-06 12:42:18+02:00, gkodinov@stripped +6 -1
    Bug #32036: Allow for the empty derived table 
     for parenthesis in the middle of the subqery stack. 

  sql/sql_yacc.yy@stripped, 2007-11-06 12:42:18+02:00, gkodinov@stripped +17 -0
    Bug #32036: Explain why we create a derived table
     for each parenthesis level when we strictly speaking 
     don't need to.

diff -Nrup a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
--- a/mysql-test/r/subselect.result	2007-10-30 14:27:19 +02:00
+++ b/mysql-test/r/subselect.result	2007-11-06 12:42:18 +02:00
@@ -4150,4 +4150,28 @@ SELECT ((a1,a2) IN (SELECT * FROM t2 WHE
 0
 0
 DROP TABLE t1, t2;
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+2
+2
+2
+EXPLAIN
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+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	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+EXPLAIN EXTENDED
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+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	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
+3	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	2	Using where
+Warnings:
+Note	1276	Field or reference 'test.t1.a' of SELECT #3 was resolved in SELECT #1
+Note	1003	select 2 AS `2` from `test`.`t1` where exists(select 1 AS `1` from `test`.`t2`
where (`test`.`t1`.`a` = `test`.`t2`.`a`))
+DROP TABLE t1,t2;
 End of 5.0 tests.
diff -Nrup a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
--- a/mysql-test/t/subselect.test	2007-10-30 14:27:19 +02:00
+++ b/mysql-test/t/subselect.test	2007-11-06 12:42:18 +02:00
@@ -3002,4 +3002,24 @@ INSERT INTO t2 VALUES (103, 203);
 SELECT ((a1,a2) IN (SELECT * FROM t2 WHERE b2 > 0)) IS NULL FROM t1;
 DROP TABLE t1, t2;
 
+#
+# Bug #32036: EXISTS within a WHERE clause with a UNION crashes MySQL 5.122
+#
+
+CREATE TABLE t1 (a INT);
+CREATE TABLE t2 (a INT);
+
+INSERT INTO t1 VALUES (1),(2);
+INSERT INTO t2 VALUES (1),(2);
+
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+
+EXPLAIN
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+
+EXPLAIN EXTENDED
+SELECT 2 FROM t1 WHERE EXISTS ((SELECT 1 FROM t2 WHERE t1.a=t2.a));
+
+DROP TABLE t1,t2;
+
 --echo End of 5.0 tests.
diff -Nrup a/sql/item.cc b/sql/item.cc
--- a/sql/item.cc	2007-10-23 18:51:36 +03:00
+++ b/sql/item.cc	2007-11-06 12:42:18 +02:00
@@ -3531,7 +3531,12 @@ Item_field::fix_outer_field(THD *thd, Fi
     select= outer_context->select_lex;
     Item_subselect *prev_subselect_item=
       last_checked_context->select_lex->master_unit()->item;
-    last_checked_context= outer_context;
+    /* 
+      There can be derived tables in the middle of the subquery stack. 
+      They don't have a subquery item. For details : sql_yacc.cc (subselect)
+    */
+    if (!outer_context->outer_context || select->master_unit()->item)
+      last_checked_context= outer_context;
     upward_lookup= TRUE;
 
     place= prev_subselect_item->parsing_place;
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2007-10-25 08:32:33 +03:00
+++ b/sql/sql_yacc.yy	2007-11-06 12:42:18 +02:00
@@ -9427,6 +9427,23 @@ subselect:
         {
           $$= $3;
         }
+        /* 
+          TODO: The parser generates here an derived table 
+          for each nested parenthesis, e.g.
+          SELECT 1 FROM t1 WHERE EXISTS 
+           ((SELECT 1 FROM t2))
+          is transformed to 
+          SELECT 1 FROM t1 WHERE EXISTS 
+           (SELECT * FROM (SELECT 1 FROM t2))
+          instead of just
+          SELECT 1 FROM t1 WHERE EXISTS (SELECT 1 FROM t2)
+ 
+          This is unfortunately non-avoidable with the current way the
+          parser operates. 
+          We need to separate code generation from parsing so we can
+          avoid problems like this one.
+        */ 
+
         | '(' subselect_start subselect ')'
           {
 	    THD *thd= YYTHD;