bk commit into 4.1 tree (igor:1.2566) BUG#22085

[igor]

Below is the list of changes that have just been committed into a local
4.1 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 09:06:59-07:00, igor@stripped +3 -0
  Fixed bug #22085: Crash on the execution of a prepared 
  statement that uses an aggregating IN subquery with 
  HAVING clause.
  A wrong order of the call of split_sum_func2 for the HAVING
  clause of the subquery and the transformation for the 
  subquery resulted in the creation of a andor structure
  that could not be restored at an execution of the prepared
  statement.

  mysql-test/r/ps.result@stripped, 2006-09-16 09:06:53-07:00, igor@stripped +47 -0
    Added a test cases for bug #22085.

  mysql-test/t/ps.test@stripped, 2006-09-16 09:06:53-07:00, igor@stripped +52 -0
    Added a test cases for bug #22085.

  sql/sql_select.cc@stripped, 2006-09-16 09:06:53-07:00, igor@stripped +6 -2
    Fixed bug #22085: Crash on the execution of a prepared 
    statement that uses an aggregating IN subquery with 
    HAVING clause.
    A wrong order of the call of split_sum_func2 for the HAVING
    clause of the subquery and the transformation for the 
    subquery resulted in the creation of a andor structure
    that could not be restored at an execution of the prepared
    statement.
    

# 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/mysql-4.1-opt

--- 1.457/sql/sql_select.cc	2006-09-16 09:07:06 -07:00
+++ 1.458/sql/sql_select.cc	2006-09-16 09:07:06 -07:00
@@ -290,8 +290,6 @@
     select_lex->having_fix_field= 0;
     if (having_fix_rc || thd->net.report_error)
       DBUG_RETURN(-1);				/* purecov: inspected */
-    if (having->with_sum_func)
-      having->split_sum_func2(thd, ref_pointer_array, all_fields, &having);
   }
 
   // Is it subselect
@@ -304,6 +302,12 @@
 	  Item_subselect::RES_OK)
 	DBUG_RETURN((res == Item_subselect::RES_ERROR));
     }
+  }
+
+  if (having)
+  {
+    if (having->with_sum_func)
+      having->split_sum_func2(thd, ref_pointer_array, all_fields, &having);
   }
 
   if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */

--- 1.50/mysql-test/r/ps.result	2006-09-16 09:07:06 -07:00
+++ 1.51/mysql-test/r/ps.result	2006-09-16 09:07:06 -07:00
@@ -889,3 +889,50 @@
 execute stmt;
 drop temporary table t1;
 deallocate prepare stmt;
+CREATE TABLE t1(
+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 t1(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' );
+PREPARE STMT FROM
+"SELECT GROUP_CONCAT(Track SEPARATOR ', ') FROM t1
+  WHERE Member_ID=? AND Action='Enrolled' AND
+        (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t1
+                                  WHERE Member_ID=?
+                                    GROUP BY Track 
+                                      HAVING Track>='CAD' AND
+                                             MAX(Action_Date)>'2006-03-01')";
+SET @id='111111';
+EXECUTE STMT USING @id,@id;
+GROUP_CONCAT(Track SEPARATOR ', ')
+NULL
+SET @id='222222';
+EXECUTE STMT USING @id,@id;
+GROUP_CONCAT(Track SEPARATOR ', ')
+CAD
+DEALLOCATE PREPARE STMT;
+DROP TABLE t1;

--- 1.51/mysql-test/t/ps.test	2006-09-16 09:07:06 -07:00
+++ 1.52/mysql-test/t/ps.test	2006-09-16 09:07:06 -07:00
@@ -951,4 +951,56 @@
 drop temporary table t1;
 deallocate prepare stmt;
 
+#
+# BUG#22085: Crash on the execution of a prepared statement that
+#            uses an IN subquery with aggregate functions in HAVING 
+#
+
+CREATE TABLE t1(
+  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 t1(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' );
+
+PREPARE STMT FROM
+"SELECT GROUP_CONCAT(Track SEPARATOR ', ') FROM t1
+  WHERE Member_ID=? AND Action='Enrolled' AND
+        (Track,Action_Date) IN (SELECT Track, MAX(Action_Date) FROM t1
+                                  WHERE Member_ID=?
+                                    GROUP BY Track 
+                                      HAVING Track>='CAD' AND
+                                             MAX(Action_Date)>'2006-03-01')";
+SET @id='111111';
+EXECUTE STMT USING @id,@id;
+SET @id='222222';
+EXECUTE STMT USING @id,@id;
+
+DEALLOCATE PREPARE STMT;
+DROP TABLE t1;
+
 # End of 4.1 tests