bk commit into 5.0 tree (igor:1.2251) BUG#21493

[igor]

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor 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-09-16 07:10:18-07:00, igor@stripped +8 -0
  Fixed bug #21493: crash for the second execution of a function
  containing a select statement that uses IN subquery with GROUP BY.
  Added a parameter to the function fix_prepare_information 
  to restore correctly the having clause for the second execution.
  Saved andor structure of the having conditions at the proper moment
  before any calls of split_sum_func2 that could modify the having structure
  adding new Item_ref objects. (These additions, are produced not with 
  the statement mem_root, but rather with the execution mem_root.)

  mysql-test/r/sp.result@stripped, 2006-09-16 07:10:13-07:00, igor@stripped +63 -0
    Added a test case for bug #21493.

  mysql-test/t/sp.test@stripped, 2006-09-16 07:10:13-07:00, igor@stripped +67 -0
    Added a test case for bug #21493.

  sql/sql_delete.cc@stripped, 2006-09-16 07:10:13-07:00, igor@stripped +2 -1
    Fixed bug #21493: crash for the second execution of a function
    containing a select statement that uses an IN aggregating subquery.
    Added a parameter to the function fix_prepare_information 
    to restore correctly the having clause for the second execution.

  sql/sql_insert.cc@stripped, 2006-09-16 07:10:14-07:00, igor@stripped +1 -1
    Fixed bug #21493: crash for the second execution of a function
    containing a select statement that uses an IN aggregating subquery.
    Added a parameter to the function fix_prepare_information 
    to restore correctly the having clause for the second execution.

  sql/sql_lex.cc@stripped, 2006-09-16 07:10:14-07:00, igor@stripped +19 -4
    Fixed bug #21493: crash for the second execution of a function
    containing a select statement that uses an IN aggregating subquery.
    Added a parameter to the function fix_prepare_information 
    to restore correctly the having clause for the second execution.

  sql/sql_lex.h@stripped, 2006-09-16 07:10:14-07:00, igor@stripped +1 -1
    Fixed bug #21493: crash for the second execution of a function
    containing a select statement that uses an IN aggregating subquery.
    Added a parameter to the function fix_prepare_information 
    to restore correctly the having clause for the second execution.

  sql/sql_select.cc@stripped, 2006-09-16 07:10:14-07:00, igor@stripped +3 -3
    Fixed bug #21493: crash for the second execution of a function
    containing a select statement that uses .
    Added a parameter to the function fix_prepare_information 
    to restore correctly the having clause for the second execution.
    Saved andor structure of the having conditions at the proper moment
    before any calls of split_sum_func2 that could modify the having structure
    adding new Item_ref objects. (These additions, are produced not with 
    the statement mem_root, but rather with the execution mem_root.)

  sql/sql_update.cc@stripped, 2006-09-16 07:10:14-07:00, igor@stripped +2 -1
    Fixed bug #21493: crash for the second execution of a function
    containing a select statement that uses an IN aggregating subquery.
    Added a parameter to the function fix_prepare_information 
    to restore correctly the having clause for the second execution.

# 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:	igor
# Host:	rurik.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug21493

--- 1.177/sql/sql_delete.cc	2006-09-16 07:10:28 -07:00
+++ 1.178/sql/sql_delete.cc	2006-09-16 07:10:28 -07:00
@@ -342,6 +342,7 @@
 */
 bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
 {
+  Item *fake_conds= 0;
   SELECT_LEX *select_lex= &thd->lex->select_lex;
   DBUG_ENTER("mysql_prepare_delete");
 
@@ -367,7 +368,7 @@
       DBUG_RETURN(TRUE);
     }
   }
-  select_lex->fix_prepare_information(thd, conds);
+  select_lex->fix_prepare_information(thd, conds, &fake_conds);
   DBUG_RETURN(FALSE);
 }
 

--- 1.198/sql/sql_insert.cc	2006-09-16 07:10:28 -07:00
+++ 1.199/sql/sql_insert.cc	2006-09-16 07:10:28 -07:00
@@ -993,7 +993,7 @@
       update_non_unique_table_error(table_list, "INSERT", duplicate);
       DBUG_RETURN(TRUE);
     }
-    select_lex->fix_prepare_information(thd, &fake_conds);
+    select_lex->fix_prepare_information(thd, &fake_conds, &fake_conds);
     select_lex->first_execution= 0;
   }
   if (duplic == DUP_UPDATE || duplic == DUP_REPLACE)

--- 1.195/sql/sql_lex.cc	2006-09-16 07:10:28 -07:00
+++ 1.196/sql/sql_lex.cc	2006-09-16 07:10:28 -07:00
@@ -2155,15 +2155,25 @@
 
 
 /*
-  fix some structures at the end of preparation
+  Save WHERE/HAVING/ON clauses and replace them with disposable copies
 
   SYNOPSIS
     st_select_lex::fix_prepare_information
-    thd   thread handler
-    conds pointer on conditions which will be used for execution statement
+      thd          thread handler
+      conds        in/out pointer to WHERE condition to be met at execution
+      having_conds in/out pointer to HAVING condition to be met at execution
+  
+  DESCRIPTION
+    The passed WHERE and HAVING are to be saved for the future executions.
+    This function saves it, and returns a copy which can be thrashed during
+    this execution of the statement. By saving/thrashing here we mean only
+    AND/OR trees.
+    The function also calls fix_prepare_info_in_table_list that saves all
+    ON expressions.    
 */
 
