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

Server crashes in table_multi_eq_cond_selectivity with optimizer_use_condition_selectivity>1

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 10.0.11
    • 10.0.12
    • None
    • None

    Description

      Initially reported by Jippi on #maria IRC channel.

      The problem appeared in 10.0 tree with the following revision:

      revno: 4169
      revision-id: psergey@askmonty.org-20140425150454-dsk6kba2vn13gw50
      parent: psergey@askmonty.org-20140428174939-32ycvsxmajmfdjno
      committer: Sergey Petrunya <psergey@askmonty.org>
      branch nick: 10.0-cp
      timestamp: Fri 2014-04-25 19:04:54 +0400
      message:
        MDEV-6003: EITS: ref access, keypart2=const vs keypart2=expr - inconsistent filtered% value
        - Fix table_cond_selectivity() to work correctly for ref access 
          and "keypart2=const" case.

      #3  <signal handler called>
      #4  0x00000000006bdc35 in table_multi_eq_cond_selectivity (join=0x7f2dfb3b46c8, idx=0, s=0x7f2dfb3b5c40, rem_tables=1, keyparts=2, ref_keyuse_steps=0x7f2e05129c20) at 10.0/sql/sql_select.cc:7224
      #5  0x00000000006be413 in table_cond_selectivity (join=0x7f2dfb3b46c8, idx=0, s=0x7f2dfb3b5c40, rem_tables=1) at 10.0/sql/sql_select.cc:7458
      #6  0x00000000006beb4a in best_extension_by_limited_search (join=0x7f2dfb3b46c8, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=2) at 10.0/sql/sql_select.cc:7706
      #7  0x00000000006bd0e4 in greedy_search (join=0x7f2dfb3b46c8, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=2) at 10.0/sql/sql_select.cc:6897
      #8  0x00000000006bc6cc in choose_plan (join=0x7f2dfb3b46c8, join_tables=3) at 10.0/sql/sql_select.cc:6474
      #9  0x00000000006b6195 in make_join_statistics (join=0x7f2dfb3b46c8, tables_list=..., conds=0x7f2dfb1dd930, keyuse_array=0x7f2dfb3b49d0) at 10.0/sql/sql_select.cc:4018
      #10 0x00000000006ace96 in JOIN::optimize_inner (this=0x7f2dfb3b46c8) at 10.0/sql/sql_select.cc:1337
      #11 0x00000000006abe54 in JOIN::optimize (this=0x7f2dfb3b46c8) at 10.0/sql/sql_select.cc:1023
      #12 0x00000000006b39d3 in mysql_select (thd=0x7f2dfd341070, rref_pointer_array=0x7f2dfd3456d8, tables=0x7f2dfb1dc2f8, wild_num=1, fields=..., conds=0x0, og_num=1, order=0x7f2dfb1ddba8, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f2dfb1ddc98, unit=0x7f2dfd344d78, select_lex=0x7f2dfd345460) at 10.0/sql/sql_select.cc:3289
      #13 0x00000000006aa063 in handle_select (thd=0x7f2dfd341070, lex=0x7f2dfd344cb0, result=0x7f2dfb1ddc98, setup_tables_done_option=0) at 10.0/sql/sql_select.cc:372
      #14 0x000000000067eddd in execute_sqlcom_select (thd=0x7f2dfd341070, all_tables=0x7f2dfb1dc2f8) at 10.0/sql/sql_parse.cc:5263
      #15 0x00000000006771d4 in mysql_execute_command (thd=0x7f2dfd341070) at 10.0/sql/sql_parse.cc:2554
      #16 0x0000000000681567 in mysql_parse (thd=0x7f2dfd341070, rawbuf=0x7f2dfb1dc088 "select * from t1 inner join t2 on id = t1_id and (f2='qux' and f1='baz') order by dt DESC", length=89, parser_state=0x7f2e0512b610) at 10.0/sql/sql_parse.cc:6409
      #17 0x0000000000674475 in dispatch_command (command=COM_QUERY, thd=0x7f2dfd341070, packet=0x7f2dfd337071 "select * from t1 inner join t2 on id = t1_id and (f2='qux' and f1='baz') order by dt DESC", packet_length=89) at 10.0/sql/sql_parse.cc:1309
      #18 0x000000000067381a in do_command (thd=0x7f2dfd341070) at 10.0/sql/sql_parse.cc:1006
      #19 0x000000000078ed29 in do_handle_one_connection (thd_arg=0x7f2dfd341070) at 10.0/sql/sql_connect.cc:1379
      #20 0x000000000078ea7c in handle_one_connection (arg=0x7f2dfd341070) at 10.0/sql/sql_connect.cc:1293
      #21 0x0000000000cbde8e in pfs_spawn_thread (arg=0x7f2e028226f0) at 10.0/storage/perfschema/pfs.cc:1860
      #22 0x00007f2e04d16b50 in start_thread (arg=<optimized out>) at pthread_create.c:304
      #23 0x00007f2e0320ea7d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112

      Stack trace from:

      revision-id: monty@askmonty.org-20140604102300-i14vdl5m7v3tl6rb
      date: 2014-06-04 13:23:00 +0300
      build-date: 2014-06-05 16:39:57 +0400
      revno: 4221
      branch-nick: 10.0

      Test case:

      set optimizer_use_condition_selectivity=2;
       
      CREATE TABLE t1 (
        id varchar(40) COLLATE utf8_bin,
        dt datetime,
        PRIMARY KEY (id)
      );
       
      INSERT INTO t1 VALUES
      	('foo','2011-04-12 05:18:08'),
      	('bar','2013-09-19 11:37:03');
       
      CREATE TABLE t2 (
        t1_id varchar(40) COLLATE utf8_bin,
        f1 varchar(64),
        f2 varchar(1024),
        KEY (f1,f2(255))
      );
       
      INSERT INTO t2 VALUES ('foo','baz','qux'),('bar','baz','qux');
       
      select * from t1 inner join t2 on id = t1_id and (f2='qux' and f1='baz') order by dt DESC;

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.