MySQL Bugs: #32260: User variables in query cause server crash

Bug #32260	User variables in query cause server crash
Submitted:	11 Nov 2007 3:26	Modified:	30 Nov 2007 1:23
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.23-BK	OS:	Any
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
The following script causes a server crash in 5.1.23 and 6.0.4. It works in 5.0.52.

# Assign ranks to the values in a table

# First, create the table

DROP TABLE IF EXISTS t;
CREATE TABLE t (score INT);
INSERT INTO t (score) VALUES(5),(4),(4),(3),(2),(2),(2),(1);
SELECT * FROM t;

# Assign ranks using position (row number) within the set of values,
# except that tied values all get the rank accorded the first of them.

SET @rownum := 0;
SET @rank := 0;
SET @prev_score := NULL;
SELECT
  @rownum := @rownum + 1 AS row
  ,@rank := IF(@prev_score!=score,@rownum,@rank) AS rank
  ,@prev_score := score AS score
FROM t ORDER BY score DESC;

How to repeat:
Run the script.

In 5.0.52, it produces this output:

+-------+
| score |
+-------+
|     5 | 
|     4 | 
|     4 | 
|     3 | 
|     2 | 
|     2 | 
|     2 | 
|     1 | 
+-------+
+------+------+-------+
| row  | rank | score |
+------+------+-------+
|    1 |    1 |     5 | 
|    2 |    2 |     4 | 
|    3 |    2 |     4 | 
|    4 |    4 |     3 | 
|    5 |    5 |     2 | 
|    6 |    5 |     2 | 
|    7 |    5 |     2 | 
|    8 |    8 |     1 | 
+------+------+-------+

In 5.1.23 and 6.0.4 the script produces this output:

+-------+
| score |
+-------+
|     5 | 
|     4 | 
|     4 | 
|     3 | 
|     2 | 
|     2 | 
|     2 | 
|     1 | 
+-------+
ERROR 2013 (HY000) at line 16: Lost connection to MySQL server during query

The final SELECT causes the problem. The narrow the problem a bit, if you don't select the third column (@prev_score := score AS score) the crash does not occur.

One might say that the results from using user variables this way are not guaranteed, but even if that is true, the server should not crash.

Thank you for a bug report. Verified just as described.

Resolved stack trace:

openxs@linux:~/dbs/5.1> bin/resolve_stack_dump -s /tmp/mysqld51.sym -n 32260.st
ack
0x8215d2b handle_segfault + 683
0x818654e _ZN22Item_func_set_user_var26register_field_in_read_mapEPh + 14
0x818446b _ZN9Item_func4walkEM4ItemFbPhEbS1_ + 155
0x82fa430 _Z8filesortP3THDP8st_tableP13st_sort_fieldjP10SQL_SELECTmbPm + 4160
0x827db1e _Z17create_sort_indexP3THDP4JOINP8st_ordermmb + 318
0x828e9c6 _ZN4JOIN4execEv + 4198
0x828abf8 _Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB
_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select + 184
0x828fa0e _Z13handle_selectP3THDP6st_lexP13select_resultm + 382
0x8222eeb _Z21execute_sqlcom_selectP3THDP10TABLE_LIST + 859
0x8229528 _Z21mysql_execute_commandP3THD + 22008
0x822cbda _Z11mysql_parseP3THDPKcjPS2_ + 586
0x822daa9 _Z16dispatch_command19enum_server_commandP3THDPcj + 3609
0x822e45b _Z10do_commandP3THD + 171
0x821e090 handle_one_connection + 528
0x40032aa7 _end + 931720083
0x401e1c2e _end + 933485850

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

ChangeSet@1.2619, 2007-11-16 09:22:21+04:00, ramil@mysql.com +3 -0
  Fix for bug #32260: User variables in query cause server crash
  
  Problem: there's no guarantee that the user variable item's result_field
  is assigned when we're adjusting its table read map.
  
  Fix: Check the result_field before using it.

Bug #32376 was marked as a duplicate of this one.

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

ChangeSet@1.2620, 2007-11-17 11:20:50+04:00, ramil@mysql.com +3 -0
  Fix for bug #32260: User variables in query cause server crash
    
  Problem: there's no guarantee that the user variable item's result_field
  is assigned when we're adjusting its table read map.
    
  Fix: check the result_field before using it.

Pushed into 6.0.4-alpha

Pushed into 5.1.23-rc

Noted in 5.1.23, 6.0.4 changelogs.

Some uses of user variables in a query could result in a server crash.