Bug #21493 MySQL aborts on calling function with ROW IN subquery for multiple rows
Submitted: 7 Aug 2006 21:33 Modified: 21 Sep 2006 1:56
Reporter: Andrew Arnold Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.21/5.0BK/5.1BK OS:Windows (WIndows 2000, Windows XP Pro/Suse Linux)
Assigned to: Igor Babaev CPU Architecture:Any
Tags: crash, row in, subquery

[7 Aug 2006 21:33] Andrew Arnold
Description:
Windows 2000 
MySQL 5.0.19

Windows XP Pro
MySQL 5.0.21 and 5.0.24

When calling a function that contains a row..in subquery from a SELECT query, MySQL dies if the query returns more than one row.

How to repeat:
# Create 2 tables
CREATE TABLE  `cardiac` (
  `Member_ID` varchar(15) NOT NULL,
  PRIMARY KEY  (`Member_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `enrollment` (
  `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 `Status` (`Member_ID`,`Program`,`Action`,`Action_Date`),
  KEY `Action` (`Action`),
  KEY `Action_Date` (`Action_Date`),
  KEY `program` (`Program`),
  KEY `Unique` (`Member_ID`,`Program`,`Track`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

# Populate tables with data
INSERT INTO cardiac (Member_ID)
VALUES ('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666');

INSERT INTO enrollment (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' );

# Create function
DELIMITER $$

DROP FUNCTION IF EXISTS `getTracks`$$
CREATE FUNCTION `getTracks`(paramMember VARCHAR(15)) RETURNS varchar(45)
BEGIN
  DECLARE tracks VARCHAR(45);
  SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM enrollment
  WHERE Member_ID=paramMember And Action='Enrolled'
  And (Track,Action_Date) In
  (SELECT Track, max(Action_Date) FROM enrollment
  WHERE Member_ID=paramMember
  GROUP BY Track);
  RETURN tracks;
END $$

DELIMITER ;

# Server dies
SELECT getTracks(Member_ID) FROM cardiac;

--------------------------------------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.19-community

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use sandbox
Database changed
mysql> select getTracks(Member_ID) FROM cardiac;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
--------------------------------------------------------

# Server does not die for only one row
select getTracks(Member_ID) FROM cardiac WHERE Member_ID='111111';

# Server dies
SELECT getTracks(Member_ID) FROM cardiac WHERE Member_ID In ('111111','222222');

The presence or absence of the GROUP_CONCAT() function in the function does not make a difference.
[7 Aug 2006 22:36] MySQL Verification Team
Back trace on Suse Linux 10 32-bits for 5.0BK

Attachment: bt-21493.txt (text/plain), 13.94 KiB.

[7 Aug 2006 22:44] MySQL Verification Team
Thank you for the bug report. I was able to repeat with current source server
on Suse Linux 10 32-bits with 5.0/5.1 tree. I changed the second table definition removing keys envolving the missed column 'program'. Back trace
attached for version 5.0.

miguel@hegel:~/dbs/5.1> bin/mysqladmin -uroot create dbq
miguel@hegel:~/dbs/5.1> bin/mysql -uroot dbq
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.1.12-beta-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE  `cardiac` (
    ->   `Member_ID` varchar(15) NOT NULL,
    ->   PRIMARY KEY  (`Member_ID`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE  `enrollment` (
    ->   `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`)
    ->  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO cardiac (Member_ID)
    -> VALUES ('111111'), ('222222'), ('333333'), ('444444'), ('555555'), ('666666');
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> 
mysql> INSERT INTO enrollment (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.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> 
mysql> # Create function
mysql> DELIMITER $$
mysql> 
mysql> DROP FUNCTION IF EXISTS `getTracks`$$
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> CREATE FUNCTION `getTracks`(paramMember VARCHAR(15)) RETURNS varchar(45)
    -> BEGIN
    ->   DECLARE tracks VARCHAR(45);
    ->   SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM enrollment
    ->   WHERE Member_ID=paramMember And Action='Enrolled'
    ->   And (Track,Action_Date) In
    ->   (SELECT Track, max(Action_Date) FROM enrollment
    ->   WHERE Member_ID=paramMember
    ->   GROUP BY Track);
    ->   RETURN tracks;
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> DELIMITER ;
mysql> SELECT getTracks(Member_ID) FROM cardiac;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql>
[8 Aug 2006 13:43] Andrew Arnold
I meant to submit the bug as S2, not S1. There is a simple work-around, which is to concatenate the columns used in the row in subquery:
  SELECT GROUP_CONCAT(Track SEPARATOR ', ') INTO tracks FROM enrollment
  WHERE Member_ID=paramMember And Action='Enrolled'
  And CONCAT(Track,Action_Date) In
  (SELECT CONCAT(Track, max(Action_Date)) FROM enrollment
  WHERE Member_ID=paramMember
  GROUP BY Track);
[7 Sep 2006 16:45] Bugs System
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/11562

ChangeSet@1.2251, 2006-09-07 09:45:05-07:00, igor@rurik.mysql.com +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, by some reasons (?!), are
  still produced not with the statement mem_root, but rather with the 
  execution mem_root.)
[16 Sep 2006 14:10] Bugs System
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/12080

ChangeSet@1.2251, 2006-09-16 07:10:18-07:00, igor@rurik.mysql.com +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.)
[16 Sep 2006 16:51] Bugs System
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/12084

ChangeSet@1.2263, 2006-09-16 09:50:48-07:00, igor@rurik.mysql.com +8 -0
  Fixed bug #21493: crash for the second execution of a function
  containing a select statement that uses an aggregating IN subquery.
  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.)
[19 Sep 2006 8:32] Georgi Kodinov
Pushed into 5.0.26/5.1.12-beta
[21 Sep 2006 1:56] Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs.