MySQL Bugs: #22085: Crash on the execution of a prepared statement using aggregating IN subquery

Bug #22085	Crash on the execution of a prepared statement using aggregating IN subquery
Submitted:	7 Sep 2006 17:21	Modified:	4 Oct 2006 19:15
Reporter:	Igor Babaev	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Prepared statements	Severity:	S1 (Critical)
Version:	4.1.22-BK	OS:
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
An execution of a prepared statement that uses an IN subquery with aggregate functions in the having clause may cause a server crash.

This bug happens by similar reasons that bug #21493 for the code 5.0 line.
Currently IN subqueries are transformed into EXIST subqueries.
If a subquery is aggregating this transformation results in
modification of the HAVING clause. This transformation is valid for
any execution of the prepared statement and is performed on
the statement memory. At the same time the presence of aggregate functions
in the HAVING clause triggers a modification of it, but by some reasons,
it's done on the execution memory. The current 4.1 code performs this
modification before the subquery transformation.
As a result we have object created on the execution memory that refers to 
objects on the statement memory. 
 
 

How to repeat:
mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 4.1.22-debug |
+--------------+
1 row in set (0.00 sec)

mysql> 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)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> 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' );
Query OK, 15 rows affected (0.00 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> 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')";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> SET @id='111111';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE STMT USING @id,@id;
ERROR 2013 (HY000): Lost connection to MySQL server during query

========================================================

home/igor/mysql-4.1-opt/sql/mysqld: ready for connections.
Version: '4.1.22-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1273943136 (LWP 28052)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -1273943136 (LWP 28052)]
0x0812bdd0 in Item_cond::copy_andor_arguments (this=0x8ceee18, thd=0x8cce098, item=0x8cfc828) at item_cmpfunc.cc:2179

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12083

ChangeSet@1.2566, 2006-09-16 09:06:59-07:00, igor@rurik.mysql.com +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.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12087

ChangeSet@1.2566, 2006-09-16 11:50:00-07:00, igor@rurik.mysql.com +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.

Pushed in 4.1.22/5.0.26/5.1.12

Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.