bk commit into 5.0 tree (konstantin:1.1910) BUG#7306

[konstantin]

Below is the list of changes that have just been committed into a local
5.0 repository of kostja. When kostja 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.1910 05/05/30 00:12:28 konstantin@stripped +7 -0
  Preparatory (and the most problematic) patch for Bug#7306
  "the server side preparedStatement error for LIMIT placeholder",
  which moves all uses of LIMIT clause from PREPARE to OPTIMIZE
  and later steps.
  Please review.

  sql/sql_union.cc
    1.116 05/05/30 00:12:23 konstantin@stripped +8 -28
    Class st_select_lex_unit was changed to avoid calls to 
     st_select_lex_unit::set_limit from places where it may be unknown.
    Now unit->select_limit_cnt is set at ::exec(). 
    st_select_lex_unit::init_prepare_fake_select_lex(): 
     - move out set_limit functionality
     - remove a few lines of dead code.
    st_select_lex_unit::prepare():
      - now we don't call set_limit at the time of prepare, so the value  
        of unit->select_limit_cnt may be unknown here. Use sl->select_limit
        instead.
    st_select_lex_unit::exec():
      - cleanup
      - call set_limit explicitly as it has been moved out of 
       init_prepare_fake_select_lex.

  sql/sql_select.cc
    1.325 05/05/30 00:12:23 konstantin@stripped +8 -18
    Move setting of JOIN::select_limit from JOIN::prepare
    to JOIN::optimize. At prepare, limit is unknown yet.
    Remove excessive cleanups from JOIN::reinit which were overwriting
    join->join_tab[i]->table->used_keys. This fixes the bug which was triggered
    by the change in item_subselect.cc. 

  sql/sql_lex.h
    1.179 05/05/30 00:12:22 konstantin@stripped +1 -1
    Cleanup signature of st_select_lex_unit::init_prepare_fake_select_lex().

  sql/sql_lex.cc
    1.145 05/05/30 00:12:22 konstantin@stripped +1 -3
    Consolidate setting of internal LIMIT for IN/ALL/ANY/EXISTS subqeries
    in one place, and hence remove it from st_select_lex::test_limit().

  sql/item_subselect.cc
    1.100 05/05/30 00:12:22 konstantin@stripped +9 -12
    Move setting of the internal LIMIT used for IN/ALL/ANY/EXISTS 
    subqueries to one place: Item_exists_subselect::fix_length_and_dec().
    This implies that unit->select_limit_cnt is not set until the item is 
    fixed. This is OK, as now LIMIT values are not used until JOIN::optimize.

  mysql-test/t/group_min_max.test
    1.12 05/05/30 00:12:22 konstantin@stripped +1 -1
    Replace 'key_len' with #, key_len changed from 163 to 146.
    The failing test case doesn't use subqueries, so the cause of the
    failure is mysterios. The only lame excuse is that the same change
    of key_len was observed in other queries of this test before, and 
    the test was patched similarly. Spent 1 hour trying to investigate.
    Timour: any idea?

  mysql-test/r/group_min_max.result
    1.13 05/05/30 00:12:22 konstantin@stripped +1 -1
    Portability fix. I can't repeat the test failure running the query
    stand-alone :(

# 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:	konstantin
# Host:	dragonfly.local
# Root:	/opt/local/work/mysql-5.0-7306-new

--- 1.144/sql/sql_lex.cc	2005-05-19 05:48:17 +04:00
+++ 1.145/sql/sql_lex.cc	2005-05-30 00:12:22 +04:00
@@ -1369,8 +1369,6 @@
              "LIMIT & IN/ALL/ANY/SOME subquery");
     return(1);
   }
-  // We need only 1 row to determinate existence
-  select_limit= 1;
   // no sense in ORDER BY without LIMIT
   order_list.empty();
   return(0);
@@ -1553,7 +1551,7 @@
        item->substype() == Item_subselect::IN_SUBS ||
        item->substype() == Item_subselect::ALL_SUBS))
   {
-    DBUG_ASSERT(select_limit == 1L && offset_limit == 0L);
+    DBUG_ASSERT(!item->fixed || select_limit == 1L && offset_limit == 0L);
     return;
   }
 

--- 1.178/sql/sql_lex.h	2005-05-23 22:38:07 +04:00
+++ 1.179/sql/sql_lex.h	2005-05-30 00:12:22 +04:00
@@ -445,7 +445,7 @@
 
   void print(String *str);
 
-  ulong init_prepare_fake_select_lex(THD *thd);
+  void init_prepare_fake_select_lex(THD *thd);
   inline bool is_prepared() { return prepared; }
   bool change_result(select_subselect *result, select_subselect *old_result);
   void set_limit(st_select_lex *values, st_select_lex *sl);

