bk commit into 4.1 tree (evgen:1.2516) BUG#16302

[eugene]

Below is the list of changes that have just been committed into a local
4.1 repository of evgen. When evgen 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
  1.2516 06/07/07 02:20:31 evgen@stripped +3 -0
  Fixed bug#16302: Non-table subquery gives wrong results
  
  The ALL/ANY subqueries are the subject of MIN/MAX optimization. The matter
  of this optimization is to embed MIN() or MAX() function into the subquery
  in order to get only one row by which we can tell whether the expression
  with ALL/ANY subquery is true or false.
  But when it is applied to a subquery like 'select a_constant' reported bug
  occurs. Due to no tables are specified in the subquery do_select() function 
  isn't called for the optimized subquery and thus no values were added 
  to a MIN()/MAX() function and it returns NULL instead of a_constant.
  This leads to a wrong query result.
  
  For the subquery like 'select a_constant' there is no reason to apply
  MIN/MAX optimization because the subquery anyway will return at most one row.
  So in this case Item_singlerow_subselect can be created without embedding
  MIN() or MAX() functions into the subquery.
  
  The Item_in_subselect::single_value_transformer() function now checks
  whether tables are specified for the subquery. If no then it just creates
  Item_singlerow_subselect object without embedding MIN()/MAX() functions
  into the subquery.

  sql/item_subselect.cc
    1.141 06/07/07 02:17:59 evgen@stripped +12 -2
    Fixed bug#16302: Non-table subquery gives wrong results
     
    The Item_in_subselect::single_value_transformer() function now checks
    whether tables are specified for the subquery. If no then it just creates
    Item_singlerow_subselect object without embedding MIN()/MAX() functions
    into the subquery.

  mysql-test/r/subselect.result
    1.176 06/07/07 02:17:40 evgen@stripped +12 -0
    Added test case for bug#16302: Non-table subquery gives wrong results

  mysql-test/t/subselect.test
    1.154 06/07/07 02:17:08 evgen@stripped +9 -0
    Added test case for bug#16302: Non-table subquery gives wrong results

# 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:	evgen
# Host:	moonbone.local
# Root:	/work/allany-4.1-mysql

--- 1.175/mysql-test/r/subselect.result	2006-04-28 13:23:28 +04:00
+++ 1.176/mysql-test/r/subselect.result	2006-07-07 02:17:40 +04:00
@@ -2835,3 +2835,15 @@
 4
 DROP TABLE t1,t2,t3;
 purge master logs before (select adddate(current_timestamp(), interval -4 day));
+select 1 from dual where 1 < any (select 2);
+1
+1
+select 1 from dual where 1 < all (select 2);
+1
+1
+select 1 from dual where 2 > any (select 1);
+1
+1
+select 1 from dual where 2 > all (select 1);
+1
+1

--- 1.153/mysql-test/t/subselect.test	2005-10-12 11:43:10 +04:00
+++ 1.154/mysql-test/t/subselect.test	2006-07-07 02:17:08 +04:00
@@ -1820,4 +1820,13 @@
 
 purge master logs before (select adddate(current_timestamp(), interval -4 day));
 
+
+#
+# Bug#16302: Non-table subquery gives wrong results
+#
+select 1 from dual where 1 < any (select 2);
+select 1 from dual where 1 < all (select 2);
+select 1 from dual where 2 > any (select 1);
+select 1 from dual where 2 > all (select 1);
+
 # End of 4.1 tests

--- 1.140/sql/item_subselect.cc	2006-04-28 13:23:28 +04:00
+++ 1.141/sql/item_subselect.cc	2006-07-07 02:17:59 +04:00
@@ -705,7 +705,8 @@
     if (!select_lex->group_list.elements &&
         !select_lex->having &&
 	!select_lex->with_sum_func &&
-	!(select_lex->next_select()))
+	!(select_lex->next_select()) &&
+        select_lex->table_list.elements)
     {
       Item_sum_hybrid *item;
       if (func->l_op())
@@ -744,7 +745,8 @@
 
       subs= new Item_singlerow_subselect(select_lex);
     }
-    else
+    else if (select_lex->table_list.elements ||
+             select_lex->next_select())
     {
       Item_maxmin_subselect *item;
       // remove LIMIT placed  by ALL/ANY subquery
@@ -753,6 +755,14 @@
       subs= item= new Item_maxmin_subselect(this, select_lex, func->l_op());
       if (upper_item)
         upper_item->set_sub_test(item);
+    }
+    else
+    {
+      /*
+        If there is no tables then no reason to add optimization
+        because there always will be only 1 row.
+      */
+      subs= new Item_singlerow_subselect(select_lex);
     }
     /* fix fields is already called for  left expression */
     substitution= func->create(left_expr, subs);