bk commit into 5.0 tree (konstantin:1.1912) 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.1912 05/06/04 16:25:27 konstantin@stripped +25 -0
  Patch two (the final one) for Bug#7306 "the server side preparedStatement
   error for LIMIT placeholder".
  The patch adds grammar support for LIMIT ?, ? and changes the
  type of ST_SELECT_LEX::select_limit,offset_limit from ha_rows to Item*,
  so that it can point to Item_param.

  sql/sql_yacc.yy
    1.389 05/06/04 16:25:21 konstantin@stripped +17 -12
    Add grammar support for LIMIT ?, ? clause.

  sql/sql_view.cc
    1.46 05/06/04 16:25:21 konstantin@stripped +3 -2
    Now ST_SELECT_LEX::select_limit, offset_limit have type Item *

  sql/sql_union.cc
    1.119 05/06/04 16:25:21 konstantin@stripped +1 -1
    Evaluate offset_limit_cnt using the new type of ST_SELECT_LEX::ofset_limit

  sql/sql_select.cc
    1.325 05/06/04 16:25:21 konstantin@stripped +1 -1
    - select_limit is now Item* so the proper way to check for default value
    is to compare it with NULL. 

  sql/sql_repl.cc
    1.139 05/06/04 16:25:21 konstantin@stripped +4 -2
    Use unit->set_limit to initialize limits.

  sql/sql_parse.cc
    1.446 05/06/04 16:25:21 konstantin@stripped +14 -10
    - use unit->set_limit() to initalize 
    unit->select_limit_cnt,offset_limit_cnt everyplace. 

  sql/sql_lex.h
    1.181 05/06/04 16:25:20 konstantin@stripped +1 -1
    Now ST_SELECT_LEX::select_limit, offset_limit have type Item *

  sql/sql_lex.cc
    1.147 05/06/04 16:25:20 konstantin@stripped +17 -16
    Now ST_SELECT_LEX::select_limit, offset_limit have type Item *

  sql/sql_handler.cc
    1.68 05/06/04 16:25:20 konstantin@stripped +5 -6
    - use unit->set_limit() to initalize 
    unit->select_limit_cnt,offset_limit_cnt everyplace. 

  sql/sql_error.cc
    1.33 05/06/04 16:25:20 konstantin@stripped +8 -8
    - use unit->set_limit() to set unit->select_limit_cnt, offset_limit_cnt
      evreryplace.
    - this change is also aware of bug#11095 "show warnings limit 0 returns 
    all rows instead of zero rows", so the one who merges the bugfix from
    4.1 can use local version of sql_error.cc.

  sql/sql_derived.cc
    1.72 05/06/04 16:25:20 konstantin@stripped +3 -5
    - use unit->set_limit() to set unit->select_limit_cnt, offset_limit_cnt
      evreryplace. Add a warning about use of set_limit in 
    mysql_derived_filling.

  sql/sql_class.h
    1.234 05/06/04 16:25:20 konstantin@stripped +3 -2
    Introduce new state 'INITIALIZED_FOR_SP' to be able to easily distinguish
    the first execution of a stored procedure from prepared statement prepare.

  sql/sp_head.cc
    1.142 05/06/04 16:25:20 konstantin@stripped +2 -2
    Add a special initalization state for stored procedures to 
    be able to easily distinguish the first execution of a stored procedure
    from prepared statement prepare.

  sql/item_subselect.cc
    1.107 05/06/04 16:25:20 konstantin@stripped +2 -2
    Now select_limit has type Item *.
    We can safely create an Item in Item_exists_subselect::fix_length_and_dec():
    it will be allocated in runtime memory root and freed in the end of
    execution.

  sql/item.h
    1.134 05/06/04 16:25:20 konstantin@stripped +8 -4
    Add a short-cut for (ulonglong) val_int() to Item.
    Add a constructor to Item_int() that accepts ulonglong.
    Simplify Item_uint constructor by using the c-tor above.

  mysql-test/t/ps.test
    1.40 05/06/04 16:25:20 konstantin@stripped +29 -0
    Add basic test coverage for LIMIT ?, ?.

  mysql-test/r/ps_7ndb.result
    1.26 05/06/04 16:25:20 konstantin@stripped +3 -7
    Fix test results: now LIMIT can contain placeholders.

  mysql-test/r/ps_6bdb.result
    1.27 05/06/04 16:25:20 konstantin@stripped +6 -7
    Fix test results: now LIMIT can contain placeholders.

  mysql-test/r/ps_5merge.result
    1.26 05/06/04 16:25:20 konstantin@stripped +12 -14
    Fix test results: now LIMIT can contain placeholders.

  mysql-test/r/ps_4heap.result
    1.25 05/06/04 16:25:20 konstantin@stripped +6 -7
    Fix test results: now LIMIT can contain placeholders.

  mysql-test/r/ps_3innodb.result
    1.29 05/06/04 16:25:20 konstantin@stripped +6 -7
    Fix test results: now LIMIT can contain placeholders.

  mysql-test/r/ps_2myisam.result
    1.26 05/06/04 16:25:19 konstantin@stripped +6 -7
    Fix test results: now LIMIT can contain placeholders.

  mysql-test/r/ps.result
    1.41 05/06/04 16:25:19 konstantin@stripped +41 -0
    Add basic test coverage for LIMIT ?, ? and fix test results.

  mysql-test/include/ps_query.inc
    1.12 05/06/04 16:25:19 konstantin@stripped +2 -4
    Fix existing tests: now LIMIT can contain placeholders.

  mysql-test/include/ps_modify.inc
    1.6 05/06/04 16:25:19 konstantin@stripped +2 -4
    Fix existing tests: now LIMIT can contain placeholders.

# 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-final

--- 1.133/sql/item.h	2005-06-03 14:56:45 +04:00
+++ 1.134/sql/item.h	2005-06-04 16:25:20 +04:00
@@ -335,6 +335,11 @@
   */
   virtual longlong val_int()=0;
   /*
+    This is just a shortcut to avoid the cast. You should still use
+    unsigned_flag to check the sign of the item.
+  */
+  inline ulonglong val_uint() { return (ulonglong) val_int(); }
+  /*
     Return string representation of this item object.
 
     SYNOPSIS
@@ -978,10 +983,10 @@
   longlong value;
   Item_int(int32 i,uint length=11) :value((longlong) i)
     { max_length=length; fixed= 1; }
-#ifdef HAVE_LONG_LONG
   Item_int(longlong i,uint length=21) :value(i)
     { max_length=length; fixed= 1; }
-#endif
+  Item_int(ulonglong i, uint length= 21) :value((longlong)i)
+    { max_length=length; fixed= 1; unsigned_flag= 1; }
   Item_int(const char *str_arg,longlong i,uint length) :value(i)
     { max_length=length; name=(char*) str_arg; fixed= 1; }
   Item_int(const char *str_arg, uint length=64);
@@ -1019,9 +1024,8 @@
 {
 public:
   Item_uint(const char *str_arg, uint length);
+  Item_uint(uint32 i) :Item_int((ulonglong) i, 10) {}
   Item_uint(const char *str_arg, longlong i, uint length);
-  Item_uint(uint32 i) :Item_int((longlong) i, 10) 
-    { unsigned_flag= 1; }
   double val_real()
     { DBUG_ASSERT(fixed == 1); return ulonglong2double((ulonglong)value); }
   String *val_str(String*);

--- 1.233/sql/sql_class.h	2005-06-03 00:02:41 +04:00
+++ 1.234/sql/sql_class.h	2005-06-04 16:25:20 +04:00
@@ -665,8 +665,8 @@
 #endif
   enum enum_state 
   {
-    INITIALIZED= 0, PREPARED= 1, EXECUTED= 3, CONVENTIONAL_EXECUTION= 2, 
-    ERROR= -1
+    INITIALIZED= 0, INITIALIZED_FOR_SP= 1, PREPARED= 2,
+    CONVENTIONAL_EXECUTION= 3, EXECUTED= 4, ERROR= -1
   };
   
   enum_state state;
@@ -695,6 +695,7 @@
   virtual Type type() const;
   virtual ~Item_arena() {};
 
+  inline bool is_stmt_prepare() const { return state == INITIALIZED; }
   inline bool is_stmt_prepare_or_first_sp_execute() const
   { return (int)state < (int)PREPARED; }
   inline bool is_first_stmt_execute() const { return state == PREPARED; }

--- 1.146/sql/sql_lex.cc	2005-05-30 20:56:07 +04:00
+++ 1.147/sql/sql_lex.cc	2005-06-04 16:25:20 +04:00
@@ -1138,8 +1138,9 @@
   order_list.elements= 0;
   order_list.first= 0;
   order_list.next= (byte**) &order_list.first;
-  select_limit= HA_POS_ERROR;
-  offset_limit= 0;
+  /* Set limit and offset to default values */
+  select_limit= 0;      /* denotes the default limit = HA_POS_ERROR */
+  offset_limit= 0;      /* denotes the default offset = 0 */
   with_sum_func= 0;
 }
 