--- 1.324/sql/sql_select.cc	2005-05-23 21:01:14 +04:00
+++ 1.325/sql/sql_select.cc	2005-05-30 00:12:23 +04:00
@@ -465,13 +465,6 @@
   count_field_types(&tmp_table_param, all_fields, 0);
   ref_pointer_array_size= all_fields.elements*sizeof(Item*);
   this->group= group_list != 0;
-  row_limit= ((select_distinct || order || group_list) ? HA_POS_ERROR :
-	      unit_arg->select_limit_cnt);
-  /* select_limit is used to decide if we are likely to scan the whole table */
-  select_limit= unit_arg->select_limit_cnt;
-  if (having || (select_options & OPTION_FOUND_ROWS))
-    select_limit= HA_POS_ERROR;
-  do_send_rows = (unit_arg->select_limit_cnt) ? 1 : 0;
   unit= unit_arg;
 
 #ifdef RESTRICTED_GROUP
@@ -550,6 +543,13 @@
     DBUG_RETURN(0);
   optimized= 1;
 
+  row_limit= ((select_distinct || order || group_list) ? HA_POS_ERROR :
+	      unit->select_limit_cnt);
+  /* select_limit is used to decide if we are likely to scan the whole table */
+  select_limit= unit->select_limit_cnt;
+  if (having || (select_options & OPTION_FOUND_ROWS))
+    select_limit= HA_POS_ERROR;
+  do_send_rows = (unit->select_limit_cnt) ? 1 : 0;
   // Ignore errors of execution if option IGNORE present
   if (thd->lex->ignore)
     thd->lex->current_select->no_error= 1;
