| 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: | |
| 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 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.
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.