@@ -1363,7 +1364,7 @@
 */
 bool st_select_lex::test_limit()
 {
-  if (select_limit != HA_POS_ERROR)
+  if (select_limit != 0)
   {
     my_error(ER_NOT_SUPPORTED_YET, MYF(0),
              "LIMIT & IN/ALL/ANY/SOME subquery");
@@ -1551,23 +1552,19 @@
        item->substype() == Item_subselect::IN_SUBS ||
        item->substype() == Item_subselect::ALL_SUBS))
   {
-    DBUG_ASSERT(!item->fixed || select_limit == 1L && offset_limit == 0L);
+    DBUG_ASSERT(!item->fixed ||
+                select_limit->val_int() == LL(1) && offset_limit == 0);
     return;
   }
 
   if (explicit_limit)
   {
     str->append(" limit ", 7);
-    char buff[20];
-    // latin1 is good enough for numbers
-    String st(buff, sizeof(buff),  &my_charset_latin1);
-    st.set((ulonglong)select_limit, &my_charset_latin1);
-    str->append(st);
+    select_limit->print(str);
     if (offset_limit)
     {
       str->append(',');
-      st.set((ulonglong)select_limit, &my_charset_latin1);
-      str->append(st);
+      offset_limit->print(str);
     }
   }
 }
@@ -1619,7 +1616,7 @@
           select_lex.with_sum_func == 0 &&
 	  select_lex.table_list.elements >= 1 &&
 	  !(select_lex.options & SELECT_DISTINCT) &&
-          select_lex.select_limit == HA_POS_ERROR);
+          select_lex.select_limit == 0);
 }
 
 
@@ -1756,11 +1753,15 @@
     values	- SELECT_LEX with initial values for counters
 */
 
-void st_select_lex_unit::set_limit(SELECT_LEX *values)
+void st_select_lex_unit::set_limit(SELECT_LEX *sl)
 {
-  offset_limit_cnt= values->offset_limit;
-  select_limit_cnt= values->select_limit+values->offset_limit;
-  if (select_limit_cnt < values->select_limit)
+  ulonglong select_limit_val;
+
+  select_limit_val= sl->select_limit ? sl->select_limit->val_uint() :
+                                       HA_POS_ERROR;
+  offset_limit_cnt= sl->offset_limit ? sl->offset_limit->val_uint() : ULL(0);
+  select_limit_cnt= select_limit_val + offset_limit_cnt;
+  if (select_limit_cnt < select_limit_val)
     select_limit_cnt= HA_POS_ERROR;		// no limit
 }
 

--- 1.180/sql/sql_lex.h	2005-05-30 20:56:07 +04:00
+++ 1.181/sql/sql_lex.h	2005-06-04 16:25:20 +04:00
@@ -488,7 +488,7 @@
   List<List_item>     expr_list;
   List<List_item>     when_list;      /* WHEN clause (expression) */
   SQL_LIST *gorder_list;
-  ha_rows select_limit, offset_limit; /* LIMIT clause parameters */
+  Item *select_limit, *offset_limit;  /* LIMIT clause parameters */
   // Arrays of pointers to top elements of all_fields list
   Item **ref_pointer_array;
 

--- 1.445/sql/sql_parse.cc	2005-06-03 00:02:41 +04:00
+++ 1.446/sql/sql_parse.cc	2005-06-04 16:25:21 +04:00
@@ -2351,7 +2351,8 @@
     {
       SELECT_LEX *param= lex->unit.global_parameters;
       if (!param->explicit_limit)
-	param->select_limit= thd->variables.select_limit;
+	param->select_limit=
+          new Item_int((ulonglong)thd->variables.select_limit);
     }
 
     select_result *result=lex->result;
@@ -3146,13 +3147,15 @@
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     if (update_precheck(thd, all_tables))
       break;
+    DBUG_ASSERT(select_lex->offset_limit == 0);
+    unit->set_limit(select_lex);
     res= (result= mysql_update(thd, all_tables,
                                select_lex->item_list,
                                lex->value_list,
                                select_lex->where,
                                select_lex->order_list.elements,
                                (ORDER *) select_lex->order_list.first,
-                               select_lex->select_limit,
+                               unit->select_limit_cnt,
                                lex->duplicates, lex->ignore));
     /* mysql_update return 2 if we need to switch to multi-update */
     if (result != 2)
@@ -3258,9 +3261,11 @@
     DBUG_ASSERT(first_table == all_tables && first_table != 0);
     if ((res= delete_precheck(thd, all_tables)))
       break;
+    DBUG_ASSERT(select_lex->offset_limit == 0);
+    unit->set_limit(select_lex);
     res = mysql_delete(thd, all_tables, select_lex->where,
                        &select_lex->order_list,
-                       select_lex->select_limit, select_lex->options);
+                       unit->select_limit_cnt, select_lex->options);
     break;
   }
   case SQLCOM_DELETE_MULTI:
@@ -3847,9 +3852,10 @@
     */
     if (check_db_used(thd, all_tables))
       goto error;
+    unit->set_limit(select_lex);
     res= mysql_ha_read(thd, first_table, lex->ha_read_mode, lex->ident.str,
                        lex->insert_list, lex->ha_rkey_mode, select_lex->where,
-                       select_lex->select_limit, select_lex->offset_limit);
+                       unit->select_limit_cnt, unit->offset_limit_cnt);
     break;
 
   case SQLCOM_BEGIN:
@@ -5130,7 +5136,6 @@
 {
   SELECT_LEX *select_lex= lex->current_select;
   select_lex->init_select();
-  select_lex->select_limit= HA_POS_ERROR;
   lex->orig_sql_command= SQLCOM_END;
   lex->wild= 0;
   if (select_lex == &lex->select_lex)
@@ -5194,7 +5199,7 @@
       fake->select_number= INT_MAX;
       fake->make_empty_select();
       fake->linkage= GLOBAL_OPTIONS_TYPE;
-      fake->select_limit= HA_POS_ERROR;
+      fake->select_limit= 0;
     }
   }
 
@@ -5242,8 +5247,8 @@
 {
   lex->sql_command=  SQLCOM_DELETE_MULTI;
   mysql_init_select(lex);
-  lex->select_lex.select_limit= lex->unit.select_limit_cnt=
-    HA_POS_ERROR;
+  lex->select_lex.select_limit= 0;
+  lex->unit.select_limit_cnt= HA_POS_ERROR;
   lex->select_lex.table_list.save_and_clear(&lex->auxilliary_table_list);
   lex->lock_option= using_update_log ? TL_READ_NO_INSERT : TL_READ;
   lex->query_tables= 0;
@@ -6757,8 +6762,7 @@
 
   if (select_lex->order_list.elements)
     msg= "ORDER BY";
-  else if (select_lex->select_limit && select_lex->select_limit !=
-	   HA_POS_ERROR)
+  else if (select_lex->select_limit)
     msg= "LIMIT";
   if (msg)
   {

--- 1.324/sql/sql_select.cc	2005-06-02 22:55:30 +04:00
+++ 1.325/sql/sql_select.cc	2005-06-04 16:25:21 +04:00
@@ -10051,7 +10051,7 @@
 	{
 	  join->do_send_rows= 0;
 	  if (join->unit->fake_select_lex)
-	    join->unit->fake_select_lex->select_limit= HA_POS_ERROR;
+	    join->unit->fake_select_lex->select_limit= 0;
 	  DBUG_RETURN(NESTED_LOOP_OK);
 	}
       }

--- 1.388/sql/sql_yacc.yy	2005-06-03 14:56:45 +04:00
+++ 1.389/sql/sql_yacc.yy	2005-06-04 16:25:21 +04:00
@@ -721,7 +721,7 @@
 	signed_literal now_or_signed_literal opt_escape
 	sp_opt_default
 	simple_ident_nospvar simple_ident_q
-        field_or_var
+        field_or_var limit_option
 
 %type <item_num>
 	NUM_literal
@@ -5542,8 +5542,8 @@
 	{
 	  LEX *lex= Lex;
 	  SELECT_LEX *sel= lex->current_select;
-          sel->offset_limit= 0L;
-          sel->select_limit= HA_POS_ERROR;
+          sel->offset_limit= 0;
+          sel->select_limit= 0;
 	}
 	| limit_clause {}
 	;
@@ -5558,21 +5558,21 @@
 	;
 
 limit_options:
-	ulong_num
+	limit_option
 	  {
             SELECT_LEX *sel= Select;
             sel->select_limit= $1;
-            sel->offset_limit= 0L;
+            sel->offset_limit= 0;
 	    sel->explicit_limit= 1;
 	  }
-	| ulong_num ',' ulong_num
+	| limit_option ',' limit_option
 	  {
 	    SELECT_LEX *sel= Select;
 	    sel->select_limit= $3;
 	    sel->offset_limit= $1;
 	    sel->explicit_limit= 1;
 	  }
-	| ulong_num OFFSET_SYM ulong_num
+	| limit_option OFFSET_SYM limit_option
 	  {
 	    SELECT_LEX *sel= Select;
 	    sel->select_limit= $1;
@@ -5580,18 +5580,23 @@
 	    sel->explicit_limit= 1;
 	  }
 	;
+limit_option:
+        param_marker
+        | ULONGLONG_NUM { $$= new Item_uint($1.str, $1.length); }
+        | LONG_NUM     { $$= new Item_uint($1.str, $1.length); }
+        | NUM           { $$= new Item_uint($1.str, $1.length); }
 
 
 delete_limit_clause:
 	/* empty */
 	{
 	  LEX *lex=Lex;
-	  lex->current_select->select_limit= HA_POS_ERROR;
+	  lex->current_select->select_limit= 0;
 	}
-	| LIMIT ulonglong_num
+	| LIMIT limit_option
 	{
 	  SELECT_LEX *sel= Select;
-	  sel->select_limit= (ha_rows) $2;
+	  sel->select_limit= $2;
 	  sel->explicit_limit= 1;
 	};
 
@@ -7942,8 +7947,8 @@
 	  LEX *lex=Lex;
 	  lex->sql_command = SQLCOM_HA_READ;
 	  lex->ha_rkey_mode= HA_READ_KEY_EXACT;	/* Avoid purify warnings */
-	  lex->current_select->select_limit= 1;
-	  lex->current_select->offset_limit= 0L;
+	  lex->current_select->select_limit= new Item_int(1);
+	  lex->current_select->offset_limit= 0;
 	  if (!lex->current_select->add_table_to_list(lex->thd, $2, 0, 0))
 	    YYABORT;
         }

--- 1.71/sql/sql_derived.cc	2005-04-01 14:02:22 +04:00
+++ 1.72/sql/sql_derived.cc	2005-06-04 16:25:20 +04:00
@@ -217,6 +217,8 @@
     queries defined. After temporary table is filled, if this is not EXPLAIN,
     then the entire unit / node is deleted. unit is deleted if UNION is used
     for derived table and node is deleted is it is a  simple SELECT.
+    If you use this function, make sure it's not called at prepare.
+    Due to evaluation of LIMIT clause it can not be used at prepared stage.
 
   RETURN
     0	ok
@@ -245,11 +247,7 @@
     }
     else
     {
-      unit->offset_limit_cnt= first_select->offset_limit;
-      unit->select_limit_cnt= first_select->select_limit+
-	first_select->offset_limit;
-      if (unit->select_limit_cnt < first_select->select_limit)
-	unit->select_limit_cnt= HA_POS_ERROR;
+      unit->set_limit(first_select);
       if (unit->select_limit_cnt == HA_POS_ERROR)
 	first_select->options&= ~OPTION_FOUND_ROWS;
 

--- 1.118/sql/sql_union.cc	2005-06-03 00:02:42 +04:00
+++ 1.119/sql/sql_union.cc	2005-06-04 16:25:21 +04:00
@@ -448,7 +448,7 @@
 	  table->no_keyread=1;
 	}
 	res= sl->join->error;
-	offset_limit_cnt= sl->offset_limit;
+	offset_limit_cnt= sl->offset_limit ? sl->offset_limit->val_uint() : 0;
 	if (!res)
 	{
 	  examined_rows+= thd->examined_row_count;

--- 1.106/sql/item_subselect.cc	2005-06-03 00:02:41 +04:00
+++ 1.107/sql/item_subselect.cc	2005-06-04 16:25:20 +04:00
@@ -602,8 +602,8 @@
    decimals= 0;
    max_length= 1;
    max_columns= engine->cols();
-   /* We need only 1 row to determinate existence */
-  unit->global_parameters->select_limit= 1;
+  /* We need only 1 row to determinate existence */
+  unit->global_parameters->select_limit= new Item_int(1);
 }
 
 double Item_exists_subselect::val_real()

--- 1.45/sql/sql_view.cc	2005-05-11 03:37:38 +04:00
+++ 1.46/sql/sql_view.cc	2005-06-04 16:25:21 +04:00
@@ -1000,8 +1000,9 @@
     we do not support updatable UNIONs in VIEW, so we can check just limit of
     LEX::select_lex
   */
-  if ((!view->view && !view->belong_to_view) || thd->lex->sql_command
== SQLCOM_INSERT ||
-      thd->lex->select_lex.select_limit == HA_POS_ERROR)
+  if ((!view->view && !view->belong_to_view) ||
+      thd->lex->sql_command == SQLCOM_INSERT ||
+      thd->lex->select_lex.select_limit == 0)
     DBUG_RETURN(FALSE); /* it is normal table or query without LIMIT */
   table= view->table;
   if (view->belong_to_view)

--- 1.5/mysql-test/include/ps_modify.inc	2004-11-04 14:45:49 +03:00
+++ 1.6/mysql-test/include/ps_modify.inc	2005-06-04 16:25:19 +04:00
@@ -175,10 +175,8 @@
 execute stmt1 ;
 select a,b from t1 where b = 'bla' ;
 # currently (May 2004, Version 4.1) it is impossible
--- error 1064
-prepare stmt1 from 'update t1 set b=''bla''
-where a=2
-limit ?';
+prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
+execute stmt1 using @arg00;
 
 --disable_query_log
 select '------ insert tests ------' as test_sequence ;

--- 1.11/mysql-test/include/ps_query.inc	2005-03-30 14:14:33 +04:00
+++ 1.12/mysql-test/include/ps_query.inc	2005-06-04 16:25:19 +04:00
@@ -300,10 +300,8 @@
 prepare stmt1 from ' select a,b from t1 order by a
 limit 1 ';
 execute stmt1 ;
-# currently (May 2004, Version 4.1) it is impossible
--- error 1064
-prepare stmt1 from ' select a,b from t1
-limit ? ';
+prepare stmt1 from ' select a,b from t1 limit ? ';
+execute stmt1 using @arg00;
 
 ##### parameter used in many places
 set @arg00='b' ;

