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

Server crashes in Item_func_trig_cond::val_int with FROM and NOT IN subqueries, LEFT JOIN, derived_merge+in_to_exists

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5.29, 5.3.12
    • 5.5.29, 5.3.12
    • None
    • None

    Description

      Stack trace from maria/5.3 revno 3612:

      #3  <signal handler called>
      #4  0x000000000061ec55 in Item_func_trig_cond::val_int (this=0x3c2cd48) at item_cmpfunc.h:480
      #5  0x000000000057bce6 in Item::val_bool (this=0x3c2cd48) at item.cc:199
      #6  0x00000000005d726b in Item_cond_and::val_int (this=0x3c563a8) at item_cmpfunc.cc:4550
      #7  0x0000000000735cdf in end_send_group (join=0x3c24088, join_tab=0x3c56b78, end_of_records=true) at sql_select.cc:16886
      #8  0x0000000000732b12 in sub_select (join=0x3c24088, join_tab=0x3c56858, end_of_records=true) at sql_select.cc:15662
      #9  0x00000000007325e7 in do_select (join=0x3c24088, fields=0x3c24468, table=0x0, procedure=0x0) at sql_select.cc:15384
      #10 0x000000000071303d in JOIN::exec (this=0x3c24088) at sql_select.cc:2764
      #11 0x0000000000618b9e in subselect_single_select_engine::exec (this=0x3c1f9f8) at item_subselect.cc:3141
      #12 0x00000000006112f5 in Item_subselect::exec (this=0x3c1f7f8) at item_subselect.cc:588
      #13 0x0000000000611837 in Item_in_subselect::exec (this=0x3c1f7f8) at item_subselect.cc:756
      #14 0x0000000000613da9 in Item_in_subselect::val_bool (this=0x3c1f7f8) at item_subselect.cc:1577
      #15 0x0000000000597dcd in Item::val_bool_result (this=0x3c1f7f8) at item.h:856
      #16 0x00000000005cd863 in Item_in_optimizer::val_int (this=0x3c24c38) at item_cmpfunc.cc:1731
      #17 0x000000000057bce6 in Item::val_bool (this=0x3c24c38) at item.cc:199
      #18 0x00000000005c97d7 in Item_func_not::val_int (this=0x3c19058) at item_cmpfunc.cc:332
      #19 0x000000000057bce6 in Item::val_bool (this=0x3c19058) at item.cc:199
      #20 0x00000000005d726b in Item_cond_and::val_int (this=0x3c2b4d8) at item_cmpfunc.cc:4550
      #21 0x000000000061ec89 in Item_func_trig_cond::val_int (this=0x3c2b6a8) at item_cmpfunc.h:480
      #22 0x000000000057bce6 in Item::val_bool (this=0x3c2b6a8) at item.cc:199
      #23 0x00000000005d726b in Item_cond_and::val_int (this=0x3c2b798) at item_cmpfunc.cc:4550
      #24 0x00000000006df0a4 in SQL_SELECT::skip_record (this=0x3c2af38, thd=0x3b9d670) at opt_range.h:924
      #25 0x00000000006e00d1 in JOIN_CACHE::check_match (this=0x3c2b968, rec_ptr=0x3c353b1 "") at sql_join_cache.cc:2445
      #26 0x00000000006db980 in JOIN_CACHE::generate_full_extensions (this=0x3c2b968, rec_ptr=0x3c353b1 "") at sql_join_cache.cc:2388
      #27 0x00000000006db75b in JOIN_CACHE::join_matching_records (this=0x3c2b968, skip_last=false) at sql_join_cache.cc:2288
      #28 0x00000000006db182 in JOIN_CACHE::join_records (this=0x3c2b968, skip_last=false) at sql_join_cache.cc:2085
      #29 0x00000000007328fd in sub_select_cache (join=0x3c22c68, join_tab=0x3c2a298, end_of_records=true) at sql_select.cc:15500
      #30 0x0000000000732b12 in sub_select (join=0x3c22c68, join_tab=0x3c29f78, end_of_records=true) at sql_select.cc:15662
      #31 0x00000000007325e7 in do_select (join=0x3c22c68, fields=0x3ba0248, table=0x0, procedure=0x0) at sql_select.cc:15384
      #32 0x000000000071303d in JOIN::exec (this=0x3c22c68) at sql_select.cc:2764
      #33 0x00000000007138c9 in mysql_select (thd=0x3b9d670, rref_pointer_array=0x3ba03d8, tables=0x3c1cd38, wild_num=1, fields=..., conds=0x3c212a8, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147764736, result=0x3c22c18, unit=0x3b9fc08, select_lex=0x3ba0130) at sql_select.cc:2985
      #34 0x000000000070a283 in handle_select (thd=0x3b9d670, lex=0x3b9fb68, result=0x3c22c18, setup_tables_done_option=0) at sql_select.cc:286
      #35 0x00000000006957a3 in execute_sqlcom_select (thd=0x3b9d670, all_tables=0x3c1cd38) at sql_parse.cc:5157
      #36 0x000000000068c562 in mysql_execute_command (thd=0x3b9d670) at sql_parse.cc:2290
      #37 0x00000000006981fb in mysql_parse (thd=0x3b9d670, rawbuf=0x3c0e738 "SELECT * FROM t1 LEFT JOIN (  \nSELECT * FROM t2 WHERE ( pk, pk ) NOT IN ( \nSELECT MIN(b), SUM(pk) FROM t1\n) \n) AS alias1 ON (a = c) \nWHERE b IS NULL OR a < 'u'", length=159, found_semicolon=0x7f190a76e7e0) at sql_parse.cc:6158
      #38 0x0000000000689d08 in dispatch_command (command=COM_QUERY, thd=0x3b9d670, packet=0x3be6c31 "", packet_length=159) at sql_parse.cc:1228
      #39 0x000000000068901c in do_command (thd=0x3b9d670) at sql_parse.cc:923
      #40 0x0000000000685a77 in handle_one_connection (arg=0x3b9d670) at sql_connect.cc:1231
      #41 0x00007f1913f79efc in start_thread (arg=0x7f190a76f700) at pthread_create.c:304
      #42 0x00007f1913520f4d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

      The problem appeared in maria/5.3 with the following revision:

      revno: 3608
      revision-id: sanja@askmonty.org-20121228124146-bk5tzqdvi4yukdpj
      parent: timour@askmonty.org-20121219135657-mbje1blpgw2qsw7j
      committer: sanja@askmonty.org
      branch nick: work-maria-5.3-MDEV-3873
      timestamp: Fri 2012-12-28 14:41:46 +0200
      message:
        MDEV-3873 & MDEV-3876 & MDEV-3912 : Wrong result (extra rows) with ALL subquery
        from a MERGE view.

      Reproducible on maria/5.3, maria/5.5.
      Minimal optimizer_switch: derived_merge=on,in_to_exists=on
      Full optimizer_switch (default):

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

      EXPLAIN (with the default optimizer_switch):

      EXPLAIN EXTENDED
      SELECT * FROM t1 LEFT JOIN (  
      SELECT * FROM t2 WHERE ( pk, pk ) NOT IN ( 
      SELECT MIN(b), SUM(pk) FROM t1
      ) 
      ) AS alias1 ON (a = c) 
      WHERE b IS NULL OR a < 'u';
      id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
      1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	2	100.00	Using where; Using join buffer (flat, BNL join)
      3	DEPENDENT SUBQUERY	t1	ALL	NULL	NULL	NULL	NULL	2	100.00	
      Warnings:
      Note	1276	Field or reference 'test.t2.b' of SELECT #3 was resolved in SELECT #2
      Note	1276	Field or reference 'test.t2.pk' of SELECT #3 was resolved in SELECT #2
      Note	1003	select `test`.`t1`.`a` AS `a`,`test`.`t2`.`pk` AS `pk`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` left join (`test`.`t2`) on(((not(<in_optimizer>((`test`.`t2`.`pk`,`test`.`t2`.`pk`),<exists>(select min(`test`.`t2`.`b`),sum(`test`.`t2`.`pk`) from `test`.`t1` having (trigcond(((<cache>(`test`.`t2`.`pk`) = min(`test`.`t2`.`b`)) or isnull(min(`test`.`t2`.`b`)))) and trigcond(((<cache>(`test`.`t2`.`pk`) = sum(`test`.`t2`.`pk`)) or isnull(sum(`test`.`t2`.`pk`)))) and trigcond(<is_not_null_test>(min(`test`.`t2`.`b`))) and trigcond(<is_not_null_test>(sum(`test`.`t2`.`pk`)))))))) and (`test`.`t2`.`c` = `test`.`t1`.`a`))) where (isnull(`test`.`t2`.`b`) or (`test`.`t1`.`a` < 'u'))
       

      Test case:

      SET optimizer_switch = 'derived_merge=on,in_to_exists=on';
       
      CREATE TABLE t1 (a VARCHAR(1)) ENGINE=MyISAM;
      INSERT INTO t1 VALUES ('x'),('d');
       
      CREATE TABLE t2 (pk INT PRIMARY KEY, b INT, c VARCHAR(1))  ENGINE=MyISAM;
      INSERT INTO t2 VALUES (1,2,'v'),(2,150,'v');
       
       
      SELECT * FROM t1 LEFT JOIN (  
        SELECT * FROM t2 WHERE ( pk, pk ) NOT IN ( 
          SELECT MIN(b), SUM(pk) FROM t1
        ) 
      ) AS alias1 ON (a = c) 
      WHERE b IS NULL OR a < 'u';

      Attachments

        Activity

          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.