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

Exists2In: Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed in String* Field_varstring::val_str(String*, String*)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      I could not reproduce it without exists_to_in, but possibly it's just because I cannot hit the desired plan. However, with all optimizer_switch keys off, and only 'in_to_exists=on', I am getting visually a very similar plan with and without exists_to_in, but the assertion only fails with exists_to_in=on. Explains are below.

      Version: '5.5.21-MariaDB-debug-log'  socket: '/home/elenst/lp-5.5-exists2in/debug/mysql-test/var/tmp/mysqld.1.sock'  port: 16020  Source distribution
      mysqld: /home/elenst/lp-5.5-exists2in/sql/field.cc:6631: virtual String* Field_varstring::val_str(String*, String*): Assertion `!table || (!table->read_set || bitmap_is_set(table->read_set, field_index))' failed.
       
      #8  0xb7567014 in __assert_fail () from /lib/libc.so.6
      #9  0x083a99d3 in Field_varstring::val_str (this=0x96f51b8, val_buffer=0x96edf88, val_ptr=0x96fd490)
          at /home/elenst/lp-5.5-exists2in/sql/field.cc:6631
      #10 0x083d0dc7 in Item_field::val_str (this=0x96fd480, str=0x96edf88)
          at /home/elenst/lp-5.5-exists2in/sql/item.cc:2445
      #11 0x083dd199 in Item_direct_ref::val_str (this=0x96fe220, tmp=0x96edf88)
          at /home/elenst/lp-5.5-exists2in/sql/item.cc:7306
      #12 0x083eb788 in Arg_comparator::compare_string (this=0x96edf34)
          at /home/elenst/lp-5.5-exists2in/sql/item_cmpfunc.cc:976
      #13 0x083fa204 in Arg_comparator::compare (this=0x96edf34)
          at /home/elenst/lp-5.5-exists2in/sql/item_cmpfunc.h:78
      #14 0x083edf8e in Item_func_le::val_int (this=0x96edeb0)
          at /home/elenst/lp-5.5-exists2in/sql/item_cmpfunc.cc:1907
      #15 0x08284cb3 in evaluate_join_record (join=0x970c398, join_tab=0x9713f90, error=0)
          at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:15681
      #16 0x08284942 in sub_select (join=0x970c398, join_tab=0x9713f90, end_of_records=false)
          at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:15587
      #17 0x0828425e in do_select (join=0x970c398, fields=0x96ece54, table=0x0, procedure=0x0)
          at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:15258
      #18 0x08267c3d in JOIN::exec (this=0x970c398) at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:2739
      #19 0x0844f132 in subselect_single_select_engine::exec (this=0x96ee380)
          at /home/elenst/lp-5.5-exists2in/sql/item_subselect.cc:3365
      #20 0x0844782f in Item_subselect::exec (this=0x96fe688)
          at /home/elenst/lp-5.5-exists2in/sql/item_subselect.cc:600
      #21 0x08447d59 in Item_in_subselect::exec (this=0x96fe688)
          at /home/elenst/lp-5.5-exists2in/sql/item_subselect.cc:760
      #22 0x08449a38 in Item_in_subselect::val_bool (this=0x96fe688)
          at /home/elenst/lp-5.5-exists2in/sql/item_subselect.cc:1481
      #23 0x081aa34c in Item::val_bool_result (this=0x96fe688) at /home/elenst/lp-5.5-exists2in/sql/item.h:952
      #24 0x083ed7b8 in Item_in_optimizer::val_int (this=0x96fe3f8)
          at /home/elenst/lp-5.5-exists2in/sql/item_cmpfunc.cc:1739
      #25 0x083cb602 in Item::val_bool (this=0x96fe3f8) at /home/elenst/lp-5.5-exists2in/sql/item.cc:214
      #26 0x083f6560 in Item_cond_and::val_int (this=0x9713410)
          at /home/elenst/lp-5.5-exists2in/sql/item_cmpfunc.cc:4599
      #27 0x08284cb3 in evaluate_join_record (join=0x96ff788, join_tab=0x9713018, error=0)
          at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:15681
      #28 0x08284942 in sub_select (join=0x96ff788, join_tab=0x9713018, end_of_records=false)
          at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:15587
      #29 0x0828425e in do_select (join=0x96ff788, fields=0x963f3d0, table=0x0, procedure=0x0)
          at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:15258
      #30 0x08267c3d in JOIN::exec (this=0x96ff788) at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:2739
      #31 0x08268416 in mysql_select (thd=0x963d900, rref_pointer_array=0x963f478, tables=0x96ec9b0, wild_num=0, 
          fields=..., conds=0x96ee2b8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, 
          select_options=2147748608, result=0x96fdcf8, unit=0x963eea0, select_lex=0x963f33c)
          at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:2959
      #32 0x0826016a in handle_select (thd=0x963d900, lex=0x963ee3c, result=0x96fdcf8, setup_tables_done_option=0)
          at /home/elenst/lp-5.5-exists2in/sql/sql_select.cc:308
      #33 0x0823c361 in execute_sqlcom_select (thd=0x963d900, all_tables=0x96ec9b0)
          at /home/elenst/lp-5.5-exists2in/sql/sql_parse.cc:4614
      #34 0x08235308 in mysql_execute_command (thd=0x963d900) at /home/elenst/lp-5.5-exists2in/sql/sql_parse.cc:218
      #35 0x0823e93c in mysql_parse (thd=0x963d900, 
          rawbuf=0x96ec7e0 "SELECT c FROM v  \nWHERE EXISTS ( \nSELECT * FROM t1, t2 \nWHERE a <= v.d AND b = v.b \", length=85, parser_state=0xad1d6db8) at /home/elenst/lp-5.5-exists2in/sql/sql_parse.cc:5728
      #36 0x08232dd2 in dispatch_command (command=COM_QUERY, thd=0x963d900, packet=0x96e2a71 "", packet_length=85)
          at /home/elenst/lp-5.5-exists2in/sql/sql_parse.cc:1054
      #37 0x08232297 in do_command (thd=0x963d900) at /home/elenst/lp-5.5-exists2in/sql/sql_parse.cc:793
      #38 0x0831afae in do_handle_one_connection (thd_arg=0x963d900)
          at /home/elenst/lp-5.5-exists2in/sql/sql_connect.cc:1252
      #39 0x0831aafc in handle_one_connection (arg=0x963d900)
          at /home/elenst/lp-5.5-exists2in/sql/sql_connect.cc:1167
      #40 0x0853cfcb in pfs_spawn_thread (arg=0x96c9300)
          at /home/elenst/lp-5.5-exists2in/storage/perfschema/pfs.cc:1015

      bzr version-info

      revision-id: sanja@montyprogram.com-20120214112903-w2bmvp058v4odysy
      date: 2012-02-14 13:29:03 +0200
      build-date: 2012-02-20 22:30:24 +0400
      revno: 3268

      EXPLAIN with in_to_exists=ON, everything else OFF (no failure)

      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
      1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
      2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
      2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note    1276    Field or reference 'v.d' of SELECT #2 was resolved in SELECT #1
      Note    1276    Field or reference 'v.b' of SELECT #2 was resolved in SELECT #1
      Note    1003    select `test`.`t2`.`c` AS `c` from `test`.`t2` where (exists(select 1 from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` <= `test`.`t2`.`d`) and (`test`.`t2`.`b` = `test`.`t2`.`b`))) and (`test`.`t2`.`b` < 1))

      EXPLAIN with in_to_exists=ON, exists_to_in=ON, everything else OFF (assertion failure)

      id      select_type     table   type    possible_keys   key     key_len ref     rows    filtered        Extra
      1       PRIMARY t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
      2       DEPENDENT SUBQUERY      t1      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where
      2       DEPENDENT SUBQUERY      t2      ALL     NULL    NULL    NULL    NULL    2       100.00  Using where; Using join buffer (flat, BNL join)
      Warnings:
      Note    1276    Field or reference 'v.d' of SELECT #2 was resolved in SELECT #1
      Note    1276    Field or reference 'v.b' of SELECT #2 was resolved in SELECT #1
      Note    1003    select `test`.`t2`.`c` AS `c` from `test`.`t2` where (<in_optimizer>(`test`.`t2`.`b`,<exists>(select `test`.`t2`.`b` from `test`.`t1` join `test`.`t2` where ((`test`.`t1`.`a` <= `test`.`t2`.`d`) and (<cache>(`test`.`t2`.`b`) = `test`.`t2`.`b`)))) and (`test`.`t2`.`b` < 1))

      optimizer_switch for the test case:

      index_merge=off,index_merge_union=off,index_merge_sort_union=off,index_merge_intersection=off,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=off,derived_merge=off,derived_with_keys=off,firstmatch=off,loosescan=off,materialization=off,in_to_exists=on,semijoin=off,partial_match_rowid_merge=off,partial_match_table_scan=off,subquery_cache=off,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=off,join_cache_hashed=off,join_cache_bka=off,optimize_join_buffer_size=off,table_elimination=off,exists_to_in=on

      With a bit more complicated queries the assertion failure is reproducible with the default optimizer_switch + exists_to_in=ON.

      Test case:

      SET optimizer_switch = REPLACE( @@optimizer_switch, '=on', '=off' );
      SET optimizer_switch='in_to_exists=on,exists_to_in=on';
       
      CREATE TABLE t1 ( a VARCHAR(1) );
      INSERT INTO t1 VALUES ('k'),('m');
       
      CREATE TABLE t2 ( b INT,
        c VARCHAR(1),
        d VARCHAR(1) NOT NULL );
      		
      INSERT INTO t2 VALUES 
        (4,'j','j'),(6,'v','v');
       
      CREATE ALGORITHM=MERGE VIEW v AS SELECT * FROM t2 WHERE b < 1;
       
      SELECT c FROM v  
      WHERE EXISTS ( 
        SELECT * FROM t1, t2 
        WHERE a <= v.d AND b = v.b 
      );

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated:
                Resolved: