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

NOT IN subquery construct crashing 10.1 and up

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Stalled (View Workflow)
    • Priority: Critical
    • Resolution: Unresolved
    • Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6, 10.1.48, 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4
    • Fix Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
    • Component/s: Optimizer
    • Labels:
      None

      Description

      The following works on MySQL 5.6 and MariaDB 10.0, but crashes later MariaDB releases:

      DROP TABLE IF EXISTS t1;
        
      CREATE TABLE `t1` (
      `id` int PRIMARY KEY,
      `i1` int,
      `i2` int,
      KEY (`i1`,`i2`)
      ) ENGINE=InnoDB;
       
      SELECT id
        FROM t1
       WHERE t1.id NOT IN (
                            SELECT s1.id
                              FROM t1 s1
                             WHERE ( ( s1.i1 = 1 OR s1.i1 = 1 ) AND s1.i2 = 1 )
                                OR ( s1.i1 = 1 AND s1.i2 = NULL )
                          )
      ;
      

      As the crash already happens in the optimizer stage it also crashes the server when trying to run "EXPLAIN SELECT"

      This seems to be independent from any optimizer_switch settings as even with all individual switch settings set to "off" it still crashes.

      MariaDB 10.6.4 gdb backtrace:

      #0  0x00007f39c99e055b in kill () at ../sysdeps/unix/syscall-template.S:78
      #1  0x0000556f4122c25e in handle_fatal_signal (sig=11) at ./sql/signal_handler.cc:356
      #2  <signal handler called>
      #3  Item_equal_iterator<List_iterator_fast, Item>::get_curr_field (this=<synthetic pointer>) at ./sql/item_cmpfunc.h:3488
      #4  Item_equal::contains (this=this@entry=0x7f39741efa00, field=0x7f39741e4868) at ./sql/item_cmpfunc.cc:6775
      #5  0x0000556f4124225f in Item_field::find_item_equal (this=0x7f3974014dc8, cond_equal=0x7f3974015af8) at ./sql/item.cc:6194
      #6  0x0000556f4103d8cc in eliminate_item_equal (thd=0x7f3974000c58, cond=0x0, upper_levels=0x7f3974015af8, item_equal=0x7f39741edec0) at ./sql/sql_select.cc:16049
      #7  0x0000556f4103e4bb in substitute_for_best_equal_field (thd=0x7f3974000c58, context_tab=0x1, cond=0x7f39741edec0, cond_equal=<optimized out>, table_join_idx=0x7f39741ee678, do_substitution=<optimized out>) at ./sql/sql_select.cc:16356
      #8  0x0000556f4103e536 in substitute_for_best_equal_field (thd=0x7f3974000c58, context_tab=0x1, cond=0x7f3974015558, cond_equal=0x7f39741efda8, table_join_idx=<optimized out>, do_substitution=<optimized out>) at ./sql/sql_select.cc:16274
      #9  0x0000556f4103e536 in substitute_for_best_equal_field (thd=0x7f3974000c58, context_tab=0x1, cond=0x7f39741efcd0, cond_equal=0x7f39741efda8, table_join_idx=<optimized out>, do_substitution=<optimized out>) at ./sql/sql_select.cc:16274
      #10 0x0000556f41060f73 in JOIN::optimize_stage2 (this=0x7f3974018108) at ./sql/sql_select.cc:2599
      #11 0x0000556f41064a4a in JOIN::optimize_inner (this=0x7f3974018108) at ./sql/sql_select.cc:2479
      #12 0x0000556f41066ad3 in JOIN::optimize (this=this@entry=0x7f3974018108) at ./sql/sql_select.cc:1809
      #13 0x0000556f40fd7466 in st_select_lex::optimize_unflattened_subqueries (this=0x7f39740132c8, const_only=const_only@entry=false) at ./sql/sql_lex.cc:4937
      #14 0x0000556f41150ae2 in JOIN::optimize_unflattened_subqueries (this=this@entry=0x7f3974017988) at ./sql/opt_subselect.cc:5567
      #15 0x0000556f410620b6 in JOIN::optimize_stage2 (this=<optimized out>) at ./sql/sql_select.cc:3061
      #16 0x0000556f41064a4a in JOIN::optimize_inner (this=0x7f3974017988) at ./sql/sql_select.cc:2479
      #17 0x0000556f41066ad3 in JOIN::optimize (this=this@entry=0x7f3974017988) at ./sql/sql_select.cc:1809
      #18 0x0000556f41066b97 in mysql_select (thd=0x7f3974000c58, tables=0x7f39740138a8, fields=..., conds=0x7f3974016f58, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=<optimized out>, result=0x7f3974017960, unit=0x7f3974004e00, select_lex=0x7f39740132c8) at ./sql/sql_select.cc:4979
      #19 0x0000556f410673b7 in handle_select (thd=thd@entry=0x7f3974000c58, lex=lex@entry=0x7f3974004d38, result=result@entry=0x7f3974017960, setup_tables_done_option=setup_tables_done_option@entry=0) at ./sql/sql_select.cc:545
      #20 0x0000556f40ff7431 in execute_sqlcom_select (thd=0x7f3974000c58, all_tables=0x7f39740138a8) at ./sql/sql_parse.cc:6256
      #21 0x0000556f4100516e in mysql_execute_command (thd=0x7f3974000c58, is_called_from_prepared_stmt=<optimized out>) at ./sql/sql_parse.cc:3946
      #22 0x0000556f40ff2367 in mysql_parse (thd=0x7f3974000c58, rawbuf=<optimized out>, length=<optimized out>, parser_state=<optimized out>) at ./sql/sql_parse.cc:8030
      #23 0x0000556f40ffe2ed in dispatch_command (command=COM_QUERY, thd=0x7f3974000c58, packet=<optimized out>, packet_length=<optimized out>, blocking=<optimized out>) at ./sql/sql_class.h:1357
      #24 0x0000556f41000208 in do_command (thd=0x7f3974000c58, blocking=blocking@entry=true) at ./sql/sql_parse.cc:1404
      #25 0x0000556f4110f867 in do_handle_one_connection (connect=<optimized out>, put_in_cache=true) at ./sql/sql_connect.cc:1418
      #26 0x0000556f4110fbbd in handle_one_connection (arg=arg@entry=0x556f4471ee08) at ./sql/sql_connect.cc:1312
      #27 0x0000556f4146d17d in pfs_spawn_thread (arg=0x556f446a1188) at ./storage/perfschema/pfs.cc:2201
      #28 0x00007f39c9ece609 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #29 0x00007f39c9abc293 in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

        Attachments

          Activity

            People

            Assignee:
            igor Igor Babaev
            Reporter:
            hholzgra Hartmut Holzgraefe
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration