Bug #37870 Assertion in convert_constant_item: result = 0
Submitted: 4 Jul 2008 13:00 Modified: 11 Nov 2008 21:35
Reporter: Philip Stoev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:5.0 -r2646, 5.1 - r2673 , 6.0 - r2764 OS:Any
Assigned to: Evgeny Potemkin CPU Architecture:Any

[4 Jul 2008 13:00] Philip Stoev
Description:
When executing a (EXPLAIN) SELECT workload, 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=6) at stacktrace.c:307
#3  0x08213e88 in handle_segfault (sig=6) at mysqld.cc:2638
#4  <signal handler called>
#5  0x00110416 in __kernel_vsyscall ()
#6  0x003e6660 in raise () from /lib/libc.so.6
#7  0x003e8028 in abort () from /lib/libc.so.6
#8  0x003df57e in __assert_fail () from /lib/libc.so.6
#9  0x081a259b in convert_constant_item (thd=0x9a3ad80, field_item=0x9a45f70, item=0x9a460c4) at item_cmpfunc.cc:434
#10 0x081ab542 in Item_bool_func2::fix_length_and_dec (this=0x9a46068) at item_cmpfunc.cc:499
#11 0x0819259d in Item_func::fix_fields (this=0x9a46068, thd=0x9a3ad80, ref=0x9a461fc) at item_func.cc:199
#12 0x081a3858 in Item_cond::fix_fields (this=0x9a46158, thd=0x9a3ad80, ref=0x9aba21c) at item_cmpfunc.cc:3959
#13 0x082569d3 in setup_conds (thd=0x9a3ad80, tables=0x9a458c0, leaves=0x9a458c0, conds=0x9aba21c) at sql_base.cc:6997
#14 0x0827a142 in JOIN::prepare (this=0x9ab8eb0, rref_pointer_array=0x9a4534c, tables_init=0x9a458c0, wild_num=0, conds_init=0x9a46158, og_num=1,
    order_init=0x0, group_init=0x9a462e8, having_init=0x9a46408, proc_param_init=0x0, select_lex_arg=0x9a45248, unit_arg=0x9a453e0) at sql_select.cc:443
#15 0x081d2f47 in subselect_single_select_engine::prepare (this=0x9a46590) at item_subselect.cc:2071
#16 0x081d4384 in Item_subselect::fix_fields (this=0x9a464f8, thd_param=0x9a3ad80, ref=0x9a46610) at item_subselect.cc:174
#17 0x08192463 in Item_func::fix_fields (this=0x9a465b8, thd=0x9a3ad80, ref=0x9aaa3cc) at item_func.cc:172
#18 0x081a3858 in Item_cond::fix_fields (this=0x9aaa320, thd=0x9a3ad80, ref=0x9aab8b4) at item_cmpfunc.cc:3959
#19 0x082569d3 in setup_conds (thd=0x9a3ad80, tables=0x9a44e08, leaves=0x9a44e08, conds=0x9aab8b4) at sql_base.cc:6997
#20 0x0827a142 in JOIN::prepare (this=0x9aaa548, rref_pointer_array=0x9a3c264, tables_init=0x9a44e08, wild_num=0, conds_init=0x9aaa320, og_num=1,
    order_init=0x0, group_init=0x9aaa4a0, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x9a3c160, unit_arg=0x9a3bed0) at sql_select.cc:443
#21 0x0828e169 in mysql_select (thd=0x9a3ad80, rref_pointer_array=0x9a3c264, tables=0x9a44e08, wild_num=0, fields=@0x9a3c1f4, conds=0x9aaa320, og_num=1,
    order=0x0, group=0x9aaa4a0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x9aaa538, unit=0x9a3bed0, select_lex=0x9a3c160)
    at sql_select.cc:2971
#22 0x082910cc in handle_select (thd=0x9a3ad80, lex=0x9a3be74, result=0x9aaa538, setup_tables_done_option=0) at sql_select.cc:302
#23 0x082201d2 in execute_sqlcom_select (thd=0x9a3ad80, all_tables=0x9a44e08) at sql_parse.cc:4847
#24 0x08220f51 in mysql_execute_command (thd=0x9a3ad80) at sql_parse.cc:2015
#25 0x0822a01e in mysql_parse (thd=0x9a3ad80,
    inBuf=0x9a447c8 "SELECT OUTR . `date_key` AS X FROM C AS OUTR WHERE NOT EXISTS ( SELECT COUNT( INNR . `datetime_nokey` ) FROM CC AS INNR WHERE INNR . `date_nokey` = 241 OR OUTR . `date_key` < 109 GROUP BY INNR . `date"..., length=342, found_semicolon=0xa8a57314) at sql_parse.cc:5811
