MySQL Bugs: #47421: Server crash from - SELECT SUM ( DISTINCT <char

Bug #47421	Server crash from - SELECT SUM ( DISTINCT <char_column>) in azalea
Submitted:	17 Sep 2009 21:07	Modified:	12 Nov 2009 19:55
Reporter:	Patrick Crews	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Logging	Severity:	S3 (Non-critical)
Version:	5.4/6.0	OS:	Any
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any
Tags:	aggregate, crashing bug, distinct, regression

Description:
The following query causes a crash in azalea (was not able to repeat with 5.1 in my testing)
SELECT  SUM(  DISTINCT `varchar_key`  )  
FROM BB   ;

NOTE:  This did not appear to be affected by optimizer_switch, ECP, optimizer_use_mrr, or join_cache_level.

This query produces the following crash output (relevant thread is here, the full rqg-generated crash output is attached separately)

# 16:33:23 Thread 1 (process 5094):
# 16:33:23 #0  0xb7f25430 in __kernel_vsyscall ()
# 16:33:23 #1  0xb7eff1c8 in pthread_kill () from /lib/tls/i686/cmov/libpthread.so.0
# 16:33:23 #2  0x091be212 in my_write_core (sig=6) at stacktrace.c:309
# 16:33:23 #3  0x084bcda2 in handle_segfault (sig=6) at mysqld.cc:2739
# 16:33:23 #4  <signal handler called>
# 16:33:23 #5  0xb7f25430 in __kernel_vsyscall ()
# 16:33:23 #6  0xb7c476d0 in raise () from /lib/tls/i686/cmov/libc.so.6
# 16:33:23 #7  0xb7c49098 in abort () from /lib/tls/i686/cmov/libc.so.6
# 16:33:23 #8  0xb7c405ce in __assert_fail () from /lib/tls/i686/cmov/libc.so.6
# 16:33:23 #9  0x08219ea8 in ~Dbug_violation_helper (this=0xb35a1cf8) at ../../include/my_dbug.h:29
# 16:33:23 #10 0x082b7812 in Aggregator_distinct::setup (this=0xad8f988, thd=0xa7d5b58) at item_sum.cc:892
# 16:33:23 #11 0x086e41ae in Item_sum::aggregator_setup (this=0xa7f6120, thd=0xa7d5b58) at item_sum.h:480
# 16:33:23 #12 0x08656362 in setup_sum_funcs (thd=0xa7d5b58, func_ptr=0xa845834) at sql_select.cc:20681
# 16:33:23 #13 0x086cdbef in JOIN::optimize (this=0xaa55780) at sql_select.cc:2155
# 16:33:23 #14 0x086d3592 in mysql_select (thd=0xa7d5b58, rref_pointer_array=0xa7d70bc, tables=0xa7f6700, wild_num=0, fields=@0xa7d704c, conds=0xa88d5d0, og_num=2, 
# 16:33:23     order=0xa88dbd8, group=0x0, having=0x0, proc_param=0x0, select_options=2147764737, result=0xa88ddb0, unit=0xa7d6b00, select_lex=0xa7d6fb8) at sql_select.cc:3073
# 16:33:23 #15 0x086e37a6 in handle_select (thd=0xa7d5b58, lex=0xa7d6aa4, result=0xa88ddb0, setup_tables_done_option=0) at sql_select.cc:306
# 16:33:23 #16 0x084f32f2 in execute_sqlcom_select (thd=0xa7d5b58, all_tables=0xa7f6700) at sql_parse.cc:4942
# 16:33:23 #17 0x084f81cb in mysql_execute_command (thd=0xa7d5b58) at sql_parse.cc:2122
# 16:33:23 #18 0x085155ba in mysql_parse (thd=0xa7d5b58, 
# 16:33:23     inBuf=0xa7f5a40 "SELECT DISTINCT   SUM( DISTINCT table1 . `varchar_key` ) AS field1 FROM ( BB AS table1 STRAIGHT_JOIN ( ( CC AS table2 INNER JOIN C AS table3 ON (table3 . `pk` = table2 . `pk`  ) ) ) ON (table3 . `int_"..., length=735, found_semicolon=0xb35a3988) at sql_parse.cc:5957
# 16:33:23 #19 0x085178bf in dispatch_command (command=COM_QUERY, thd=0xa7d5b58, 
# 16:33:23     packet=0xa8057e9 "  SELECT DISTINCT   SUM( DISTINCT table1 . `varchar_key` ) AS field1 FROM ( BB AS table1 STRAIGHT_JOIN ( ( CC AS table2 INNER JOIN C AS table3 ON (table3 . `pk` = table2 . `pk`  ) ) ) ON (table3 . `in"..., packet_length=737) at sql_parse.cc:1073
# 16:33:23 #20 0x0851bb61 in do_command (thd=0xa7d5b58) at sql_parse.cc:755
# 16:33:23 #21 0x084df32a in handle_one_connection (arg=0xa7d5b58) at sql_connect.cc:1163
# 16:33:23 #22 0xb7efa4ff in start_thread () from /lib/tls/i686/cmov/libpthread.so.0
# 16:33:23 #23 0xb7d0049e in clone () from /lib/tls/i686/cmov/libc.so.6

