MySQL Lists are EOL. Please join:

List:Internals« Previous MessageNext Message »
From:konstantin Date:March 3 2005 1:28pm
Subject:bk commit into 5.0 tree (konstantin:1.1786) BUG#8849
View as plain text  
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.1786 05/03/03 16:28:27 konstantin@stripped +11 -0
  Fixes for bug#8115 "Server Crash with prepared statement"
   and bug#8849 "problem with insert statement with table alias's": 
  make equality propagation work in stored procedures and prepared 
  statements.
  Equality propagation can change AND/OR structure of ON expressions,
  so the fix is to provide each execution of PS/SP with it's own
  copy of AND/OR tree. We have been doing that already for WHERE clauses,
  now ON clauses are also copied.

  sql/table.h
    1.90 05/03/03 16:28:22 konstantin@stripped +9 -0
    Add declaration for TABLE::prep_on_expr.

  sql/sql_select.cc
    1.293 05/03/03 16:28:22 konstantin@stripped +26 -13
    Implementation of equality propagation inspected with regard to 
    prepared statements and stored procedures. We now restore
    AND/OR structure of every ON expression in addition to AND/OR
    structure of WHERE clauses when reexecuting a PS/SP.

  sql/sql_prepare.cc
    1.102 05/03/03 16:28:22 konstantin@stripped +27 -8
    Implement init_stmt_after_parse() which prepares AND/OR
    structure of all ON expressions and WHERE clauses of a statement
    for copying.

  sql/sp_head.cc
    1.117 05/03/03 16:28:22 konstantin@stripped +1 -0
    Call init_stmt_after_parse in restore_lex(), which is used to
    grab TABLE_LIST and SELECT_LEX list of a parsed substatement of
    stored procedure. This is a necessary post-init step which 
    must be done for any statement which can be executed many times.

  sql/mysql_priv.h
    1.268 05/03/03 16:28:22 konstantin@stripped +1 -0
    Add declaration for init_stmt_after_parse.

  sql/item_cmpfunc.h
    1.92 05/03/03 16:28:22 konstantin@stripped +6 -0
    Comment how Item_equal works with PS/SP.

  sql/item_cmpfunc.cc
    1.139 05/03/03 16:28:22 konstantin@stripped +17 -6
    Comment a parse tree transformation.

  mysql-test/t/sp.test
    1.106 05/03/03 16:28:22 konstantin@stripped +49 -0
    A test case for Bug#8849 "problem with insert statement with table 
    alias's".

  mysql-test/t/ps.test
    1.33 05/03/03 16:28:21 konstantin@stripped +33 -0
    A test case for Bug#8115 "Server Crash with prepared statement".

  mysql-test/r/sp.result
    1.112 05/03/03 16:28:21 konstantin@stripped +38 -0
    Bug#8849: test results fixed.

  mysql-test/r/ps.result
    1.34 05/03/03 16:28:21 konstantin@stripped +25 -0
    Bug#8115: test results fixed.

# 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:	/media/sda1/mysql/mysql-5.0-8849

--- 1.138/sql/item_cmpfunc.cc	2005-03-02 12:41:10 +03:00
+++ 1.139/sql/item_cmpfunc.cc	2005-03-03 16:28:22 +03:00
@@ -2289,6 +2289,21 @@
 
   if (check_stack_overrun(thd, buff))
     return TRUE;				// Fatal error flag is set!
+  /*
+    The following optimization reduces the depth of an AND-OR tree.
+    E.g. a WHERE clause like
+      F1 AND (F2 AND (F2 AND F4))
+    is parsed into a tree with the same nested structure as defined
+    by braces. This optimization will transform such tree into
+      AND (F1, F2, F3, F4).
+    Trees of OR items are flattened as well:
+      ((F1 OR F2) OR (F3 OR F4))   =>   OR (F1, F2, F3, F4)
+    Items for removed AND/OR levels will dangle until the death of the
+    entire statement.
+    The optimization is currently prepared statements and stored procedures
+    friendly as it doesn't allocate any memory and its effects are durable
+    (i.e. do not depend on PS/SP arguments).
+  */
   while ((item=li++))
   {
     table_map tmp_table_map;
@@ -3265,6 +3280,7 @@
   const_item= c;
 }
 
