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@stripped, 2006-07-08 02:23:25+04:00, evgen@stripped +3 -0
  Fixed bug#16302: Quantified subquery without any tables 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' the reported bug
  occurs. As to no tables are specified in the subquery the 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.
  Thus the Item_maxmin_subselect class is more appropriate for handling such
  subqueries.
  
  The Item_in_subselect::single_value_transformer() function now checks
  whether tables are specified for the subquery. If no then this subselect is
  handled like a UNION using an Item_maxmin_subselect object.

  mysql-test/r/subselect.result@stripped, 2006-07-08 02:18:57+04:00, evgen@stripped +18 -0
    Added test case for bug#16302: Quantified subquery without any tables gives wrong results

  mysql-test/t/subselect.test@stripped, 2006-07-08 02:18:44+04:00, evgen@stripped +10 -0
    Added test case for bug#16302: Quantified subquery without any tables gives wrong results

  sql/item_subselect.cc@stripped, 2006-07-08 02:19:16+04:00, evgen@stripped +2 -1
    Fixed bug#16302: Quantified subquery without any tables gives wrong results
     
    The Item_in_subselect::single_value_transformer() function now checks
    whether tables are specified for the subquery. If no then this subselect is
    handled like a UNION using an Item_maxmin_subselect object.

# 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-07-08 02:23:28 +04:00
+++ 1.176/mysql-test/r/subselect.result	2006-07-08 02:23:28 +04:00
@@ -2835,3 +2835,21 @@
 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
+select 1 from dual where 1 < any (select 2 from dual);
+1
+1
+select 1 from dual where 1 < all (select 2 from dual where 1!=1);
+1
+1

--- 1.153/mysql-test/t/subselect.test	2006-07-08 02:23:28 +04:00
+++ 1.154/mysql-test/t/subselect.test	2006-07-08 02:23:28 +04:00
@@ -1820,4 +1820,14 @@
 
 purge master logs before (select adddate(current_timestamp(), interval -4 day));
 
+
+#
+# Bug#16302: Quantified subquery without any tables 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);
+select 1 from dual where 1 < any (select 2 from dual);
+select 1 from dual where 1 < all (select 2 from dual where 1!=1);
 # End of 4.1 tests

--- 1.140/sql/item_subselect.cc	2006-07-08 02:23:28 +04:00
+++ 1.141/sql/item_subselect.cc	2006-07-08 02:23:28 +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())