How to repeat:
MTR test case:

/*!50400 SET SESSION optimizer_switch = 'firstmatch=on,index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,loosescan=on,materialization=off,semijoin=off' */;
/*!50400 SET SESSION optimizer_use_mrr = 'force' */;
/*!50400 SET SESSION engine_condition_pushdown = 'ON' */;
/*!50400 SET SESSION join_cache_level = 1 */;

#/* Begin test case for query 0 */

--disable_warnings
DROP TABLE /*! IF EXISTS */ BB;
--enable_warnings

CREATE TABLE BB (pk INTEGER AUTO_INCREMENT,
int_nokey INTEGER /*! NULL */,int_key INTEGER /*! NULL */,
date_key DATE /*! NULL */,vdate_nokey DATE /*! NULL */,
time_key TIME /*! NULL */,vtime_nokey TIME /*! NULL */,
datetime_key DATETIME /*! NULL */,datetime_nokey DATETIME /*! NULL */,varchar_key VARCHAR(1) /*! NULL */,varchar_nokey VARCHAR(1) /*! NULL */,

PRIMARY KEY (pk),
KEY (int_key),
KEY (date_key),
KEY (time_key),
KEY (datetime_key),
KEY (varchar_key, int_key)
)  AUTO_INCREMENT=10;

INSERT /*! IGNORE */ INTO BB (
int_key, int_nokey,
date_key, date_nokey,
time_key, time_nokey,
datetime_key, datetime_nokey,
varchar_key, varchar_nokey) VALUES (8, 8, '2002-02-21', '2002-02-21', '18:27:58', '18:27:58', '1900-01-01 00:00:00', '1900-01-01 00:00:00', NULL, NULL);
 
SELECT  SUM(  DISTINCT `varchar_key`  )  
FROM BB   ;

DROP TABLE BB;
#/* End of test case for query 0 */

Suggested fix:
Ensure crash-free query processing.

Full rqg-generated crash output

Attachment: Bug47421_crash_output.txt (text/plain), 17.19 KiB.

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

2836 Alexander Nozdrin	2009-09-23
      A patch for Bug#47421 (Server crash from -
      SELECT SUM ( DISTINCT <char_column>) in azalea).
      
      The problem was that DBUG_RETURN() macro should have been
      used instead the 'return' operator.

Pushed into mysql-6.0-bugfixing.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20090929093622-1mooerbh12e97zux) (version source revid:alik@sun.com-20090927203924-087s36mrs0uxepwb) (merge vers: 6.0.14-alpha) (pib:11)

Noted in 6.0.14 changelog.

Queries of the form SELECT SUM(DISTINCT varchar_key) FROM tbl_name
caused a server crash.

Setting report to NDI pending push into 5.4.x.

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

2932 Alexander Nozdrin	2009-10-16
      Backporting a patch for Bug#47421 from 6.0:
      revno: 2617.68.41
      committer: Alexander Nozdrin <alik@sun.com>
      branch nick: mysql-6.0-codebase-bugfixing-bug47421
      timestamp: Wed 2009-09-23 17:48:11 +0400
      message:
        A patch for Bug#47421 (Server crash from -
        SELECT SUM ( DISTINCT <char_column>) in azalea).
        
        The problem was that DBUG_RETURN() macro should have been
        used instead the 'return' operator.

Pushed into 5.5.0.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091102151658-j9o4wgro47m5v84d) (version source revid:alik@sun.com-20091023064702-2f8jdmny61bdl94u) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:mikael@mysql.com-20091103113702-p61dlwc6ml6fxg18) (merge vers: 5.5.0-beta) (pib:13)

Noted in 5.5.0 changelog.