+
 Item_equal::Item_equal(Item_equal *item_equal)
   : Item_bool_func(), eval_item(0), cond_false(0)
 {
@@ -3301,12 +3317,7 @@
 
 uint Item_equal::members()
 {
-  uint count= 0;
-  List_iterator_fast<Item_field> li(fields);
-  Item_field *item;
-  while ((item= li++))
-    count++;
-  return count;
+  return fields.elements;
 }
 
 

--- 1.91/sql/item_cmpfunc.h	2005-03-02 12:38:14 +03:00
+++ 1.92/sql/item_cmpfunc.h	2005-03-03 16:28:22 +03:00
@@ -1095,6 +1095,12 @@
   predicates that can not be used to access tables in the investigated
   plan for those, obtained by substitution of some fields for equal fields,
   that can be used.     
+
+  Prepared Statements/Stored Procedures note: instances of class
+  Item_equal are created only at the time a PS/SP is executed and
+  are deleted in the end of execution. All changes made to these
+  objects need not be registered in the list of changes of the parse
+  tree and do not harm PS/SP re-execution.
 */
 
 class Item_equal: public Item_bool_func

--- 1.267/sql/mysql_priv.h	2005-03-02 16:39:20 +03:00
+++ 1.268/sql/mysql_priv.h	2005-03-03 16:28:22 +03:00
@@ -805,6 +805,7 @@
 void mysql_stmt_reset(THD *thd, char *packet);
 void mysql_stmt_get_longdata(THD *thd, char *pos, ulong packet_length);
 void reset_stmt_for_execute(THD *thd, LEX *lex);
+void init_stmt_after_parse(THD*, LEX*);
 
 /* sql_error.cc */
 MYSQL_ERROR *push_warning(THD *thd, MYSQL_ERROR::enum_warning_level level, uint code,

--- 1.292/sql/sql_select.cc	2005-03-01 23:19:14 +03:00
+++ 1.293/sql/sql_select.cc	2005-03-03 16:28:22 +03:00
@@ -6192,9 +6192,9 @@
     For b=c it will be called with *cond_equal=(0,[Item_equal(a,b)])
     and will transform *cond_equal into CE=(0,[Item_equal(a,b,c)]).
     For b=2 it will be called with *cond_equal=(ptr(CE),[])
-    and will transform *cond_equal into (ptr(CE,[Item_equal(2,a,b,c)]).
+    and will transform *cond_equal into (ptr(CE),[Item_equal(2,a,b,c)]).
     For f=e it will be called with *cond_equal=(ptr(CE), [])
-    and will transform *cond_equal into (ptr(CE,[Item_equal(f,e)]).
+    and will transform *cond_equal into (ptr(CE),[Item_equal(f,e)]).
 
   NOTES
     Now only fields that have the same type defintions (verified by
@@ -6463,6 +6463,11 @@
      */      
       while ((item= li++))
       {
+        /*
+          PS/SP note: we can safely remove a node from AND-OR
+          structure here because it's restored before each
+          re-execution of any prepared statement/stored procedure.
+        */
         if (check_equality(item, &cond_equal))
           li.remove();
       }
@@ -6501,6 +6506,11 @@
       if ((new_item = build_equal_items_for_cond(item, inherited))!= item)
       {
         /* This replacement happens only for standalone equalities */
+        /*
+          This is ok with PS/SP as the replacement is done for
+          arguments of an AND/OR item, which are restored for each
+          execution of PS/SP.
+        */
         li.replace(new_item);
       }
     }
@@ -6636,10 +6646,12 @@
         Item *expr;
         List<TABLE_LIST> *join_list= table->nested_join ?
 	                             &table->nested_join->join_list : NULL;
-        expr= build_equal_items(thd, table->on_expr, inherited, join_list,
-                                &table->cond_equal);
-        if (expr != table->on_expr)
-          thd->change_item_tree(&table->on_expr, expr);
+        /*
+          We can modify table->on_expr because its old value will
+          be restored before re-execution of PS/SP.
+        */
+        table->on_expr= build_equal_items(thd, table->on_expr, inherited,
+                                          join_list, &table->cond_equal);
       }
     }
   }
@@ -6866,10 +6878,14 @@
     while ((item= li++))
     {
       Item *new_item =substitute_for_best_equal_field(item, cond_equal,
-                                                        table_join_idx);
+                                                      table_join_idx);
+      /*
+        This works OK with PS/SP re-execution as changes are made to
+        the arguments of AND/OR items only
+      */
       if (new_item != item)
         li.replace(new_item);
-   }
+    }
 
     if (and_level)
     {
@@ -7198,7 +7214,7 @@
 	*/ 
         expr= simplify_joins(join, &nested_join->join_list,
                              table->on_expr, FALSE);
-        table->on_expr= expr;
+        table->prep_on_expr= table->on_expr= expr;
       }
       nested_join->used_tables= (table_map) 0;
       nested_join->not_null_tables=(table_map) 0;
@@ -7238,7 +7254,7 @@
         }
         else
           conds= table->on_expr; 
-        table->on_expr= 0;
+        table->prep_on_expr= table->on_expr= 0;
       }
     }
     
@@ -7319,10 +7335,7 @@
   DBUG_ENTER("optimize_cond");
 
   if (!conds)
