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

InnoDB NOT IN Query Crash When One Item Is NULL

Details

    • 10.0.26

    Description

      I understand this is generally a malformed query and a rather esoteric set of circumstances, but it was identified by accident by one of our developers.

      Scenario: If an InnoDB column is indexed, and the query has a NOT IN with more than two parameters, one of which is "null", the database crashes. If the index isn't there, the database does not crash. If the table is not InnoDB, the database does not crash.

      Test Case (verified on both 10.0.19 and 10.0.25 for Centos 6 x86_64 in both our development and production environments):

      create table crash
      (col_a int(11),
       col_b varchar(10),
       key (col_b))
       engine=innodb;
      

      – Does not crash

      select *
      from crash
      where col_b not in (null);
      

      – Does not crash

      select *
      from crash
      where col_b not in (null, '');
      

      – Does not crash

      select * 
      from crash
      where col_b not in ('', '0');
      

      – Crash

      select *
      from crash
      where col_b not in (null, '', 'A');
      

      – Crash

      select *
      from crash
      where col_b not in (null, '', '0');
      

      Attachments

        Issue Links

          Activity

            hydrapolic Tomas Mozes added a comment -

            I can confirm this happening on Gentoo Linux, MariaDB 10.0.25, amd64.

            hydrapolic Tomas Mozes added a comment - I can confirm this happening on Gentoo Linux, MariaDB 10.0.25, amd64.

            Thanks for the report and the test case.

            Stack trace from 10.0 commit cee9ab9d85a8d75290b0d60bc7af26c8cf179a1d

            #2  0x000000000085b466 in handle_fatal_signal (sig=11) at /src/10.0/sql/signal_handler.cc:262
            #3  <signal handler called>
            #4  0x0000000000819650 in DTCollation::set_repertoire_from_charset (this=0x7fc286869fc0, cs=0x0) at /src/10.0/sql/item.h:101
            #5  0x00000000008196de in DTCollation::set (this=0x7fc286869fc0, collation_arg=0x0) at /src/10.0/sql/item.h:145
            #6  0x00000000008a36bc in in_string::Item_string_for_in_vector::set_value (this=0x7fc286869f50, str=0x7fc286869738) at /src/10.0/sql/item_cmpfunc.h:898
            #7  0x00000000008a3744 in in_string::value_to_item (this=0x7fc286869658, pos=2, item=0x7fc286869f50) at /src/10.0/sql/item_cmpfunc.h:914
            #8  0x0000000000977698 in get_func_mm_tree (param=0x7fc29b61a930, cond_func=0x7fc286868bf0, field=0x7fc286650990, value=0x0, cmp_type=STRING_RESULT, inv=true) at /src/10.0/sql/opt_range.cc:7738
            #9  0x0000000000977d3a in get_full_func_mm_tree (param=0x7fc29b61a930, cond_func=0x7fc286868bf0, field_item=0x7fc2868688b8, value=0x0, inv=true) at /src/10.0/sql/opt_range.cc:7961
            #10 0x000000000097872a in get_mm_tree (param=0x7fc29b61a930, cond=0x7fc286868bf0) at /src/10.0/sql/opt_range.cc:8114
            #11 0x000000000096db71 in SQL_SELECT::test_quick_select (this=0x7fc28686aa98, thd=0x7fc294be3b70, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at /src/10.0/sql/opt_range.cc:3139
            #12 0x00000000006aa1ad in get_quick_record_count (thd=0x7fc294be3b70, select=0x7fc28686aa98, table=0x7fc286964870, keys=0x7fc2868698e0, limit=18446744073709551615) at /src/10.0/sql/sql_select.cc:3345
            #13 0x00000000006ac21f in make_join_statistics (join=0x7fc286868de8, tables_list=..., conds=0x7fc286868bf0, keyuse_array=0x7fc286869100) at /src/10.0/sql/sql_select.cc:3953
            #14 0x00000000006a3264 in JOIN::optimize_inner (this=0x7fc286868de8) at /src/10.0/sql/sql_select.cc:1339
            #15 0x00000000006a2188 in JOIN::optimize (this=0x7fc286868de8) at /src/10.0/sql/sql_select.cc:1024
            #16 0x00000000006a9f24 in mysql_select (thd=0x7fc294be3b70, rref_pointer_array=0x7fc294be7e48, tables=0x7fc2868682b8, wild_num=1, fields=..., conds=0x7fc286868bf0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc286868dc8, unit=0x7fc294be74e0, select_lex=0x7fc294be7bd0) at /src/10.0/sql/sql_select.cc:3294
            #17 0x00000000006a0333 in handle_select (thd=0x7fc294be3b70, lex=0x7fc294be7418, result=0x7fc286868dc8, setup_tables_done_option=0) at /src/10.0/sql/sql_select.cc:373
            #18 0x0000000000674b85 in execute_sqlcom_select (thd=0x7fc294be3b70, all_tables=0x7fc2868682b8) at /src/10.0/sql/sql_parse.cc:5293
            #19 0x000000000066d09f in mysql_execute_command (thd=0x7fc294be3b70) at /src/10.0/sql/sql_parse.cc:2562
            #20 0x00000000006777cd in mysql_parse (thd=0x7fc294be3b70, rawbuf=0x7fc286868088 "select *\nfrom crash\nwhere col_b not in (null, '', 'A')", length=54, parser_state=0x7fc29b61c650) at /src/10.0/sql/sql_parse.cc:6574
            #21 0x000000000066a309 in dispatch_command (command=COM_QUERY, thd=0x7fc294be3b70, packet=0x7fc289382071 "select *\nfrom crash\nwhere col_b not in (null, '', 'A')", packet_length=54) at /src/10.0/sql/sql_parse.cc:1308
            #22 0x00000000006695d6 in do_command (thd=0x7fc294be3b70) at /src/10.0/sql/sql_parse.cc:998
            #23 0x0000000000787cb2 in do_handle_one_connection (thd_arg=0x7fc294be3b70) at /src/10.0/sql/sql_connect.cc:1378
            #24 0x0000000000787a24 in handle_one_connection (arg=0x7fc294be3b70) at /src/10.0/sql/sql_connect.cc:1293
            #25 0x0000000000df561c in pfs_spawn_thread (arg=0x7fc2892ca4f0) at /src/10.0/storage/perfschema/pfs.cc:1860
            #26 0x00007fc29b2970a4 in start_thread () from /lib64/libpthread.so.0
            #27 0x00007fc29962b04d in clone () from /lib64/libc.so.6
            

            The problem was introduced by this revision:

            commit fbaaf3688d5fac4b1b867155a9f09575367c0f98
            Author: Alexander Barkov <bar@mariadb.org>
            Date:   Wed Sep 3 01:56:21 2014 +0400
             
                Moving Item::str_value from public to protected.
            

            elenst Elena Stepanova added a comment - Thanks for the report and the test case. Stack trace from 10.0 commit cee9ab9d85a8d75290b0d60bc7af26c8cf179a1d #2 0x000000000085b466 in handle_fatal_signal (sig=11) at /src/10.0/sql/signal_handler.cc:262 #3 <signal handler called> #4 0x0000000000819650 in DTCollation::set_repertoire_from_charset (this=0x7fc286869fc0, cs=0x0) at /src/10.0/sql/item.h:101 #5 0x00000000008196de in DTCollation::set (this=0x7fc286869fc0, collation_arg=0x0) at /src/10.0/sql/item.h:145 #6 0x00000000008a36bc in in_string::Item_string_for_in_vector::set_value (this=0x7fc286869f50, str=0x7fc286869738) at /src/10.0/sql/item_cmpfunc.h:898 #7 0x00000000008a3744 in in_string::value_to_item (this=0x7fc286869658, pos=2, item=0x7fc286869f50) at /src/10.0/sql/item_cmpfunc.h:914 #8 0x0000000000977698 in get_func_mm_tree (param=0x7fc29b61a930, cond_func=0x7fc286868bf0, field=0x7fc286650990, value=0x0, cmp_type=STRING_RESULT, inv=true) at /src/10.0/sql/opt_range.cc:7738 #9 0x0000000000977d3a in get_full_func_mm_tree (param=0x7fc29b61a930, cond_func=0x7fc286868bf0, field_item=0x7fc2868688b8, value=0x0, inv=true) at /src/10.0/sql/opt_range.cc:7961 #10 0x000000000097872a in get_mm_tree (param=0x7fc29b61a930, cond=0x7fc286868bf0) at /src/10.0/sql/opt_range.cc:8114 #11 0x000000000096db71 in SQL_SELECT::test_quick_select (this=0x7fc28686aa98, thd=0x7fc294be3b70, keys_to_use=..., prev_tables=0, limit=18446744073709551615, force_quick_range=false, ordered_output=false) at /src/10.0/sql/opt_range.cc:3139 #12 0x00000000006aa1ad in get_quick_record_count (thd=0x7fc294be3b70, select=0x7fc28686aa98, table=0x7fc286964870, keys=0x7fc2868698e0, limit=18446744073709551615) at /src/10.0/sql/sql_select.cc:3345 #13 0x00000000006ac21f in make_join_statistics (join=0x7fc286868de8, tables_list=..., conds=0x7fc286868bf0, keyuse_array=0x7fc286869100) at /src/10.0/sql/sql_select.cc:3953 #14 0x00000000006a3264 in JOIN::optimize_inner (this=0x7fc286868de8) at /src/10.0/sql/sql_select.cc:1339 #15 0x00000000006a2188 in JOIN::optimize (this=0x7fc286868de8) at /src/10.0/sql/sql_select.cc:1024 #16 0x00000000006a9f24 in mysql_select (thd=0x7fc294be3b70, rref_pointer_array=0x7fc294be7e48, tables=0x7fc2868682b8, wild_num=1, fields=..., conds=0x7fc286868bf0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7fc286868dc8, unit=0x7fc294be74e0, select_lex=0x7fc294be7bd0) at /src/10.0/sql/sql_select.cc:3294 #17 0x00000000006a0333 in handle_select (thd=0x7fc294be3b70, lex=0x7fc294be7418, result=0x7fc286868dc8, setup_tables_done_option=0) at /src/10.0/sql/sql_select.cc:373 #18 0x0000000000674b85 in execute_sqlcom_select (thd=0x7fc294be3b70, all_tables=0x7fc2868682b8) at /src/10.0/sql/sql_parse.cc:5293 #19 0x000000000066d09f in mysql_execute_command (thd=0x7fc294be3b70) at /src/10.0/sql/sql_parse.cc:2562 #20 0x00000000006777cd in mysql_parse (thd=0x7fc294be3b70, rawbuf=0x7fc286868088 "select *\nfrom crash\nwhere col_b not in (null, '', 'A')", length=54, parser_state=0x7fc29b61c650) at /src/10.0/sql/sql_parse.cc:6574 #21 0x000000000066a309 in dispatch_command (command=COM_QUERY, thd=0x7fc294be3b70, packet=0x7fc289382071 "select *\nfrom crash\nwhere col_b not in (null, '', 'A')", packet_length=54) at /src/10.0/sql/sql_parse.cc:1308 #22 0x00000000006695d6 in do_command (thd=0x7fc294be3b70) at /src/10.0/sql/sql_parse.cc:998 #23 0x0000000000787cb2 in do_handle_one_connection (thd_arg=0x7fc294be3b70) at /src/10.0/sql/sql_connect.cc:1378 #24 0x0000000000787a24 in handle_one_connection (arg=0x7fc294be3b70) at /src/10.0/sql/sql_connect.cc:1293 #25 0x0000000000df561c in pfs_spawn_thread (arg=0x7fc2892ca4f0) at /src/10.0/storage/perfschema/pfs.cc:1860 #26 0x00007fc29b2970a4 in start_thread () from /lib64/libpthread.so.0 #27 0x00007fc29962b04d in clone () from /lib64/libc.so.6 The problem was introduced by this revision: commit fbaaf3688d5fac4b1b867155a9f09575367c0f98 Author: Alexander Barkov <bar@mariadb.org> Date: Wed Sep 3 01:56:21 2014 +0400   Moving Item::str_value from public to protected.

            The problem is also repeatable with MyISAM (providing that a few records are inserted).

            DROP TABLE IF EXISTS t1;
            CREATE TABLE t1
            (
              a INT(11),
              b VARCHAR(10),
              KEY (b)
            ) ENGINE=MyISAM;
            INSERT INTO t1 VALUES (1,'x'),(2,'y'),(3,'z');                      
            SELECT * FROM t1 WHERE b NOT IN (NULL, '', 'A');
            

            bar Alexander Barkov added a comment - The problem is also repeatable with MyISAM (providing that a few records are inserted). DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a INT (11), b VARCHAR (10), KEY (b) ) ENGINE=MyISAM; INSERT INTO t1 VALUES (1, 'x' ),(2, 'y' ),(3, 'z' ); SELECT * FROM t1 WHERE b NOT IN ( NULL , '' , 'A' );

            People

              bar Alexander Barkov
              bmoore29 Bryce Moore
              Votes:
              0 Vote for this issue
              Watchers:
              5 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.