--- 1.25/mysql-test/r/ps_2myisam.result	2005-05-06 00:01:35 +04:00
+++ 1.26/mysql-test/r/ps_2myisam.result	2005-06-04 16:25:19 +04:00
@@ -444,9 +444,10 @@
 execute stmt1 ;
 a	b
 1	one
-prepare stmt1 from ' select a,b from t1
-limit ? ';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 2
+prepare stmt1 from ' select a,b from t1 limit ? ';
+execute stmt1 using @arg00;
+a	b
+1	one
 set @arg00='b' ;
 set @arg01=0 ;
 set @arg02=2 ;
@@ -1381,10 +1382,8 @@
 select a,b from t1 where b = 'bla' ;
 a	b
 2	bla
-prepare stmt1 from 'update t1 set b=''bla''
-where a=2
-limit ?';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 3
+prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
+execute stmt1 using @arg00;
 test_sequence
 ------ insert tests ------
 delete from t1 ;

--- 1.28/mysql-test/r/ps_3innodb.result	2005-05-06 00:01:35 +04:00
+++ 1.29/mysql-test/r/ps_3innodb.result	2005-06-04 16:25:20 +04:00
@@ -444,9 +444,10 @@
 execute stmt1 ;
 a	b
 1	one
-prepare stmt1 from ' select a,b from t1
-limit ? ';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 2
+prepare stmt1 from ' select a,b from t1 limit ? ';
+execute stmt1 using @arg00;
+a	b
+1	one
 set @arg00='b' ;
 set @arg01=0 ;
 set @arg02=2 ;
@@ -1364,10 +1365,8 @@
 select a,b from t1 where b = 'bla' ;
 a	b
 2	bla
-prepare stmt1 from 'update t1 set b=''bla''
-where a=2
-limit ?';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 3
+prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
+execute stmt1 using @arg00;
 test_sequence
 ------ insert tests ------
 delete from t1 ;

--- 1.24/mysql-test/r/ps_4heap.result	2005-05-06 00:01:35 +04:00
+++ 1.25/mysql-test/r/ps_4heap.result	2005-06-04 16:25:20 +04:00
@@ -445,9 +445,10 @@
 execute stmt1 ;
 a	b
 1	one
-prepare stmt1 from ' select a,b from t1
-limit ? ';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 2
+prepare stmt1 from ' select a,b from t1 limit ? ';
+execute stmt1 using @arg00;
+a	b
+1	one
 set @arg00='b' ;
 set @arg01=0 ;
 set @arg02=2 ;
@@ -1365,10 +1366,8 @@
 select a,b from t1 where b = 'bla' ;
 a	b
 2	bla
-prepare stmt1 from 'update t1 set b=''bla''
-where a=2
-limit ?';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 3
+prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
+execute stmt1 using @arg00;
 test_sequence
 ------ insert tests ------
 delete from t1 ;

--- 1.25/mysql-test/r/ps_5merge.result	2005-05-06 00:01:35 +04:00
+++ 1.26/mysql-test/r/ps_5merge.result	2005-06-04 16:25:20 +04:00
@@ -487,9 +487,10 @@
 execute stmt1 ;
 a	b
 1	one
-prepare stmt1 from ' select a,b from t1
-limit ? ';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 2
+prepare stmt1 from ' select a,b from t1 limit ? ';
+execute stmt1 using @arg00;
+a	b
+1	one
 set @arg00='b' ;
 set @arg01=0 ;
 set @arg02=2 ;
@@ -1407,10 +1408,8 @@
 select a,b from t1 where b = 'bla' ;
 a	b
 2	bla
-prepare stmt1 from 'update t1 set b=''bla''
-where a=2
-limit ?';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 3
+prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
+execute stmt1 using @arg00;
 test_sequence
 ------ insert tests ------
 delete from t1 ;
@@ -3500,9 +3499,10 @@
 execute stmt1 ;
 a	b
 1	one
-prepare stmt1 from ' select a,b from t1
-limit ? ';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 2
+prepare stmt1 from ' select a,b from t1 limit ? ';
+execute stmt1 using @arg00;
+a	b
+1	one
 set @arg00='b' ;
 set @arg01=0 ;
 set @arg02=2 ;
@@ -4420,10 +4420,8 @@
 select a,b from t1 where b = 'bla' ;
 a	b
 2	bla
-prepare stmt1 from 'update t1 set b=''bla''
-where a=2
-limit ?';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 3
+prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
+execute stmt1 using @arg00;
 test_sequence
 ------ insert tests ------
 delete from t1 ;

--- 1.26/mysql-test/r/ps_6bdb.result	2005-05-06 00:01:35 +04:00
+++ 1.27/mysql-test/r/ps_6bdb.result	2005-06-04 16:25:20 +04:00
@@ -444,9 +444,10 @@
 execute stmt1 ;
 a	b
 1	one