-  {
     *cond_value= Item::COND_TRUE;
-    select->prep_where= 0;
-  }
   else
   {
     /* 

--- 1.89/sql/table.h	2005-02-07 11:57:07 +03:00
+++ 1.90/sql/table.h	2005-03-03 16:28:22 +03:00
@@ -339,6 +339,15 @@
   char		*db, *alias, *table_name, *schema_table_name;
   char          *option;                /* Used by cache index  */
   Item		*on_expr;		/* Used with outer join */
+  /*
+    The scturcture of ON expression presented in the member above
+    can be changed during certain optimizations. This member
+    contains a snapshot of AND-OR structure of the ON expression
+    made after permanent transformations of the parse tree, and is
+    used to restore ON clause before every reexecution of a prepared
+    statement or stored procedure.
+  */
+  Item          *prep_on_expr;
   COND_EQUAL    *cond_equal;            /* Used with outer join */
   struct st_table_list *natural_join;	/* natural join on this table*/
   /* ... join ... USE INDEX ... IGNORE INDEX */

--- 1.111/mysql-test/r/sp.result	2005-03-02 19:25:51 +03:00
+++ 1.112/mysql-test/r/sp.result	2005-03-03 16:28:21 +03:00
@@ -2542,3 +2542,41 @@
 drop table t3|
 drop table t1;
 drop table t2;
+CREATE TABLE t1 (
+lpitnumber int(11) default NULL,
+lrecordtype int(11) default NULL
+);
+CREATE TABLE t2 (
+lbsiid int(11) NOT NULL default '0',
+ltradingmodeid int(11) NOT NULL default '0',
+ltradingareaid int(11) NOT NULL default '0',
+csellingprice decimal(19,4) default NULL,
+PRIMARY KEY  (lbsiid,ltradingmodeid,ltradingareaid)
+);
+CREATE TABLE t3 (
+lbsiid int(11) NOT NULL default '0',
+ltradingareaid int(11) NOT NULL default '0',
+PRIMARY KEY  (lbsiid,ltradingareaid)
+);
+CREATE PROCEDURE bug8849()
+begin
+insert into t3
+(
+t3.lbsiid,
+t3.ltradingareaid
+)
+select distinct t1.lpitnumber, t2.ltradingareaid
+from
+t2 join t1 on
+t1.lpitnumber = t2.lbsiid
+and t1.lrecordtype = 1
+left join t2 as price01 on
+price01.lbsiid = t2.lbsiid and
+price01.ltradingmodeid = 1 and
+t2.ltradingareaid = price01.ltradingareaid;
+end|
+call bug8849();
+call bug8849();
+call bug8849();
+drop procedure bug8849;
+drop tables t1,t2,t3;

--- 1.105/mysql-test/t/sp.test	2005-03-02 19:25:51 +03:00
+++ 1.106/mysql-test/t/sp.test	2005-03-03 16:28:22 +03:00
@@ -3086,3 +3086,52 @@
 drop table t1;
 drop table t2;
 
+#
+# Bug#8849: rolling back changes to AND/OR structure of ON and WHERE clauses 
+# in SP
+# 
+
+CREATE TABLE t1 (
+  lpitnumber int(11) default NULL,
+  lrecordtype int(11) default NULL
+);
+
+CREATE TABLE t2 (
+  lbsiid int(11) NOT NULL default '0',
+  ltradingmodeid int(11) NOT NULL default '0',
+  ltradingareaid int(11) NOT NULL default '0',
+  csellingprice decimal(19,4) default NULL,
+  PRIMARY KEY  (lbsiid,ltradingmodeid,ltradingareaid)
+);
+
+CREATE TABLE t3 (
+  lbsiid int(11) NOT NULL default '0',
+  ltradingareaid int(11) NOT NULL default '0',
+  PRIMARY KEY  (lbsiid,ltradingareaid)
+);
+
+delimiter |;
+CREATE PROCEDURE bug8849()
+begin
+  insert into t3
+  (
+   t3.lbsiid,
+   t3.ltradingareaid
+  )
+  select distinct t1.lpitnumber, t2.ltradingareaid
+  from
+    t2 join t1 on
+      t1.lpitnumber = t2.lbsiid
+      and t1.lrecordtype = 1
+    left join t2 as price01 on
+      price01.lbsiid = t2.lbsiid and
+      price01.ltradingmodeid = 1 and
+      t2.ltradingareaid = price01.ltradingareaid;
+end|
+delimiter ;|
+
+call bug8849();
+call bug8849();
+call bug8849();
+drop procedure bug8849;
+drop tables t1,t2,t3;

--- 1.116/sql/sp_head.cc	2005-02-25 17:21:00 +03:00
+++ 1.117/sql/sp_head.cc	2005-03-03 16:28:22 +03:00
@@ -819,6 +819,7 @@
   LEX *sublex= thd->lex;
   LEX *oldlex= (LEX *)m_lex.pop();
 
+  init_stmt_after_parse(thd, sublex);
   if (! oldlex)
     return;			// Nothing to restore
 

--- 1.33/mysql-test/r/ps.result	2005-03-03 13:29:32 +03:00
+++ 1.34/mysql-test/r/ps.result	2005-03-03 16:28:21 +03:00
@@ -499,3 +499,28 @@
 FOUND_ROWS()
 2
 deallocate prepare stmt;
+create table t1 (a char(3) not null, b char(3) not null,
+c char(3) not null, primary key  (a, b, c));
+create table t2 like t1;
+prepare stmt from
+"select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b)
+  where t1.a=1";
+execute stmt;
+a
+execute stmt;
+a
+execute stmt;
+a
+prepare stmt from
+"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from
+(t1 left outer join t2 on t2.a=? and t1.b=t2.b)
+left outer join t2 t3 on t3.a=? where t1.a=?";
+set @a:=1, @b:=1, @c:=1;
+execute stmt using @a, @b, @c;
+a	b	c	a	b	c
+execute stmt using @a, @b, @c;
+a	b	c	a	b	c
+execute stmt using @a, @b, @c;
+a	b	c	a	b	c
+deallocate prepare stmt;
+drop table t1,t2;

--- 1.32/mysql-test/t/ps.test	2005-03-03 13:29:32 +03:00
+++ 1.33/mysql-test/t/ps.test	2005-03-03 16:28:21 +03:00
@@ -507,3 +507,36 @@
 execute stmt;                                                                   
 SELECT FOUND_ROWS();                                                            
 deallocate prepare stmt;
+
+#
+# Bug#8115: equality propagation and prepared statements
+#
+
+create table t1 (a char(3) not null, b char(3) not null,
+                 c char(3) not null, primary key  (a, b, c));
+create table t2 like t1;
+
+# reduced query
+prepare stmt from
+  "select t1.a from (t1 left outer join t2 on t2.a=1 and t1.b=t2.b)
+  where t1.a=1";
+execute stmt;
+execute stmt;
+execute stmt;
+
+# original query
+prepare stmt from
+"select t1.a, t1.b, t1.c, t2.a, t2.b, t2.c from
+(t1 left outer join t2 on t2.a=? and t1.b=t2.b)
+left outer join t2 t3 on t3.a=? where t1.a=?";
+
+set @a:=1, @b:=1, @c:=1;
+
+execute stmt using @a, @b, @c;
+execute stmt using @a, @b, @c;
+execute stmt using @a, @b, @c;
+
+deallocate prepare stmt;
+
+drop table t1,t2;
+

--- 1.101/sql/sql_prepare.cc	2005-02-25 17:53:16 +03:00
+++ 1.102/sql/sql_prepare.cc	2005-03-03 16:28:22 +03:00
@@ -1809,20 +1809,33 @@
   else
   {
     stmt->setup_set_params();
-    SELECT_LEX *sl= stmt->lex->all_selects_list;
-    /*
-      Save WHERE clause pointers, because they may be changed during query
-      optimisation.
-    */
-    for (; sl; sl= sl->next_select_in_list())
-      sl->prep_where= sl->where;
+    init_stmt_after_parse(thd, stmt->lex);
     stmt->state= Item_arena::PREPARED;
   }
   DBUG_RETURN(!stmt);
 }
 
 
-/* Reinit statement before execution */
+/*
+  Init PS/SP specific parse tree members.
+*/
+
+void init_stmt_after_parse(THD *thd, LEX *lex)
+{
+  SELECT_LEX *sl= lex->all_selects_list;
+  /*
+    Save WHERE clause pointers, because they may be changed during query
+    optimisation.
+  */
+  for (; sl; sl= sl->next_select_in_list())
+    sl->prep_where= sl->where;
+
+  for (TABLE_LIST *table= lex->query_tables; table; table= table->next_global)
+    table->prep_on_expr= table->on_expr;
+}
+
+
+/* Reinit prepared statement/stored procedure before execution */
 
 void reset_stmt_for_execute(THD *thd, LEX *lex)
 {
@@ -1883,6 +1896,12 @@
     tables->table= 0;
     if (tables->nested_join)
       tables->nested_join->counter= 0;
+
+    if (tables->prep_on_expr)
+    {
+      tables->on_expr= tables->prep_on_expr->copy_andor_structure(thd);
+      tables->on_expr->cleanup();
+    }
   }
   lex->current_select= &lex->select_lex;
 
Thread
bk commit into 5.0 tree (konstantin:1.1786) BUG#8849konstantin3 Mar