Bug#21904 (parser problem when using IN with a double "(())")

Before this fix, a IN predicate of the form: "IN (( subselect ))", with two
parenthesis, would be evaluated as a single row subselect: if the subselect
returns more that 1 row, the statement would fail.

The SQL:2003 standard defines a special exception in the specification,
and mandates that this particular form of IN predicate shall be equivalent
to "IN ( subselect )", which involves a table subquery and works with more
than 1 row.

This fix implements "IN (( subselect ))", "IN ((( subselect )))" etc
as per the SQL:2003 requirement.

All the details related to the implementation of this change have been
commented in the code, and the relevant sections of the SQL:2003 spec
are given for reference, so they are not repeated here.

Having access to the spec is a requirement to review in depth this patch.


mysql-test/r/subselect.result:
  Implement IN predicate special exceptions with subselects.
mysql-test/t/subselect.test:
  Implement IN predicate special exceptions with subselects.
sql/item_subselect.cc:
  Implement IN predicate special exceptions with subselects.
sql/item_subselect.h:
  Implement IN predicate special exceptions with subselects.
sql/sql_yacc.yy:
  Implement IN predicate special exceptions with subselects, cleanup.

1 files changed, 30 insertions, 0 deletions

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index bffecb3c84c..21624f23267 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -52,6 +52,10 @@ Item_subselect::Item_subselect():
 void Item_subselect::init(st_select_lex *select_lex,
 			  select_subselect *result)
 {
+  /*
+    Please see Item_singlerow_subselect::invalidate_and_restore_select_lex(),
+    which depends on alterations to the parse tree implemented here.
+  */
 
   DBUG_ENTER("Item_subselect::init");
   DBUG_PRINT("enter", ("select_lex: 0x%lx", (long) select_lex));
@@ -92,6 +96,12 @@ void Item_subselect::init(st_select_lex *select_lex,
   DBUG_VOID_RETURN;
 }
 
+st_select_lex *
+Item_subselect::get_select_lex()
+{
+  return unit->first_select();
+}
+
 void Item_subselect::cleanup()
 {
   DBUG_ENTER("Item_subselect::cleanup");
@@ -265,6 +275,26 @@ Item_singlerow_subselect::Item_singlerow_subselect(st_select_lex *select_lex)
   DBUG_VOID_RETURN;
 }
 
+st_select_lex *
+Item_singlerow_subselect::invalidate_and_restore_select_lex()
+{
+  DBUG_ENTER("Item_singlerow_subselect::invalidate_and_restore_select_lex");
+  st_select_lex *result= get_select_lex();
+
+  DBUG_ASSERT(result);
+
+  /*
+    This code restore the parse tree in it's state before the execution of
+    Item_singlerow_subselect::Item_singlerow_subselect(),
+    and in particular decouples this object from the SELECT_LEX,
+    so that the SELECT_LEX can be used with a different flavor
+    or Item_subselect instead, as part of query rewriting.
+  */
+  unit->item= NULL;
+
+  DBUG_RETURN(result);
+}
+
 Item_maxmin_subselect::Item_maxmin_subselect(THD *thd_param,
                                              Item_subselect *parent,
 					     st_select_lex *select_lex,