Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13723

Server crashes in ha_heap::find_unique_row or Assertion `0' failed in st_select_lex_unit::optimize with INTERSECT

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.3(EOL)
    • 10.3.3
    • Optimizer
    • None

    Description

      CREATE TABLE t (i INT);
      INSERT INTO t VALUES (1),(2);
      SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT SELECT 3 );
      

      10.3 non-debug 016c35a7f2f4a2

      #2  <signal handler called>
      #3  ha_heap::find_unique_row (this=0x7fc4080aa1c0, record=0x7fc4080a9f80 "\377\003", unique_idx=<optimized out>) at /data/src/10.3/storage/heap/ha_heap.cc:813
      #4  0x000056176c1f1fe5 in select_unit::send_data (this=0x7fc40802010d, values=...) at /data/src/10.3/sql/sql_union.cc:209
      #5  0x000056176c1b2462 in JOIN::exec_inner (this=this@entry=0x7fc408014548) at /data/src/10.3/sql/sql_select.cc:3418
      #6  0x000056176c1b26b9 in JOIN::exec (this=0x7fc408014548) at /data/src/10.3/sql/sql_select.cc:3337
      #7  0x000056176c1f403b in st_select_lex_unit::exec (this=0x7fc408012080) at /data/src/10.3/sql/sql_union.cc:1428
      #8  0x000056176c3a870d in subselect_union_engine::exec (this=0x7fc4080132d0) at /data/src/10.3/sql/item_subselect.cc:3873
      #9  0x000056176c3a8ebd in Item_subselect::exec (this=0x7fc4080130c0) at /data/src/10.3/sql/item_subselect.cc:715
      #10 0x000056176c3a92bf in Item_in_subselect::val_bool (this=0x7fc4080130c0) at /data/src/10.3/sql/item_subselect.cc:1729
      #11 0x000056176c34b94d in Item_in_optimizer::val_int (this=0x7fc4080142b0) at /data/src/10.3/sql/item_cmpfunc.cc:1634
      #12 0x000056176c327f42 in Item_cache_int::cache_value (this=0x7fc40806d280) at /data/src/10.3/sql/item.cc:9410
      #13 0x000056176c33aa67 in cache (this=0x7fc40806d1c8) at /data/src/10.3/sql/item.cc:8252
      #14 Item_cache_wrapper::val_int (this=0x7fc40806d1c8) at /data/src/10.3/sql/item.cc:8306
      #15 0x000056176c3497ec in Item_func_nop_all::val_int (this=0x7fc408013308) at /data/src/10.3/sql/item_cmpfunc.cc:285
      #16 0x000056176c185d08 in evaluate_join_record (join=join@entry=0x7fc4080134d0, join_tab=join_tab@entry=0x7fc408016050, error=error@entry=0) at /data/src/10.3/sql/sql_select.cc:18962
      #17 0x000056176c18fc5b in sub_select (join=0x7fc4080134d0, join_tab=0x7fc408016050, end_of_records=<optimized out>) at /data/src/10.3/sql/sql_select.cc:18906
      #18 0x000056176c1b24cc in do_select (procedure=<optimized out>, join=0x7fc4080134d0) at /data/src/10.3/sql/sql_select.cc:18407
      #19 JOIN::exec_inner (this=this@entry=0x7fc4080134d0) at /data/src/10.3/sql/sql_select.cc:3542
      #20 0x000056176c1b26b9 in JOIN::exec (this=this@entry=0x7fc4080134d0) at /data/src/10.3/sql/sql_select.cc:3337
      #21 0x000056176c1b27fb in mysql_select (thd=thd@entry=0x7fc4080009a8, tables=0x7fc408011570, wild_num=1, fields=..., conds=0x7fc408013308, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc4080134b0, unit=0x7fc4080044a8, select_lex=0x7fc408004be8) at /data/src/10.3/sql/sql_select.cc:3737
      #22 0x000056176c1b3210 in handle_select (thd=thd@entry=0x7fc4080009a8, lex=lex@entry=0x7fc4080043e0, result=result@entry=0x7fc4080134b0, setup_tables_done_option=setup_tables_done_option@entry=0) at /data/src/10.3/sql/sql_select.cc:378
      #23 0x000056176c09a2eb in execute_sqlcom_select (thd=0x7fc4080009a8, all_tables=0x7fc408011570) at /data/src/10.3/sql/sql_parse.cc:6466
      #24 0x000056176c16016c in mysql_execute_command (thd=0x7fc4080009a8) at /data/src/10.3/sql/sql_parse.cc:3731
      #25 0x000056176c162bca in mysql_parse (thd=0x7fc4080009a8, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>, is_com_multi=<optimized out>, is_next_command=<optimized out>) at /data/src/10.3/sql/sql_parse.cc:7921
      #26 0x000056176c166738 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x7fc4080009a8, packet=packet@entry=0x7fc408008fc9 "", packet_length=packet_length@entry=62, is_com_multi=is_com_multi@entry=false, is_next_command=is_next_command@entry=false) at /data/src/10.3/sql/sql_parse.cc:1819
      #27 0x000056176c166e3f in do_command (thd=0x7fc4080009a8) at /data/src/10.3/sql/sql_parse.cc:1370
      #28 0x000056176c230724 in do_handle_one_connection (connect=connect@entry=0x56176f1b21b8) at /data/src/10.3/sql/sql_connect.cc:1418
      #29 0x000056176c2308c4 in handle_one_connection (arg=arg@entry=0x56176f1b21b8) at /data/src/10.3/sql/sql_connect.cc:1324
      #30 0x000056176c515134 in pfs_spawn_thread (arg=0x56176f1d8448) at /data/src/10.3/storage/perfschema/pfs.cc:1862
      #31 0x00007fc41fd33494 in start_thread (arg=0x7fc418084700) at pthread_create.c:333
      #32 0x00007fc41deab93f in clone () from /lib/x86_64-linux-gnu/libc.so.6
      

      10.3 debug 016c35a7f2f4a2

      mysqld: /data/src/10.3/sql/sql_union.cc:1308: bool st_select_lex_unit::optimize(): Assertion `0' failed.
      170903 23:55:15 [ERROR] mysqld got signal 6 ;
       
      #7  0x00007f0289fd5ee2 in __assert_fail () from /lib/x86_64-linux-gnu/libc.so.6
      #8  0x00005602da76a3f2 in st_select_lex_unit::optimize (this=0x7f0274015758) at /data/src/10.3/sql/sql_union.cc:1308
      #9  0x00005602da76a76f in st_select_lex_unit::exec (this=0x7f0274015758) at /data/src/10.3/sql/sql_union.cc:1374
      #10 0x00005602daa11a53 in subselect_union_engine::exec (this=0x7f02740169a8) at /data/src/10.3/sql/item_subselect.cc:3873
      #11 0x00005602daa05f78 in Item_subselect::exec (this=0x7f0274016798) at /data/src/10.3/sql/item_subselect.cc:715
      #12 0x00005602daa065b4 in Item_in_subselect::exec (this=0x7f0274016798) at /data/src/10.3/sql/item_subselect.cc:895
      #13 0x00005602daa092a4 in Item_in_subselect::val_bool (this=0x7f0274016798) at /data/src/10.3/sql/item_subselect.cc:1729
      #14 0x00005602da5a9fa9 in Item::val_bool_result (this=0x7f0274016798) at /data/src/10.3/sql/item.h:1119
      #15 0x00005602da97e206 in Item_in_optimizer::val_int (this=0x7f0274017988) at /data/src/10.3/sql/item_cmpfunc.cc:1634
      #16 0x00005602da5a9f1d in Item::val_int_result (this=0x7f0274017988) at /data/src/10.3/sql/item.h:1115
      #17 0x00005602da96d9e1 in Item_cache_int::cache_value (this=0x7f027411c998) at /data/src/10.3/sql/item.cc:9410
      #18 0x00005602da975ae8 in Item_cache_wrapper::cache (this=0x7f027411c8e0) at /data/src/10.3/sql/item.cc:8252
      #19 0x00005602da96a74e in Item_cache_wrapper::val_int (this=0x7f027411c8e0) at /data/src/10.3/sql/item.cc:8306
      #20 0x00005602da979e78 in Item_func_nop_all::val_int (this=0x7f02740169e0) at /data/src/10.3/sql/item_cmpfunc.cc:285
      #21 0x00005602da6f1516 in evaluate_join_record (join=0x7f0274016ba8, join_tab=0x7f0274019728, error=0) at /data/src/10.3/sql/sql_select.cc:18962
      #22 0x00005602da6f12e4 in sub_select (join=0x7f0274016ba8, join_tab=0x7f0274019728, end_of_records=false) at /data/src/10.3/sql/sql_select.cc:18906
      #23 0x00005602da6f06c0 in do_select (join=0x7f0274016ba8, procedure=0x0) at /data/src/10.3/sql/sql_select.cc:18407
      #24 0x00005602da6c91b8 in JOIN::exec_inner (this=0x7f0274016ba8) at /data/src/10.3/sql/sql_select.cc:3542
      #25 0x00005602da6c863e in JOIN::exec (this=0x7f0274016ba8) at /data/src/10.3/sql/sql_select.cc:3337
      #26 0x00005602da6c9831 in mysql_select (thd=0x7f0274000b00, tables=0x7f0274014c48, wild_num=1, fields=..., conds=0x7f02740169e0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f0274016b88, unit=0x7f02740047c0, select_lex=0x7f0274004f00) at /data/src/10.3/sql/sql_select.cc:3737
      #27 0x00005602da6bddbc in handle_select (thd=0x7f0274000b00, lex=0x7f02740046f8, result=0x7f0274016b88, setup_tables_done_option=0) at /data/src/10.3/sql/sql_select.cc:378
      #28 0x00005602da68926e in execute_sqlcom_select (thd=0x7f0274000b00, all_tables=0x7f0274014c48) at /data/src/10.3/sql/sql_parse.cc:6466
      #29 0x00005602da67f7ef in mysql_execute_command (thd=0x7f0274000b00) at /data/src/10.3/sql/sql_parse.cc:3731
      #30 0x00005602da68cbd4 in mysql_parse (thd=0x7f0274000b00, rawbuf=0x7f0274014a08 "SELECT * FROM t WHERE i != ANY ( SELECT 6 INTERSECT SELECT 3 )", length=62, parser_state=0x7f028426c610, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7921
      #31 0x00005602da67a32e in dispatch_command (command=COM_QUERY, thd=0x7f0274000b00, packet=0x7f0274149c61 "", packet_length=62, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1819
      #32 0x00005602da678d90 in do_command (thd=0x7f0274000b00) at /data/src/10.3/sql/sql_parse.cc:1370
      #33 0x00005602da7cf646 in do_handle_one_connection (connect=0x5602dcd1d260) at /data/src/10.3/sql/sql_connect.cc:1418
      #34 0x00005602da7cf3d3 in handle_one_connection (arg=0x5602dcd1d260) at /data/src/10.3/sql/sql_connect.cc:1324
      #35 0x00005602dac471dc in pfs_spawn_thread (arg=0x5602dcdb6240) at /data/src/10.3/storage/perfschema/pfs.cc:1862
      #36 0x00007f028bf1a494 in start_thread (arg=0x7f028426d700) at pthread_create.c:333
      #37 0x00007f028a09293f in clone () from /lib/x86_64-linux-gnu/libc.so.6
      

      Attachments

        Issue Links

          Activity

            Here is other test suite which we got from respectable source:

            --source include/have_innodb.inc
             
             
            CREATE TABLE t1 (
              F1 varchar(36) DEFAULT NULL,
              F6 varchar(3) DEFAULT NULL,
              F7 varchar(5) DEFAULT NULL,
              F8 varchar(2) DEFAULT NULL,
              F2 varchar(6) DEFAULT NULL,
              F5 char(1) DEFAULT NULL,
              pk int(11) NOT NULL,
              PRIMARY KEY (pk),
              UNIQUE KEY PK_1 (F1,F7,F8,F2,F6),
              KEY I1 (F8,F5),
              KEY I2 (F1,F6,F8,F2)
            ) engine=innodb;
             
            insert into t1 values
            ("123","ABC","ABCDE","DE","ABCDEF", "I", 1234), 
            ("124","ABE","ABCD4","DE","ABCDEF", "I", 1235);
             
            CREATE TABLE t2 (
              F1 varchar(36) NOT NULL,
              F6 varchar(3) NOT NULL,
              F7 varchar(5) NOT NULL,
              F8 varchar(2) NOT NULL,
              F3 datetime NOT NULL,
              F2 varchar(6) NOT NULL,
              F9 decimal(20,4) DEFAULT NULL,
              F4 char(1) NOT NULL,
              FF1 char(1) NOT NULL DEFAULT 'P',
              PRIMARY KEY (F1,F7,F8,F3,F2,F6),
              UNIQUE KEY U1 (F1,F7,F8,F3,F2,F6),
              KEY K1 (F3),
              KEY K2 (F1,F7,F8,F2,F6),
              KEY K3 (F1,F8,F6,F2,F3),
              KEY K4 (F3,F8,F2,F4,FF1),
              KEY K5 (F2,F3,F9,F4),
              CONSTRAINT C1 FOREIGN KEY (F1, F7, F8, F2, F6) REFERENCES t1 (F1, F7, F8, F2, F6)
            ) engine=innodb;
             
             
            insert into t2 values
            ("123","ABC","ABCDE","DE", "20171104", "ABCDEF", 123, "I", "P");
             
            SELECT * FROM t1
              WHERE F1 IN (SELECT F1 FROM t1 WHERE F2 ='ABCDEF'
                           EXCEPT
                           SELECT F1 FROM t2 WHERE F2='ABCDEF' AND
                                                   F3='20171104' AND
                                                   F4='I'  )
                    AND F5 = 1;
             
            drop table t1,t2;
            

            sanja Oleksandr Byelkin added a comment - Here is other test suite which we got from respectable source: --source include/have_innodb.inc     CREATE TABLE t1 ( F1 varchar(36) DEFAULT NULL, F6 varchar(3) DEFAULT NULL, F7 varchar(5) DEFAULT NULL, F8 varchar(2) DEFAULT NULL, F2 varchar(6) DEFAULT NULL, F5 char(1) DEFAULT NULL, pk int(11) NOT NULL, PRIMARY KEY (pk), UNIQUE KEY PK_1 (F1,F7,F8,F2,F6), KEY I1 (F8,F5), KEY I2 (F1,F6,F8,F2) ) engine=innodb;   insert into t1 values ("123","ABC","ABCDE","DE","ABCDEF", "I", 1234), ("124","ABE","ABCD4","DE","ABCDEF", "I", 1235);   CREATE TABLE t2 ( F1 varchar(36) NOT NULL, F6 varchar(3) NOT NULL, F7 varchar(5) NOT NULL, F8 varchar(2) NOT NULL, F3 datetime NOT NULL, F2 varchar(6) NOT NULL, F9 decimal(20,4) DEFAULT NULL, F4 char(1) NOT NULL, FF1 char(1) NOT NULL DEFAULT 'P', PRIMARY KEY (F1,F7,F8,F3,F2,F6), UNIQUE KEY U1 (F1,F7,F8,F3,F2,F6), KEY K1 (F3), KEY K2 (F1,F7,F8,F2,F6), KEY K3 (F1,F8,F6,F2,F3), KEY K4 (F3,F8,F2,F4,FF1), KEY K5 (F2,F3,F9,F4), CONSTRAINT C1 FOREIGN KEY (F1, F7, F8, F2, F6) REFERENCES t1 (F1, F7, F8, F2, F6) ) engine=innodb;     insert into t2 values ("123","ABC","ABCDE","DE", "20171104", "ABCDEF", 123, "I", "P");   SELECT * FROM t1 WHERE F1 IN (SELECT F1 FROM t1 WHERE F2 ='ABCDEF' EXCEPT SELECT F1 FROM t2 WHERE F2='ABCDEF' AND F3='20171104' AND F4='I' ) AND F5 = 1;   drop table t1,t2;

            It looks like the table was not emptied between rexecutions...

            sanja Oleksandr Byelkin added a comment - It looks like the table was not emptied between rexecutions...

            The problem was that now with EXCEPT & INTERSECT it is not rue anymore that is Item on subquery is not assigned value the temporary table is empty (it is possible that something was added then deleted)

            sanja Oleksandr Byelkin added a comment - The problem was that now with EXCEPT & INTERSECT it is not rue anymore that is Item on subquery is not assigned value the temporary table is empty (it is possible that something was added then deleted)

            github tree: bb-10.3-MDEV-13723

            revision-id: 78a31db6b322ec0e63601ed471feaedc0ad2987c (mariadb-10.3.2-40-g78a31db6b32)
            parent(s): 06a9a366a3df18b02a97fce8e585229f0c01bd09
            author: Oleksandr Byelkin
            committer: Oleksandr Byelkin
            timestamp: 2017-11-10 11:22:51 +0100
            message:

            MDEV-13723: Server crashes in ha_heap::find_unique_row or Assertion `0' failed in st_select_lex_unit::optimize with INTERSECT

            With INTERSECT/EXCEPT fakt that subquery item of IN/ALL/ANY was not assigned value does not mean that temporary table used for calculating unit is empty (records could be deleted).

            —

            sanja Oleksandr Byelkin added a comment - github tree: bb-10.3- MDEV-13723 revision-id: 78a31db6b322ec0e63601ed471feaedc0ad2987c (mariadb-10.3.2-40-g78a31db6b32) parent(s): 06a9a366a3df18b02a97fce8e585229f0c01bd09 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2017-11-10 11:22:51 +0100 message: MDEV-13723 : Server crashes in ha_heap::find_unique_row or Assertion `0' failed in st_select_lex_unit::optimize with INTERSECT With INTERSECT/EXCEPT fakt that subquery item of IN/ALL/ANY was not assigned value does not mean that temporary table used for calculating unit is empty (records could be deleted). —

            People

              sanja Oleksandr Byelkin
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.