#26 0x0822a911 in dispatch_command (command=COM_QUERY, thd=0x9a3ad80,
    packet=0x9a3c799 "SELECT OUTR . `date_key` AS X FROM C AS OUTR WHERE NOT EXISTS ( SELECT COUNT( INNR . `datetime_nokey` ) FROM CC AS INNR WHERE INNR . `date_nokey` = 241 OR OUTR . `date_key` < 109 GROUP BY INNR . `date"..., packet_length=342) at sql_parse.cc:1051
#27 0x0822ba39 in do_command (thd=0x9a3ad80) at sql_parse.cc:724
#28 0x0821bee0 in handle_one_connection (arg=0x9a3ad80) at sql_connect.cc:1153
#29 0x0057d32f in start_thread () from /lib/libpthread.so.0
#30 0x0049a27e in clone () from /lib/libc.so.6

The assertion is here:

429         /* Restore the original field value. */
430         if (field_item->depended_from)
431         {
432           result= field->store(orig_field_val, TRUE);
433           /* orig_field_val must be a valid value that can be restored back. */
434           DBUG_ASSERT(!result); <<<<<<<< HERE
435         }
436         thd->variables.sql_mode= orig_sql_mode;
437         thd->count_cuted_fields= orig_count_cuted_fields;
438         if (table)

(gdb) print result
$3 = 0
(gdb) print orig_field_val
$4 = 12
(gdb) print field
$5 = (class Field *) 0x9a568f0
(gdb) print * field
$6 = {_vptr.Field = 0x8620fc8, ptr = 0x9a56756 "", null_ptr = 0x9a56748 "ВЪ\004", table = 0x9a56000, orig_table = 0x9a56000, table_name = 0x9a56080,
  field_name = 0x9a19a02 "date_key", comment = {str = 0x86c8761 "", length = 0}, key_start = {map = 4}, part_of_key = {map = 4},
  part_of_key_not_clustered = {map = 4}, part_of_sortkey = {map = 4}, part_of_key_wo_keyread = {map = 4}, unireg_check = Field::NONE, field_length = 10,
  flags = 16520, field_index = 3, null_bit = 8 '\b', is_created_from_null_item = false}

How to repeat:
If this crash happens again, a repeatable test case will be provided.
[4 Jul 2008 13:09] Philip Stoev
The query in question is:

SELECT OUTR . `date_key` AS X
FROM C AS OUTR WHERE NOT EXISTS (
 SELECT COUNT( INNR . `datetime_nokey` )
 FROM CC AS INNR
 WHERE INNR . `date_nokey` = 241
 OR OUTR . `date_key` < 109
 GROUP BY INNR . `datetime_key`
 HAVING X < 49
) AND NOT (
 OUTR . `int_key` >= 174
 AND NOT OUTR . `date_key` >= OUTR . `int_key`
)
GROUP BY OUTR . `int_key`
LIMIT 5

However executing the same query again on its own does not cause a crash.
[5 Jul 2008 11:30] Philip Stoev
Grammar file for bug 37870

Attachment: bug37870.yy (text/plain), 3.36 KiB.

[5 Jul 2008 11:34] Philip Stoev
To reproduce this bug, please clone the mysql-test-extra-6.0 tree and:

$ cd mysql-test-extra-6.0/mysql-test/gentest
$ ./runall.pl --basedir=/location/of/mysql-6.0 --engine=myisam \
  --threads=5 --queries=1000000 --grammar=/path/to/bug37870.yy

This will start a server, create the test tables and proceed to run random queries generated from the grammar in the .yy file. The crash will happen anywhere from 5 seconds to two hours of running the test (5 threads and 2 CPU cores). More threads and more CPU cores may or may not help reduce the time-to-crash.

To run against an existing server, please use:

$ ./gentest.pl --gendata --engine=myisam --threads=5 \
  --queries=1000000 --grammar=/path/to/bug37870.yy \ 
  --dsn=dbi:mysql:host=127.0.0.1:port=9306:user=root:database=test

Please let me know if a better/simplifed/in other format test case is required.
[9 Jul 2008 16:05] Philip Stoev
Setting to Open so that the bug verification team can check previous releases.
[17 Jul 2008 8:08] Philip Stoev
Affects 5.1 bzr as well.
[12 Aug 2008 18:19] Sveta Smirnova
Thank you for the report.

Verified as described.
[17 Oct 2008 7:55] 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/56436

2765 Evgeny Potemkin	2008-10-17
      Bug#37870: Usage of uninitialized value caused failed assertion.
      
      The convert_constant_item function converts a constant to integer using
      field for condition like 'field = a_constant'. When the convert_constant_item
      is called for a subquery the outer select is already being executed, so
      convert_constant_item saves field's value to prevent its corruption.
      For EXPLAIN field's value isn't initialized thus when convert_constant_item
      tries to restore saved value it fails assertion.
      
      Now the convert_constant_item doesn't save/restore field's value
      for EXPLAIN.
[27 Oct 2008 9:24] Evgeny Potemkin
The reported stack trace seems to belongs to the bug#37937.
The reported assertion occurs only on EXPLAIN queries.
[27 Oct 2008 9:26] 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/57091

2765 Evgeny Potemkin	2008-10-27
      Bug#37870: Usage of uninitialized value caused failed assertion.
            
      The convert_constant_item function converts a constant to integer using
      field for condition like 'field = a_constant'. When the convert_constant_item
      is called for a subquery the outer select is already being executed, so
      convert_constant_item saves field's value to prevent its corruption.
      For EXPLAIN field's value isn't initialized thus when convert_constant_item
      tries to restore saved value it fails assertion.
            
      Now the convert_constant_item doesn't save/restore field's value
      for EXPLAIN.
[28 Oct 2008 17:33] Philip Stoev
Evgeny, I am getting this assertion even on workloads that contain no EXPLAIN and no "=" operator at all.
[5 Nov 2008 13:11] 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/57889

2694 Evgeny Potemkin	2008-11-05
      Bug#37870: Usage of uninitialized value caused failed assertion.
      
      The convert_constant_item function converts a constant to integer using
      field for condition like 'field = a_constant'. In some cases the
      convert_constant_item is called for a subquery when outer select is already
      being executed, so convert_constant_item saves field's value to prevent its
      corruption. For EXPLAIN and at the prepare phase field's value isn't
      initialized yet, thus when convert_constant_item tries to restore saved
      value it fails assertion.
[5 Nov 2008 15:00] 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/57904

2694 Evgeny Potemkin	2008-11-05
      Bug#37870: Usage of uninitialized value caused failed assertion.
      
      The convert_constant_item function converts a constant to integer using
      field for condition like 'field = a_constant'. In some cases the
      convert_constant_item is called for a subquery when outer select is already
      being executed, so convert_constant_item saves field's value to prevent its
      corruption. For EXPLAIN and at the prepare phase field's value isn't
      initialized yet, thus when convert_constant_item tries to restore saved
      value it fails assertion.
      
      Now the convert_constant_item doesn't save/restore field's value if it's
      haven't been read yet. Outer constants values are always saved.
[5 Nov 2008 15:40] 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/57912

2694 Evgeny Potemkin	2008-11-05
      Bug#37870: Usage of uninitialized value caused failed assertion.
      
      The convert_constant_item function converts a constant to integer using
      field for condition like 'field = a_constant'. In some cases the
      convert_constant_item is called for a subquery when outer select is already
      being executed, so convert_constant_item saves field's value to prevent its
      corruption. For EXPLAIN and at the prepare phase field's value isn't
      initialized yet, thus when convert_constant_item tries to restore saved
      value it fails assertion.
      
      Now the convert_constant_item doesn't save/restore field's value if it's
      haven't been read yet. Outer constant values are always saved.
[10 Nov 2008 10:53] Bugs System
Pushed into 6.0.8-alpha  (revid:epotemkin@mysql.com-20081105204624-0typ2ug2w5qcmylq) (version source revid:mattias.jonsson@sun.com-20081106095916-hp895g17ase9ie7b) (pib:5)
[10 Nov 2008 11:37] Bugs System
Pushed into 5.1.30  (revid:epotemkin@mysql.com-20081105204624-0typ2ug2w5qcmylq) (version source revid:epotemkin@mysql.com-20081105204624-0typ2ug2w5qcmylq) (pib:5)
[11 Nov 2008 16:05] Paul DuBois
The versions are actually 5.1.31, 6.0.9.
[11 Nov 2008 21:35] Paul DuBois
Noted in 5.1.31, 6.0.9 changelog.

Use of an uninitialized constant in EXPLAIN evaluation caused an 
assertion failure.
[19 Jan 2009 11:25] Bugs System
Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)
[19 Jan 2009 13:02] Bugs System
Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)
[19 Jan 2009 16:08] Bugs System
Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)