@@ -1110,18 +1110,7 @@
 JOIN::reinit()
 {
   DBUG_ENTER("JOIN::reinit");
-  /* TODO move to unit reinit */
-  unit->set_limit(select_lex, select_lex);
 
-  /* conds should not be used here, it is added just for safety */
-  if (tables_list)
-  {
-    if (setup_tables(thd, tables_list, &conds, &select_lex->leaf_tables,
-                     TRUE, FALSE))
-      DBUG_RETURN(1);
-  }
-
-  /* Reset of sum functions */
   first_record= 0;
 
   if (exec_tmp_table1)
@@ -1147,6 +1136,7 @@
   if (tmp_join)
     restore_tmp();
 
+  /* Reset of sum functions */
   if (sum_funcs)
   {
     Item_sum *func, **func_ptr= sum_funcs;

--- 1.115/sql/sql_union.cc	2005-03-31 11:39:43 +04:00
+++ 1.116/sql/sql_union.cc	2005-05-30 00:12:23 +04:00
@@ -115,27 +115,15 @@
     options of SELECT
 */
 
-ulong
+void
 st_select_lex_unit::init_prepare_fake_select_lex(THD *thd) 
 {
-  ulong options_tmp= thd->options | fake_select_lex->options;
   thd->lex->current_select= fake_select_lex;
-  offset_limit_cnt= global_parameters->offset_limit;
-  select_limit_cnt= global_parameters->select_limit +
-    global_parameters->offset_limit;
-
-  if (select_limit_cnt < global_parameters->select_limit)
-    select_limit_cnt= HA_POS_ERROR;		// no limit
-  if (select_limit_cnt == HA_POS_ERROR)
-    options_tmp&= ~OPTION_FOUND_ROWS;
-  else if (found_rows_for_union && !thd->lex->describe)
-    options_tmp|= OPTION_FOUND_ROWS;
   fake_select_lex->ftfunc_list_alloc.empty();
   fake_select_lex->ftfunc_list= &fake_select_lex->ftfunc_list_alloc;
   fake_select_lex->table_list.link_in_list((byte *)&result_table_list,
 					   (byte **)
 					   &result_table_list.next_local);
-  return options_tmp;
 }
 
 
@@ -217,10 +205,9 @@
       goto err;
 
     thd_arg->lex->current_select= sl;
-    set_limit(sl, sl);
 
     can_skip_order_by= is_union &&
-                       (!sl->braces || select_limit_cnt == HA_POS_ERROR);
+                       (!sl->braces || sl->select_limit == HA_POS_ERROR);
     
     res= join->prepare(&sl->ref_pointer_array,
 		       (TABLE_LIST*) sl->table_list.first, sl->with_wild,
@@ -340,7 +327,7 @@
       if (arena->is_stmt_prepare())
       {
 	/* prepare fake select to initialize it correctly */
-	(void) init_prepare_fake_select_lex(thd);
+	init_prepare_fake_select_lex(thd);
         /*
           Should be done only once (the only item_list per statement).
         */
@@ -429,12 +416,8 @@
 	res= sl->join->reinit();
       else
       {
-	if (sl != global_parameters && !describe)
-	{
-	  offset_limit_cnt= sl->offset_limit;
-	  select_limit_cnt= sl->select_limit+sl->offset_limit;
-	}
-	else
+        set_limit(sl, sl);
+	if (sl == global_parameters || describe)
 	{
 	  offset_limit_cnt= 0;
 	  /*
@@ -443,11 +426,7 @@
 	  */
 	  if (sl->order_list.first || describe)
 	    select_limit_cnt= HA_POS_ERROR;
-	  else
-	    select_limit_cnt= sl->select_limit+sl->offset_limit;
-	}
-	if (select_limit_cnt < sl->select_limit)
-	  select_limit_cnt= HA_POS_ERROR;		// no limit
+        }
 
         /*
           When using braces, SQL_CALC_FOUND_ROWS affects the whole query:
@@ -512,7 +491,8 @@
 
     if (!thd->is_fatal_error)				// Check if EOM
     {
-      ulong options_tmp= init_prepare_fake_select_lex(thd);
+      set_limit(global_parameters, global_parameters);
+      init_prepare_fake_select_lex(thd);
       JOIN *join= fake_select_lex->join;
       if (!join)
       {

--- 1.99/sql/item_subselect.cc	2005-04-01 03:13:23 +04:00
+++ 1.100/sql/item_subselect.cc	2005-05-30 00:12:22 +04:00
@@ -537,8 +537,6 @@
   null_value= 0; //can't be NULL
   maybe_null= 0; //can't be NULL
   value= 0;
-  // We need only 1 row to determinate existence
-  select_lex->master_unit()->global_parameters->select_limit= 1;
   DBUG_VOID_RETURN;
 }
 
@@ -605,6 +603,8 @@
    decimals= 0;
    max_length= 1;
    max_columns= engine->cols();
+   /* We need only 1 row to determinate existence */
+  unit->global_parameters->select_limit= 1;
 }
 
 double Item_exists_subselect::val_real()
@@ -854,9 +854,6 @@
     else
     {
       Item_maxmin_subselect *item;
-      // remove LIMIT placed  by ALL/ANY subquery
-      select_lex->master_unit()->global_parameters->select_limit=
-	HA_POS_ERROR;
       subs= item= new Item_maxmin_subselect(thd, this, select_lex, func->l_op());
       if (upper_item)
         upper_item->set_sub_test(item);
@@ -1286,13 +1283,10 @@
 			       select_subselect *result,
 			       Item_subselect *item)
   :subselect_engine(item, result),
-   prepared(0), optimized(0), executed(0), join(0)
+   prepared(0), optimized(0), executed(0),
+   select_lex(select), join(0)
 {
-  select_lex= select;
-  SELECT_LEX_UNIT *unit= select_lex->master_unit();
-  unit->set_limit(unit->global_parameters, select_lex);
-  unit->item= item;
-  this->select_lex= select_lex;
+  select_lex->master_unit()->item= item;
 }
 
 
@@ -1440,7 +1434,10 @@
   thd->lex->current_select= select_lex;
   if (!optimized)
   {
-    optimized=1;
+    SELECT_LEX_UNIT *unit= select_lex->master_unit();
+
+    optimized= 1;
+    unit->set_limit(unit->global_parameters, select_lex);
     if (join->optimize())
     {
       thd->where= save_where;

--- 1.12/mysql-test/r/group_min_max.result	2005-05-19 06:54:28 +04:00
+++ 1.13/mysql-test/r/group_min_max.result	2005-05-30 00:12:22 +04:00
@@ -1389,7 +1389,7 @@
 1	SIMPLE	t1	range	NULL	idx_t1_1	147	NULL	17	Using where; Using index for group-by
 explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b
> 'a') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
-1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index
for group-by
+1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	#	NULL	#	Using where; Using index
for group-by
 explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c
> 'b111') group by a1,a2,b;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	SIMPLE	t2	range	idx_t2_0,idx_t2_1,idx_t2_2	idx_t2_1	163	NULL	#	Using where; Using index
for group-by

--- 1.11/mysql-test/t/group_min_max.test	2005-05-19 06:54:28 +04:00
+++ 1.12/mysql-test/t/group_min_max.test	2005-05-30 00:12:22 +04:00
@@ -422,7 +422,7 @@
 explain select a1,a2,b,min(c) from t1 where (a1 > 'a') and (a2 > 'a') and (b = 'c')
group by a1,a2,b;
 explain select a1,a2,b,min(c) from t1 where (ord(a1) > 97) and (ord(a2) + ord(a1) >
194) and (b = 'c') group by a1,a2,b;
 
---replace_column 9 #
+--replace_column 7 # 9 #
 explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (b
> 'a') group by a1,a2,b;
 --replace_column 9 #
 explain select a1,a2,b,min(c),max(c) from t2 where (a1 >= 'c' or a2 < 'b') and (c
> 'b111') group by a1,a2,b;