-prepare stmt1 from ' select a,b from t1
-limit ? ';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 2
+prepare stmt1 from ' select a,b from t1 limit ? ';
+execute stmt1 using @arg00;
+a	b
+1	one
 set @arg00='b' ;
 set @arg01=0 ;
 set @arg02=2 ;
@@ -1364,10 +1365,8 @@
 select a,b from t1 where b = 'bla' ;
 a	b
 2	bla
-prepare stmt1 from 'update t1 set b=''bla''
-where a=2
-limit ?';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 3
+prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
+execute stmt1 using @arg00;
 test_sequence
 ------ insert tests ------
 delete from t1 ;

--- 1.25/mysql-test/r/ps_7ndb.result	2005-05-06 00:01:35 +04:00
+++ 1.26/mysql-test/r/ps_7ndb.result	2005-06-04 16:25:20 +04:00
@@ -1,6 +1,4 @@
-use test;
-drop table if exists t1, t9 ;
-create table t1
+use test; drop table if exists t1, t9 ; create table t1
 (
 a int, b varchar(30),
 primary key(a)
@@ -1364,10 +1362,8 @@
 select a,b from t1 where b = 'bla' ;
 a	b
 2	bla
-prepare stmt1 from 'update t1 set b=''bla''
-where a=2
-limit ?';
-ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to
your MySQL server version for the right syntax to use near '?' at line 3
+prepare stmt1 from 'update t1 set b=''bla'' where a=2 limit ?';
+execute stmt1 using @arg00;
 test_sequence
 ------ insert tests ------
 delete from t1 ;

--- 1.141/sql/sp_head.cc	2005-06-01 14:17:38 +04:00
+++ 1.142/sql/sp_head.cc	2005-06-04 16:25:20 +04:00
@@ -320,7 +320,7 @@
     *sp_lex_sp_key(const byte *ptr, uint *plen, my_bool first);
   DBUG_ENTER("sp_head::sp_head");
 
-  state= INITIALIZED;
+  state= INITIALIZED_FOR_SP;
   m_backpatch.empty();
   m_lex.empty();
   hash_init(&m_sptabs, system_charset_info, 0, 0, 0, sp_table_key, 0, 0);
@@ -1078,7 +1078,7 @@
   DBUG_ENTER("sp_head::restore_thd_mem_root");
   Item *flist= free_list;	// The old list
   set_item_arena(thd);          // Get new free_list and mem_root
-  state= INITIALIZED;
+  state= INITIALIZED_FOR_SP;
 
   DBUG_PRINT("info", ("mem_root 0x%lx returned from thd mem root 0x%lx",
                       (ulong) &mem_root, (ulong) &thd->mem_root));

--- 1.138/sql/sql_repl.cc	2005-06-02 08:15:16 +04:00
+++ 1.139/sql/sql_repl.cc	2005-06-04 16:25:21 +04:00
@@ -1316,6 +1316,7 @@
   if (mysql_bin_log.is_open())
   {
     LEX_MASTER_INFO *lex_mi= &thd->lex->mi;
+    SELECT_LEX_UNIT *unit= &thd->lex->unit;
     ha_rows event_count, limit_start, limit_end;
     my_off_t pos = max(BIN_LOG_HEADER_SIZE, lex_mi->pos); // user-friendly
     char search_file_name[FN_REFLEN], *name;
@@ -1324,8 +1325,9 @@
     LOG_INFO linfo;
     Log_event* ev;
 
-    limit_start= thd->lex->current_select->offset_limit;
-    limit_end= thd->lex->current_select->select_limit + limit_start;
+    unit->set_limit(thd->lex->current_select);
+    limit_start= unit->offset_limit_cnt;
+    limit_end= unit->select_limit_cnt;
 
     name= search_file_name;
     if (log_file_name)

--- 1.67/sql/sql_handler.cc	2005-06-01 12:52:58 +04:00
+++ 1.68/sql/sql_handler.cc	2005-06-04 16:25:20 +04:00
@@ -321,8 +321,8 @@
     key_expr
     ha_rkey_mode
     cond
-    select_limit
-    offset_limit
+    select_limit_cnt
+    offset_limit_cnt
 
   RETURN
     FALSE ok
@@ -333,7 +333,7 @@
                    enum enum_ha_read_modes mode, char *keyname,
                    List<Item> *key_expr,
                    enum ha_rkey_function ha_rkey_mode, Item *cond,
-                   ha_rows select_limit,ha_rows offset_limit)
+                   ha_rows select_limit_cnt, ha_rows offset_limit_cnt)
 {
   TABLE_LIST    *hash_tables;
   TABLE         *table;
@@ -429,7 +429,6 @@
   if (insert_fields(thd, tables, tables->db, tables->alias, &it, 0, 0))
     goto err0;
 
-  select_limit+=offset_limit;
   protocol->send_fields(&list, Protocol::SEND_NUM_ROWS | Protocol::SEND_EOF);
 
   HANDLER_TABLES_HACK(thd);
@@ -447,7 +446,7 @@
 
   table->file->init_table_handle_for_HANDLER();
 
-  for (num_rows=0; num_rows < select_limit; )
+  for (num_rows=0; num_rows < select_limit_cnt; )
   {
     switch (mode) {
     case RFIRST:
@@ -535,7 +534,7 @@
     }
     if (cond && !cond->val_int())
       continue;
-    if (num_rows >= offset_limit)
+    if (num_rows >= offset_limit_cnt)
     {
       Item *item;
       protocol->prepare_for_resend();

--- 1.40/mysql-test/r/ps.result	2005-05-16 14:34:18 +04:00
+++ 1.41/mysql-test/r/ps.result	2005-06-04 16:25:19 +04:00
@@ -634,3 +634,44 @@
 3
 deallocate prepare stmt;
 drop table t1, t2;
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+prepare stmt from "select * from t1 limit ?, ?";
+set @offset=0, @limit=1;
+execute stmt using @offset, @limit;
+a
+1
+select * from t1 limit 0, 1;
+a
+1
+set @offset=3, @limit=2;
+execute stmt using @offset, @limit;
+a
+4
+5
+select * from t1 limit 3, 2;
+a
+4
+5
+prepare stmt from "select * from t1 limit ?";
+execute stmt using @limit;
+a
+1
+2
+prepare stmt from "select * from t1 where a in (select a from t1 limit ?)";
+ERROR 42000: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME
subquery'
+prepare stmt from "select * from t1 union all select * from t1 limit ?, ?";
+set @offset=9;
+set @limit=2;
+execute stmt using @offset, @limit;
+a
+10
+1
+prepare stmt from "(select * from t1 limit ?, ?) union all
+                   (select * from t1 limit ?, ?) order by a limit ?";
+execute stmt using @offset, @limit, @offset, @limit, @limit;
+a
+10
+10
+drop table t1;
+deallocate prepare stmt;

--- 1.39/mysql-test/t/ps.test	2005-05-16 14:34:19 +04:00
+++ 1.40/mysql-test/t/ps.test	2005-06-04 16:25:20 +04:00
@@ -664,3 +664,32 @@
 
 deallocate prepare stmt;
 drop table t1, t2;
+
+#
+# Bug#7306 LIMIT ?, ? and also WL#1785 " Prepared statements: implement
+# support for placeholders in LIMIT clause."
+# Add basic test coverage for the feature.
+# 
+create table t1 (a int);
+insert into t1 (a) values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
+prepare stmt from "select * from t1 limit ?, ?";
+set @offset=0, @limit=1;
+execute stmt using @offset, @limit;
+select * from t1 limit 0, 1;
+set @offset=3, @limit=2;
+execute stmt using @offset, @limit;
+select * from t1 limit 3, 2;
+prepare stmt from "select * from t1 limit ?";
+execute stmt using @limit;
+--error 1235
+prepare stmt from "select * from t1 where a in (select a from t1 limit ?)";
+prepare stmt from "select * from t1 union all select * from t1 limit ?, ?";
+set @offset=9;
+set @limit=2;
+execute stmt using @offset, @limit;
+prepare stmt from "(select * from t1 limit ?, ?) union all
+                   (select * from t1 limit ?, ?) order by a limit ?";
+execute stmt using @offset, @limit, @offset, @limit, @limit;
+
+drop table t1;
+deallocate prepare stmt;

--- 1.32/sql/sql_error.cc	2005-04-22 14:53:42 +04:00
+++ 1.33/sql/sql_error.cc	2005-06-04 16:25:20 +04:00
@@ -225,20 +225,22 @@
 
   MYSQL_ERROR *err;
   SELECT_LEX *sel= &thd->lex->select_lex;
-  ha_rows offset= sel->offset_limit, limit= sel->select_limit;
+  SELECT_LEX_UNIT *unit= &thd->lex->unit;
+  ha_rows idx= 0;
   Protocol *protocol=thd->protocol;
-  
+
+  unit->set_limit(sel);
+
   List_iterator_fast<MYSQL_ERROR> it(thd->warn_list);
   while ((err= it++))
   {
     /* Skip levels that the user is not interested in */
     if (!(levels_to_show & ((ulong) 1 << err->level)))
       continue;
-    if (offset)
-    {
-      offset--;
+    if (++idx <= unit->offset_limit_cnt)
       continue;
-    }
+    if (idx > unit->select_limit_cnt)
+      break;
     protocol->prepare_for_resend();
     protocol->store(warning_level_names[err->level],
 		    warning_level_length[err->level], system_charset_info);
@@ -246,8 +248,6 @@
     protocol->store(err->msg, strlen(err->msg), system_charset_info);
     if (protocol->write())
       DBUG_RETURN(TRUE);
-    if (!--limit)
-      break;
   }
   send_eof(thd);
   DBUG_RETURN(FALSE);