MySQL Bugs: #27937: stored procedure + union + rand = crashed server

Bug #27937	stored procedure + union + rand = crashed server
Submitted:	18 Apr 2007 21:07	Modified:	7 Jun 2007 16:47
Reporter:	Scott Noyes (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0 - 5.1.17, 4.1	OS:	Any
Assigned to:	Igor Babaev	CPU Architecture:	Any
Tags:	crash, qc, stored procedure, UNION

Description:
A stored procedure using UNION and ORDER BY rand() causes the server to crash. Tested in several 5.0 and 5.1 versions, including 5.0.37 and 5.1.17

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

DELIMITER //
CREATE PROCEDURE myProc() BEGIN (SELECT 1) UNION (SELECT 2) UNION (SELECT 3) ORDER BY rand(); END//
DELIMITER ;

CALL myProc();
CALL myProc();
CALL myProc();

First call is ok:
+---+
| 1 |
+---+
| 2 | 
| 3 | 
| 1 | 
+---+
3 rows in set (0.00 sec)

Second call:
ERROR 2013 (HY000): Lost connection to MySQL server during query

Third call:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost' (10061)
ERROR: 
Can't connect to the server

Note that the same SELECT statement outside of a stored procedure does not crash, nor does the stored procedure crash if the statement inside is any of these:

SELECT 1 ORDER BY rand();
(SELECT 1) UNION (SELECT 2);
(SELECT 1) UNION (SELECT 2) ORDER BY 1;

Thanks for this bug report.
Verified as described on 5.0.42 and 5.1.18 on Linux and Mac OS X.

mysqld-debug.exe!st_select_lex_unit::init_prepare_fake_select_lex
mysqld-debug.exe!st_select_lex_unit::exec
mysqld-debug.exe!mysql_union
mysqld-debug.exe!handle_select
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!sp_instr_stmt::exec_core
mysqld-debug.exe!sp_lex_keeper::reset_lex_and_exec_core
mysqld-debug.exe!sp_instr_stmt::execute
mysqld-debug.exe!sp_head::execute
mysqld-debug.exe!sp_head::execute_procedure
mysqld-debug.exe!mysql_execute_command
mysqld-debug.exe!mysql_parse
mysqld-debug.exe!dispatch_command
mysqld-debug.exe!do_command
mysqld-debug.exe!handle_one_connection
mysqld-debug.exe!pthread_start
mysqld-debug.exe!_callthreadstart
mysqld-debug.exe!_threadstart

This problem can be demonstrated for 4.1 with a prepared statement:

mysql> SELECT VERSION();
+--------------+
| VERSION()    |
+--------------+
| 4.1.23-debug |
+--------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE t1(a int);
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO t1 VALUES (2), (3), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> PREPARE st1 FROM
    -> '(SELECT a FROM t1) UNION (SELECT a+10 FROM t1) ORDER BY RAND()';
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE st1;
+------+
| a    |
+------+
|    1 |
|   11 |
|   13 |
|    2 |
|    3 |
|   12 |
+------+
6 rows in set (0.00 sec)

mysql> EXECUTE st1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

On the server side we have:
Version: '4.1.23-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread -1273451616 (LWP 12291)]

Program received signal SIGSEGV, Segmentation fault.

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/26556

ChangeSet@1.2655, 2007-05-12 22:58:23-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #27937: crash for the the second execution of a prepared
  statement from a UNION query with ORDER BY an expression containing
  RAND().
  The crash happened because the global order by list in the union query
  was not re-initialized for the second execution.
  (Local order by lists were re-initialized though).

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/26650

ChangeSet@1.2655, 2007-05-14 16:41:09-07:00, igor@olga.mysql.com +3 -0
  Fixed bug #27937: crash for the the second execution of a prepared
  statement from a UNION query with ORDER BY an expression containing
  RAND().
  The crash happened because the global order by list in the union query
  was not re-initialized for execution.
  (Local order by lists were re-initialized though).

Pushed into 4.1.23

Pushed into 5.0.44

Pushed into 5.1.19-beta

Thank you for your bug report. This issue has already been fixed in the latest released version of that product, which you can download at

  http://www.mysql.com/downloads/

Documented bugfix in 4.1.23, 5.0.44, and 5.1.19 changelogs.