Bug #44303 Assertion failures in Field_xxx::store_xxx for materialized InsideOut semijoins
Submitted: 16 Apr 2009 1:55 Modified: 20 Nov 2010 23:21
Reporter: Elena Stepanova Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S1 (Critical)
Version:6.0.10-debug, 6.0.12-debug, 5.4.4-alpha-debug OS:Any
Assigned to: Øystein Grøvlen CPU Architecture:Any
Tags: optimizer_switch, semijoin, subquery

[16 Apr 2009 1:55] Elena Stepanova
Description:
There are bugs (bug#37936, bug#34800) which resemble this one to some extent.
However, I am not sure whether this new bug is a duplicate of any of those, because
- bug#37936 was closed as fixed in 6.0.10; 
- bug#34800 is still open, but I could not reproduce the problem described there on 6.0.10 (although it does happen on 6.0.4);
- the scenario described below does not seem to cause crash on 6.0.9. 

Assertion failures and backtraces:

mysqld: field.cc:2723: virtual int Field_new_decimal::store_decimal(const my_decimal*): Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field_index))' failed.

#6  0x00002ad690691286 in __assert_fail () from /lib64/libc.so.6
#7  0x000000000061d65a in Field_new_decimal::store_decimal (
    this=<value optimized out>, decimal_value=<value optimized out>)
    at field.cc:2723
#8  0x000000000076d88e in do_field_decimal (copy=0x288a6f0)
    at field_conv.cc:354
#9  0x000000000076d6a5 in do_copy_null (copy=0x2f47) at field_conv.cc:207
#10 0x00000000006a12c1 in rr_sequential_and_unpack (info=0x2889770)
    at sql_select.cc:15852
#11 0x000000000069de09 in join_read_record_no_init (tab=0x0)
    at sql_select.cc:17034
#12 0x00000000006b7945 in sub_select (join=0x28632b8, join_tab=0x28896e8,
    end_of_records=6) at sql_select.cc:16222
#13 0x00000000006b7eed in sub_select_sjm (join=0x28632b8,
    join_tab=<value optimized out>, end_of_records=6) at sql_select.cc:15961
#14 0x00000000006b911a in do_select (join=0x28632b8, fields=0x2868f00,
    table=0x0, procedure=0x0) at sql_select.cc:15786
#15 0x00000000006c7f9d in JOIN::exec (this=0x28632b8) at sql_select.cc:2881
#16 0x00000000006c2db2 in mysql_select (thd=0x28155f0,
    rref_pointer_array=0x28176a0, tables=<value optimized out>, wild_num=0,
    fields=<value optimized out>, conds=0x283e8f0, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147764736,
    result=0x283eaf8, unit=0x2817050, select_lex=0x28174b8)
    at sql_select.cc:3062
#17 0x00000000006c81bf in handle_select (thd=0x28155f0, lex=0x2816fb0,
    result=0x283eaf8, setup_tables_done_option=0) at sql_select.cc:314
#18 0x000000000064bc8e in execute_sqlcom_select (thd=0x28155f0,
    all_tables=0x283d128) at sql_parse.cc:4757
#19 0x000000000064cc6b in mysql_execute_command (thd=0x28155f0)
    at sql_parse.cc:2063
#20 0x0000000000653fd2 in mysql_parse (thd=0x28155f0,
    inBuf=0x283cdc8 "SELECT COUNT(i1) FROM tb1\nWHERE (f1,f3) IN (SELECT f1,f3 FROM tmp1)", length=67, found_semicolon=0x4a8d70c8) at sql_parse.cc:5752
#21 0x0000000000654887 in dispatch_command (command=COM_QUERY, thd=0x28155f0,
    packet=0x282d071 "", packet_length=67) at sql_parse.cc:1009
#22 0x000000000065578e in do_command (thd=0x28155f0) at sql_parse.cc:691
#23 0x0000000000646d75 in handle_one_connection (arg=<value optimized out>)
    at sql_connect.cc:1146
#24 0x00002ad68feaf143 in start_thread () from /lib64/libpthread.so.0
#25 0x00002ad6907288cd in clone () from /lib64/libc.so.6
#26 0x0000000000000000 in ?? ()

mysqld: field.cc:5863: virtual int Field_datetime::store(const char*, uint, CHARSET_INFO*): Assertion `!table || (!table->write_set || bitmap_is_set(table->write_set, field_index))' failed.

#6  0x00002b2b0e006286 in __assert_fail () from /lib64/libc.so.6
#7  0x000000000062471e in Field_datetime::store (this=0x2849008,
    from=0x4a8d5090 "2009-04-16 03:08:38", len=19, cs=<value optimized out>)
    at field.cc:5863
#8  0x000000000076d7f2 in do_field_string (copy=0x288a6f0) at field_conv.cc:309
#9  0x000000000076d6a5 in do_copy_null (copy=0xa65) at field_conv.cc:207
#10 0x00000000006a12c1 in rr_sequential_and_unpack (info=0x2889770)
    at sql_select.cc:15852
#11 0x000000000069de09 in join_read_record_no_init (tab=0x0)
    at sql_select.cc:17034
#12 0x00000000006b7945 in sub_select (join=0x28632b8, join_tab=0x28896e8,
    end_of_records=6) at sql_select.cc:16222
#13 0x00000000006b7eed in sub_select_sjm (join=0x28632b8,
    join_tab=<value optimized out>, end_of_records=6) at sql_select.cc:15961
#14 0x00000000006b911a in do_select (join=0x28632b8, fields=0x2868f00,
    table=0x0, procedure=0x0) at sql_select.cc:15786
#15 0x00000000006c7f9d in JOIN::exec (this=0x28632b8) at sql_select.cc:2881
#16 0x00000000006c2db2 in mysql_select (thd=0x28155f0,
    rref_pointer_array=0x28176a0, tables=<value optimized out>, wild_num=0,
    fields=<value optimized out>, conds=0x283e8f0, og_num=0, order=0x0,
    group=0x0, having=0x0, proc_param=0x0, select_options=2147764736,
    result=0x283eaf8, unit=0x2817050, select_lex=0x28174b8)
    at sql_select.cc:3062
#17 0x00000000006c81bf in handle_select (thd=0x28155f0, lex=0x2816fb0,
    result=0x283eaf8, setup_tables_done_option=0) at sql_select.cc:314
#18 0x000000000064bc8e in execute_sqlcom_select (thd=0x28155f0,
    all_tables=0x283d128) at sql_parse.cc:4757
#19 0x000000000064cc6b in mysql_execute_command (thd=0x28155f0)
    at sql_parse.cc:2063
#20 0x0000000000653fd2 in mysql_parse (thd=0x28155f0,
    inBuf=0x283cdc8 "SELECT COUNT(i1) FROM tb1\nWHERE (f1,f3) IN (SELECT f1,f3 FROM tmp1)", length=67, found_semicolon=0x4a8d70c8) at sql_parse.cc:5752
#21 0x0000000000654887 in dispatch_command (command=COM_QUERY, thd=0x28155f0,
    packet=0x282d071 "", packet_length=67) at sql_parse.cc:1009
#22 0x000000000065578e in do_command (thd=0x28155f0) at sql_parse.cc:691
#23 0x0000000000646d75 in handle_one_connection (arg=<value optimized out>)
    at sql_connect.cc:1146
#24 0x00002b2b0d824143 in start_thread () from /lib64/libpthread.so.0
#25 0x00002b2b0e09d8cd in clone () from /lib64/libc.so.6
#26 0x0000000000000000 in ?? ()

How to repeat:
# Scenario for decimal:

use test;
drop table if exists tb1;
drop table if exists tmp1;
create table tb1 ( f1 int, f3 decimal (5,3) ) engine=MyISAM;

INSERT INTO tb1 (f1, f3) values (1, 1.789);
INSERT INTO tb1 (f1, f3) values (13, 1.454);
INSERT INTO tb1 (f1, f3) values (10, 1.668);

CREATE TABLE tmp1 as SELECT f1, f3 FROM tb1 where 1=0;

INSERT INTO tmp1 values (1, 1.789);
INSERT INTO tmp1 values (13, 1.454);

SELECT COUNT(*) FROM tb1
WHERE (f1,f3) IN (SELECT f1,f3 FROM tmp1);

# The same scenario for datetime, only f3 type is different:

use test;
drop table if exists tb1;
drop table if exists tmp1;
create table tb1 ( f1 int, f3 datetime ) engine=MyISAM;

INSERT INTO tb1 (f1, f3) values (1, now());
INSERT INTO tb1 (f1, f3) values (13, now());
INSERT INTO tb1 (f1, f3) values (10, now());

CREATE TABLE tmp1 as SELECT f1, f3 FROM tb1 where 1=0;

INSERT INTO tmp1 values (1, now());
INSERT INTO tmp1 values (13, now());

SELECT COUNT(*) FROM tb1
WHERE (f1,f3) IN (SELECT f1,f3 FROM tmp1);
[14 Jul 2009 22:24] Patrick Crews
The optimizer_switch, optimizer_use_mrr, and engine_condition_pushdown variable settings did not affect this crash (present regardless of their values)
[16 Sep 2009 7:18] Roy Lyseng
This bug goes away when semijoin is turned off:

set @@optimizer_switch='default,semijoin=off';
[25 Sep 2009 6:54] Øystein Grøvlen
Same core dump for the following query (without aggregation):
  SELECT * FROM tb1 WHERE (f1,f3) IN (SELECT f1,f3 FROM tmp1);

From inspecting the core dump, it seems one tries to update fields of tmp1 while scanning the materialization earlier made of tmp1. Strange ...

Query plan:

mysql> explain SELECT * FROM tb1 WHERE (f1,f3) IN (SELECT f1,f3 FROM tmp1)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: tmp1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
        Extra: Materialize; Scan
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: tb1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
        Extra: Using where; Using join buffer
2 rows in set (0.00 sec)
[25 Sep 2009 9:13] Øystein Grøvlen
I realized, with the help of Evgeny, that fields of tmp1 is not actually updated,
it is the record buffer for tmp1 that is updated.  As described in a comment in setup_sj_materialization(), this is done since the join condition refers to the current row of tmp1. Hence, the content of the temp table is copied back into the record buffer.

The problem is that the write_set of tmp1 is not set up to accept writes to the record buffer.  Hence, the failing assert.
[25 Sep 2009 12:15] 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/84643

2840 oystein.grovlen@sun.com	2009-09-25
      Bug#44303 Assertion failures in Field_new_decimal::store_decimal when executing
                materialized InsideOut semijoin
      
      When inner tables of semijoin is materialized, field values are copied back
      from the materialized table to the record buffer of the source tables.
      The problem was that the write_set of the source tables were not set up to
      allow such copying.  
     @ mysql-test/r/subselect_mat.result
        Update result file with new test case for Bug#44303.
     @ mysql-test/t/subselect_mat.test
        Test for Bug#44303.  A semijoin where InsideOut strategy with materialization
        of inner table is used.
     @ sql/sql_select.cc
        setup_sj_materialization():  If field values are to be copied back from 
        a materialized table to the record buffer of source tables, set write_set
        for source tables so that this copying is allowed.
        
        Fixed some comments to make it clear that the source tables are not actually
        updated, just the record buffers.
[29 Sep 2009 10:45] 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/84971

3626 oystein.grovlen@sun.com	2009-09-29
      Bug#44303 Assertion failures in Field_new_decimal::store_decimal when executing
                materialized InsideOut semijoin
      
      When inner tables of semijoin is materialized, field values are copied back
      from the materialized table to the record buffer of the source tables.
      The problem was that the write_set of the source tables were not set up to
      allow such copying.  
     @ mysql-test/r/subselect_mat.result
        Update result file with new test case for Bug#44303.
     @ mysql-test/suite/ndb/t/ndb_read_multi_range.test
        Re-enable this test for Mac platform.  (Bug#42979 is a duplicate of this bug)
     @ mysql-test/t/subselect_mat.test
        Test for Bug#44303.  A semijoin where InsideOut strategy with materialization
        of inner table is used.
     @ sql/sql_select.cc
        setup_sj_materialization():  If field values are to be copied back from 
        a materialized table to the record buffer of source tables, set write_set
        for source tables so that this copying is allowed.
        
        Fixed some comments to make it clear that the source tables are not actually
        updated, just the record buffers.
[5 Oct 2009 15:39] 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/85780

3643 oystein.grovlen@sun.com	2009-10-05
      Bug#44303 Assertion failures in Field_new_decimal::store_decimal when executing
                materialized InsideOut semijoin
      
      When inner tables of semijoin is materialized, field values are copied back
      from the materialized table to the record buffer of the source tables.
      The problem was that the write_set of the source tables were not set up to
      allow such copying.  
     @ mysql-test/r/subselect_mat.result
        Update result file with new test case for Bug#44303.
     @ mysql-test/suite/ndb/t/ndb_read_multi_range.test
        Re-enable this test for Mac platform.  (Bug#42979 is a duplicate of this bug)
     @ mysql-test/t/subselect_mat.test
        Test for Bug#44303.  A semijoin where InsideOut strategy with materialization
        of inner table is used.
     @ sql/sql_select.cc
        setup_sj_materialization():  If field values are to be copied back from 
        a materialized table to the record buffer of source tables, set write_set
        for source tables so that this copying is allowed.
        
        Fixed some comments to make it clear that the source tables are not actually
        updated, just the record buffers.
[6 Oct 2009 16:32] 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/85941

3645 oystein.grovlen@sun.com	2009-10-06
      Bug#44303 Assertion failures in Field_new_decimal::store_decimal when executing
                materialized InsideOut semijoin
      
      When inner tables of semijoin is materialized, field values are copied back
      from the materialized table to the record buffer of the source tables.
      The problem was that the write_set of the source tables were not set up to
      allow such copying.  
     @ mysql-test/r/subselect_mat.result
        Update result file with new test case for Bug#44303.
     @ mysql-test/suite/ndb/t/ndb_read_multi_range.test
        Re-enable this test for Mac platform.  (Bug#42979 is a duplicate of this bug)
     @ mysql-test/t/subselect_mat.test
        Test for Bug#44303.  A semijoin where InsideOut strategy with materialization
        of inner table is used.
     @ sql/sql_select.cc
        setup_sj_materialization():  If field values are to be copied back from 
        a materialized table to the record buffer of source tables, set write_set
        for source tables so that this copying is allowed.
        
        Fixed some comments to make it clear that the source tables are not actually
        updated, just the record buffers.
[9 Oct 2009 9:36] 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/86315

3652 oystein.grovlen@sun.com	2009-10-09
      Bug#44303 Assertion failures in Field_new_decimal::store_decimal when executing
                materialized InsideOut semijoin
      
      When inner tables of semijoin is materialized, field values are copied back
      from the materialized table to the record buffer of the source tables.
      The problem was that the write_set of the source tables were not set up to
      allow such copying.  
     @ mysql-test/r/subselect_mat.result
        Update result file with new test case for Bug#44303.
     @ mysql-test/suite/ndb/t/ndb_read_multi_range.test
        Re-enable this test for Mac platform.  (Bug#42979 is a duplicate of this bug)
     @ mysql-test/t/subselect_mat.test
        Test for Bug#44303.  A semijoin where InsideOut strategy with materialization
        of inner table is used.
     @ sql/sql_select.cc
        setup_sj_materialization():  If field values are to be copied back from 
        a materialized table to the record buffer of source tables, set write_set
        for source tables so that this copying is allowed.
        
        Fixed some comments to make it clear that the source tables are not actually
        updated, just the record buffers.
[9 Oct 2009 14:07] Øystein Grøvlen
Patch pushed to mysql-6.0-codebase-bugfixing. 
revid:oystein.grovlen@sun.com-20091009093541-7bkrgk2tsnpi6iyf
[31 Oct 2009 8:18] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20091031081410-qkxmjsdzjmj840aq) (version source revid:guilhem@mysql.com-20091010123356-qfouds7ucvkvbczt) (merge vers: 6.0.14-alpha) (pib:13)
[2 Nov 2009 21:11] Paul DuBois
Noted in 6.0.14 changelog.

Improper materialization of semijoin inner tables could cause an
assertion failure.
[6 Apr 2010 11: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/105064

3060 oystein.grovlen@sun.com	2010-04-06
      Bug#44303 Assertion failures in Field_new_decimal::store_decimal when executing
                materialized InsideOut semijoin
      
      (Backporting of revid:oystein.grovlen@sun.com-20091009093541-7bkrgk2tsnpi6iyf)
      
      When inner tables of semijoin is materialized, field values are copied back
      from the materialized table to the record buffer of the source tables.
      The problem was that the write_set of the source tables were not set up to
      allow such copying.  
     @ mysql-test/r/subselect_mat.result
        Update result file with new test case for Bug#44303.
     @ mysql-test/t/subselect_mat.test
        Re-enable this test for Mac platform.  (Bug#42979 is a duplicate of this bug)
     @ sql/sql_select.cc
        setup_sj_materialization():  If field values are to be copied back from 
        a materialized table to the record buffer of source tables, set write_set
        for source tables so that this copying is allowed.
        
        Fixed some comments to make it clear that the source tables are not actually
        updated, just the record buffers.
[6 Apr 2010 11: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/105068

3060 oystein.grovlen@sun.com	2010-04-06
      Bug#44303 Assertion failures in Field_new_decimal::store_decimal when executing
                materialized InsideOut semijoin
      
      (Backporting of revid:oystein.grovlen@sun.com-20091009093541-7bkrgk2tsnpi6iyf)
      
      When inner tables of semijoin is materialized, field values are copied back
      from the materialized table to the record buffer of the source tables.
      The problem was that the write_set of the source tables were not set up to
      allow such copying.  
     @ mysql-test/r/subselect_mat.result
        Update result file with new test case for Bug#44303.
     @ mysql-test/t/subselect_mat.test
        Test for Bug#44303.  A semijoin where InsideOut strategy with materialization
        of inner table is used.
     @ sql/sql_select.cc
        setup_sj_materialization():  If field values are to be copied back from 
        a materialized table to the record buffer of source tables, set write_set
        for source tables so that this copying is allowed.
        
        Fixed some comments to make it clear that the source tables are not actually
        updated, just the record buffers.
[16 Aug 2010 6:38] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100816062819-bluwgdq8q4xysmlg) (version source revid:alik@sun.com-20100816062612-enatdwnv809iw3s9) (pib:20)
[13 Nov 2010 16:25] Bugs System
Pushed into mysql-trunk 5.6.99-m5 (revid:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (merge vers: 5.6.99-m4) (pib:21)
[20 Nov 2010 23:21] Paul DuBois
Noted in 5.6.1 changelog.
[23 Nov 2010 2:12] Paul DuBois
Correction: No 5.6.1 changelog entry. Bug does not appear in any released 5.6.x version.