-void st_select_lex::fix_prepare_information(THD *thd, Item **conds)
+void st_select_lex::fix_prepare_information(THD *thd, Item **conds, 
+                                            Item **having_conds)
 {
   if (!thd->stmt_arena->is_conventional() && first_execution)
   {
@@ -2172,6 +2182,11 @@
     {
       prep_where= *conds;
       *conds= where= prep_where->copy_andor_structure(thd);
+    }
+    if (*having_conds)
+    {
+      prep_having= *having_conds;
+      *having_conds= having= prep_having->copy_andor_structure(thd);
     }
     fix_prepare_info_in_table_list(thd, (TABLE_LIST *)table_list.first);
   }

--- 1.225/sql/sql_lex.h	2006-09-16 07:10:28 -07:00
+++ 1.226/sql/sql_lex.h	2006-09-16 07:10:28 -07:00
@@ -650,7 +650,7 @@
   void print(THD *thd, String *str);
   static void print_order(String *str, ORDER *order);
   void print_limit(THD *thd, String *str);
-  void fix_prepare_information(THD *thd, Item **conds);
+  void fix_prepare_information(THD *thd, Item **conds, Item **having_conds);
   /*
     Destroy the used execution plan (JOIN) of this subtree (this
     SELECT_LEX and all nested SELECT_LEXes and SELECT_LEX_UNITs).

--- 1.439/sql/sql_select.cc	2006-09-16 07:10:28 -07:00
+++ 1.440/sql/sql_select.cc	2006-09-16 07:10:28 -07:00
@@ -382,12 +382,14 @@
       if ((res= subselect->select_transformer(this)) !=
 	  Item_subselect::RES_OK)
       {
-        select_lex->fix_prepare_information(thd, &conds);
+        select_lex->fix_prepare_information(thd, &conds, &having);
 	DBUG_RETURN((res == Item_subselect::RES_ERROR));
       }
     }
   }
 
+  select_lex->fix_prepare_information(thd, &conds, &having);
+
   if (having && having->with_sum_func)
     having->split_sum_func2(thd, ref_pointer_array, all_fields,
                             &having, TRUE);
@@ -499,7 +501,6 @@
   if (alloc_func_list())
     goto err;
 
-  select_lex->fix_prepare_information(thd, &conds);
   DBUG_RETURN(0); // All OK
 
 err:
@@ -618,7 +619,6 @@
     build_bitmap_for_nested_joins(join_list, 0);
 
     sel->prep_where= conds ? conds->copy_andor_structure(thd) : 0;
-    sel->prep_having= having ? having->copy_andor_structure(thd) : 0;
 
     if (arena)
       thd->restore_active_arena(arena, &backup);

--- 1.195/sql/sql_update.cc	2006-09-16 07:10:28 -07:00
+++ 1.196/sql/sql_update.cc	2006-09-16 07:10:28 -07:00
@@ -606,6 +606,7 @@
 bool mysql_prepare_update(THD *thd, TABLE_LIST *table_list,
 			 Item **conds, uint order_num, ORDER *order)
 {
+  Item *fake_conds= 0;
   TABLE *table= table_list->table;
   TABLE_LIST tables;
   List<Item> all_fields;
@@ -645,7 +646,7 @@
       DBUG_RETURN(TRUE);
     }
   }
-  select_lex->fix_prepare_information(thd, conds);
+  select_lex->fix_prepare_information(thd, conds, &fake_conds);
   DBUG_RETURN(FALSE);
 }
 

--- 1.206/mysql-test/r/sp.result	2006-09-16 07:10:28 -07:00
+++ 1.207/mysql-test/r/sp.result	2006-09-16 07:10:28 -07:00
@@ -5256,4 +5256,67 @@
 1
 use test|
 drop table t3|
+CREATE TABLE t3 (
+Member_ID varchar(15) NOT NULL,
+PRIMARY KEY (Member_ID)
+)|
+CREATE TABLE t4 (
+ID int(10) unsigned NOT NULL auto_increment,
+Member_ID varchar(15) NOT NULL default '',
+Action varchar(12) NOT NULL,
+Action_Date datetime NOT NULL,
+Track varchar(15) default NULL,
+User varchar(12) default NULL,
+Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update
+CURRENT_TIMESTAMP,
+PRIMARY KEY (ID),
+KEY Action (Action),
+KEY Action_Date (Action_Date)
+)|
+INSERT INTO t3(Member_ID) VALUES
+('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')|
+INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES
+('111111', 'Disenrolled', '2006-03-01', 'CAD' ),
+('111111', 'Enrolled', '2006-03-01', 'CAD' ),
+('111111', 'Disenrolled', '2006-07-03', 'CAD' ),
+('222222', 'Enrolled', '2006-03-07', 'CAD' ),
+('222222', 'Enrolled', '2006-03-07', 'CHF' ),
+('222222', 'Disenrolled', '2006-08-02', 'CHF' ),
+('333333', 'Enrolled', '2006-03-01', 'CAD' ),
+('333333', 'Disenrolled', '2006-03-01', 'CAD' ),
+('444444', 'Enrolled', '2006-03-01', 'CAD' ),
+('555555', 'Disenrolled', '2006-03-01', 'CAD' ),
+('555555', 'Enrolled', '2006-07-21', 'CAD' ),
+('555555', 'Disenrolled', '2006-03-01', 'CHF' ),
+('666666', 'Enrolled', '2006-02-09', 'CAD' ),
+('666666', 'Enrolled', '2006-05-12', 'CHF' ),
+('666666', 'Disenrolled', '2006-06-01', 'CAD' )|
+DROP FUNCTION IF EXISTS bug21493|
+Warnings:
+Note	1305	FUNCTION bug21493 does not exist
+CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45)
+BEGIN
+DECLARE tracks VARCHAR(45);
+SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4
+WHERE Member_ID=paramMember AND Action='Enrolled' AND 
+(Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4
+WHERE Member_ID=paramMember GROUP BY Track);
+RETURN tracks;
+END|
+SELECT bug21493('111111')|
+bug21493('111111')
+NULL
+SELECT bug21493('222222')|
+bug21493('222222')
+CAD
+SELECT bug21493(Member_ID) FROM t3|
+bug21493(Member_ID)
+NULL
+CAD
+CAD
+CAD
+CAD
+CHF
+DROP FUNCTION bug21493|
+DROP TABLE t3,t4|
 drop table t1,t2;

--- 1.194/mysql-test/t/sp.test	2006-09-16 07:10:28 -07:00
+++ 1.195/mysql-test/t/sp.test	2006-09-16 07:10:28 -07:00
@@ -6189,6 +6189,73 @@
 drop table t3|
 
 #
+# BUG#21493: Crash on the second call of a procedure containing
+#            a select statement that uses an IN aggregating subquery  
+#
+
+CREATE TABLE t3 (
+  Member_ID varchar(15) NOT NULL,
+  PRIMARY KEY (Member_ID)
+)|
+
+CREATE TABLE t4 (
+  ID int(10) unsigned NOT NULL auto_increment,
+  Member_ID varchar(15) NOT NULL default '',
+  Action varchar(12) NOT NULL,
+  Action_Date datetime NOT NULL,
+  Track varchar(15) default NULL,
+  User varchar(12) default NULL,
+  Date_Updated timestamp NOT NULL default CURRENT_TIMESTAMP on update
+    CURRENT_TIMESTAMP,
+  PRIMARY KEY (ID),
+  KEY Action (Action),
+  KEY Action_Date (Action_Date)
+)|
+
+
+INSERT INTO t3(Member_ID) VALUES
+  ('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666')|
+
+INSERT INTO t4(Member_ID, Action, Action_Date, Track) VALUES
+  ('111111', 'Disenrolled', '2006-03-01', 'CAD' ),
+  ('111111', 'Enrolled', '2006-03-01', 'CAD' ),
+  ('111111', 'Disenrolled', '2006-07-03', 'CAD' ),
+  ('222222', 'Enrolled', '2006-03-07', 'CAD' ),
+  ('222222', 'Enrolled', '2006-03-07', 'CHF' ),
+  ('222222', 'Disenrolled', '2006-08-02', 'CHF' ),
+  ('333333', 'Enrolled', '2006-03-01', 'CAD' ),
+  ('333333', 'Disenrolled', '2006-03-01', 'CAD' ),
+  ('444444', 'Enrolled', '2006-03-01', 'CAD' ),
+  ('555555', 'Disenrolled', '2006-03-01', 'CAD' ),
+  ('555555', 'Enrolled', '2006-07-21', 'CAD' ),
+  ('555555', 'Disenrolled', '2006-03-01', 'CHF' ),
+  ('666666', 'Enrolled', '2006-02-09', 'CAD' ),
+  ('666666', 'Enrolled', '2006-05-12', 'CHF' ),
+  ('666666', 'Disenrolled', '2006-06-01', 'CAD' )|
+
+#--disable_warnings
+DROP FUNCTION IF EXISTS bug21493|
+#--enable_warnings
+
+CREATE FUNCTION bug21493(paramMember VARCHAR(15)) RETURNS varchar(45)
+BEGIN
+DECLARE tracks VARCHAR(45);
+SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM t4
+  WHERE Member_ID=paramMember AND Action='Enrolled' AND 
+        (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t4
+                                  WHERE Member_ID=paramMember GROUP BY Track);
+RETURN tracks;
+END|
+
+SELECT bug21493('111111')|
+SELECT bug21493('222222')|
+
+SELECT bug21493(Member_ID) FROM t3|
+
+DROP FUNCTION bug21493|
+DROP TABLE t3,t4|
+
+#
 # BUG#NNNN: New bug synopsis
 #
 #--disable_warnings