Bug #37868 Crash in test_if_skip_sort_order, bad value for used_key_parts
Submitted: 4 Jul 2008 11:42 Modified: 1 Nov 2009 20:20
Reporter: Philip Stoev Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:6.0 OS:Any
Assigned to: CPU Architecture:Any

[4 Jul 2008 11:42] Philip Stoev
Description:
When executing a concurrent workload consisting of (EXPLAIN) SELECTs using joins and subqueries and group by, mysqld crashed as follows:

#0  0x00110416 in __kernel_vsyscall ()
#1  0x00581c78 in pthread_kill () from /lib/libpthread.so.0
#2  0x085b0d94 in my_write_core (sig=11) at stacktrace.c:307
#3  0x08213e88 in handle_segfault (sig=11) at mysqld.cc:2638
#4  <signal handler called>
#5  0x082846c0 in test_if_skip_sort_order (tab=0xa54f3e8, order=0xa548ca8, select_limit=1, no_changes=false, map=0xa534ad8) at sql_select.cc:16021
#6  0x08284eb2 in create_sort_index (thd=0xa4e3f30, join=0xa545e98, order=0xa548ca8, filesort_limit=4294967295, select_limit=1, is_order_by=true)
    at sql_select.cc:16280
#7  0x0828d5a6 in JOIN::exec (this=0xa545e98) at sql_select.cc:2754
#8  0x081d27ba in subselect_single_select_engine::exec (this=0xa548f80) at item_subselect.cc:2277
#9  0x081d0c4a in Item_subselect::exec (this=0xa548eb8) at item_subselect.cc:280
#10 0x081d42fc in Item_in_subselect::exec (this=0xa548eb8) at item_subselect.cc:330
#11 0x081d1363 in Item_in_subselect::val_bool (this=0xa548eb8) at item_subselect.cc:899
#12 0x0817cd6d in Item::val_bool_result (this=0xa548eb8) at ../item.h:742
#13 0x081a17e1 in Item_in_optimizer::val_int (this=0xa549858) at item_cmpfunc.cc:1595
#14 0x0816f04b in Item::val_bool (this=0xa549858) at item.cc:184
#15 0x081a1976 in Item_func_not::val_int (this=0xa548fa8) at item_cmpfunc.cc:275
#16 0x0816f04b in Item::val_bool (this=0xa548fa8) at item.cc:184
#17 0x081a0d5f in Item_cond_or::val_int (this=0xa54d4a0) at item_cmpfunc.cc:4273
#18 0x08281324 in evaluate_join_record (join=0xa54c0d0, join_tab=0xa54a2d8, error=<value optimized out>) at sql_select.cc:13775
#19 0x08281610 in sub_select (join=0xa54c0d0, join_tab=0xa54a2d8, end_of_records=false) at sql_select.cc:13637
#20 0x0828b5a9 in do_select (join=0xa54c0d0, fields=0xa54d410, table=0x0, procedure=0x0) at sql_select.cc:13387
#21 0x0828d751 in JOIN::exec (this=0xa54c0d0) at sql_select.cc:2811
#22 0x0828e1ea in mysql_select (thd=0xa4e3f30, rref_pointer_array=0xa4e5414, tables=0xa4e64a0, wild_num=0, fields=@0xa4e53a4, conds=0xa549500, og_num=1,
    order=0x0, group=0xa549670, having=0x0, proc_param=0x0, select_options=2147764736, result=0xa549708, unit=0xa4e5080, select_lex=0xa4e5310)
    at sql_select.cc:3001
#23 0x082910cc in handle_select (thd=0xa4e3f30, lex=0xa4e5024, result=0xa549708, setup_tables_done_option=0) at sql_select.cc:302
#24 0x082201d2 in execute_sqlcom_select (thd=0xa4e3f30, all_tables=0xa4e64a0) at sql_parse.cc:4847
#25 0x08220f51 in mysql_execute_command (thd=0xa4e3f30) at sql_parse.cc:2015
#26 0x0822a01e in mysql_parse (thd=0xa4e3f30,
    inBuf=0xa4e5d70 "SELECT COUNT( OUTR . `int_nokey` ) AS X FROM C AS OUTR WHERE OUTR . `pk` NOT IN ( SELECT COUNT( INNR . `int_nokey` ) FROM AA AS INNR2 LEFT JOIN CC AS INNR ON ( INNR2 . `int_key` = INNR . `pk` ) WHERE "..., length=378, found_semicolon=0xa8a35314) at sql_parse.cc:5811
#27 0x0822a911 in dispatch_command (command=COM_QUERY, thd=0xa4e3f30, packet=0xa4f1559 "", packet_length=380) at sql_parse.cc:1051
#28 0x0822ba39 in do_command (thd=0xa4e3f30) at sql_parse.cc:724
#29 0x0821bee0 in handle_one_connection (arg=0xa4e3f30) at sql_connect.cc:1153
#30 0x0057d32f in start_thread () from /lib/libpthread.so.0
#31 0x0049a27e in clone () from /lib/libc.so.6

The crash is here:

16016             KEY *keyinfo= tab->table->key_info+nr;
16017             if (select_limit == HA_POS_ERROR)
16018               select_limit= table_records;
16019             if (group)
16020             {
16021               rec_per_key= keyinfo->rec_per_key[used_key_parts-1];<<<HERE!
16022               set_if_bigger(rec_per_key, 1);
16023               /*
16024                 With a grouping query each group containing on average
16025                 rec_per_key records produces only one row that will

(gdb) print used_key_parts
$4 = 172906784

It appears to me that used_key_parts ended up having a random value. used_key_parts is initialized here:

if (keys.is_set(nr) &&
          (direction= test_if_order_by_key(order, table, nr, &used_key_parts)))

so either key.is_set(nr) was true and test_if_order_by_key() was never called or test_if_order_by_key() failed to initialize used_key_parts().

How to repeat:
The query that caused the crash was:

SELECT COUNT( OUTR . `int_nokey` ) AS X FROM C AS OUTR
WHERE OUTR . `pk` NOT IN (
 SELECT COUNT( INNR . `int_nokey` )
 FROM AA AS INNR2
 LEFT JOIN CC AS INNR
 ON ( INNR2 . `int_key` = INNR . `pk` )
 WHERE INNR . `pk` IS NULL
 AND NOT INNR . `pk` <> INNR . `int_key`
 GROUP BY INNR . `int_key`
 HAVING X < 218
) OR OUTR . `pk` <> 140
AND OUTR . `int_key` >= 141
GROUP BY OUTR . `int_key`;

However unfortunately executing the same query now does not cause a crash. If a repeatable test case is obtained, it will be provided.
[15 May 2009 5:36] MySQL Verification Team
Hi Philip, take a look at bug #44886
[1 Nov 2009 20:09] Valeriy Kravchuk
Do you have a repeatable test case for this already?
[1 Nov 2009 20:20] Philip Stoev
Duplicate of bug #44886