Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.9, 10.3(EOL)
    • 10.3.10
    • Optimizer
    • None
    • Crash is reproducible on server versions 10.3.5 to 10.3.9 inclusive.

    Description

      Run these statements and most of the time the server crashes on the SELECT statement:

      SET optimizer_switch='split_materialized=on';
       
      CREATE DATABASE IF NOT EXISTS `testdb` DEFAULT CHARACTER SET utf8;
      USE testdb;
       
      DROP TABLE IF EXISTS `t`;
      CREATE TABLE `t` (
        `tId` bigint(20) NOT NULL,
        `tgId` bigint(20) DEFAULT NULL,
        `sgId` bigint(20) DEFAULT NULL,
        PRIMARY KEY (`tId`),
        KEY `fk_t_tgId` (`tgId`),
        KEY `sgId` (`sgId`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO t (tId, tgId, sgId)
      VALUES ('1', NULL, '1');
       
      DROP TABLE IF EXISTS `sgh`;
      CREATE TABLE `sgh` (
        `sgId` bigint(20) NOT NULL,
        `psd` timestamp(3) NOT NULL DEFAULT '0000-00-00 00:00:00.000',
        PRIMARY KEY (`sgId`,`psd`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO sgh (sgId, psd) VALUES
      ('1', '2018-08-16 07:30:00.000');
       
      DROP TABLE IF EXISTS `l`;
      CREATE TABLE `l` (
        `lId` bigint(20) NOT NULL AUTO_INCREMENT,
        `a` bigint(20) NOT NULL,
        PRIMARY KEY (`lId`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
      INSERT INTO l (a) VALUES (1);
       
      SELECT lId, sdate
      FROM
      (
        SELECT l.lId, sgh.psd AS sdate, t.sgId
        FROM l 
        INNER JOIN t ON (l.a=t.tId)
        INNER JOIN sgh ON sgh.sgId=t.sgId 
        GROUP BY l.lId, sdate, t.sgId
      ) us
      INNER JOIN sgh ON sgh.sgId=us.sgId AND sgh.psd=sdate
      GROUP BY lId, sdate;
      

      If you change the optization switch to this:

      SET optimizer_switch='split_materialized=off';
      

      You get the single (expected) row returned.

      The split_materialized switch was added 10.3.4 and the code works in that version regardless of its value, a change in 10.3.5 seems to have broken it.

      Note the crash occurs on Windows and Ubuntu host machines. I have attached the crash output from my syslog.

      Regards,
      Mark.

      Attachments

        Activity

          alice Alice Sherepa added a comment -

          Thanks for the report and the test case!
          Reproducible on Mariadb 10.3 with Innodb.

          CREATE TABLE t1 ( id1 int, i1 int, id2 int, PRIMARY KEY (id1), KEY (i1), KEY (id2)) ENGINE=InnoDB;
          INSERT INTO t1 VALUES (1,1,1);
           
          CREATE TABLE t2 (id2 int, i2 int) ENGINE=InnoDB;
          INSERT INTO t2  VALUES (1, 1);
           
          CREATE TABLE t3 (id3 int, i3 int, PRIMARY KEY (id3)) ENGINE=InnoDB;
          INSERT INTO t3 VALUES (1,1);
           
          SELECT id3 FROM 
          (SELECT t3.id3, t2.i2, t1.id2  FROM t3 
             JOIN t1 ON t3.i3=t1.id1
             JOIN t2 ON t2.id2=t1.id2 
             GROUP BY t3.id3, t1.id2) tbl
           JOIN t2 ON t2.id2=tbl.id2;
          

          10.3 c5a9a63293f868f074def37c985fe2
           
           
          #4  0x000055e729126073 in Index_statistics::get_avg_frequency (this=0x8f8f8f8f8f8f8f8f, i=0) at /10.3/sql/sql_statistics.h:416
          #5  0x000055e729189476 in st_key::actual_rec_per_key (this=0x7f85fc0965c0, i=0) at /10.3/sql/table.cc:8473
          #6  0x000055e729272099 in st_join_table::choose_best_splitting (this=0x7f85fc0440d8, record_count=1, remaining_tables=1) at /10.3/sql/opt_split.cc:927
          #7  0x000055e7290bdb09 in best_access_path (join=0x7f85fc01a4d0, s=0x7f85fc0440d8, remaining_tables=1, idx=1, disable_jbuf=false, record_count=1, pos=0x7f85fc044b68, loose_scan_pos=0x7f86501d7c90) at /10.3/sql/sql_select.cc:6723
          #8  0x000055e7290c2910 in best_extension_by_limited_search (join=0x7f85fc01a4d0, remaining_tables=1, idx=1, record_count=1, read_time=1.2, search_depth=61, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8686
          #9  0x000055e7290c2ded in best_extension_by_limited_search (join=0x7f85fc01a4d0, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8757
          #10 0x000055e7290c0ed4 in greedy_search (join=0x7f85fc01a4d0, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:7920
          #11 0x000055e7290c03bc in choose_plan (join=0x7f85fc01a4d0, join_tables=3) at /10.3/sql/sql_select.cc:7498
          #12 0x000055e7290b9265 in make_join_statistics (join=0x7f85fc01a4d0, tables_list=..., keyuse_array=0x7f85fc01a7c0) at /10.3/sql/sql_select.cc:4992
          #13 0x000055e7290ae1ba in JOIN::optimize_inner (this=0x7f85fc01a4d0) at /10.3/sql/sql_select.cc:1895
          #14 0x000055e7290ac7f3 in JOIN::optimize (this=0x7f85fc01a4d0) at /10.3/sql/sql_select.cc:1448
          #15 0x000055e7290b634a in mysql_select (thd=0x7f85fc000b00, tables=0x7f85fc018be0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f85fc01a4a8, unit=0x7f85fc0049b0, select_lex=0x7f85fc005120) at /10.3/sql/sql_select.cc:4220
          #16 0x000055e7290a8340 in handle_select (thd=0x7f85fc000b00, lex=0x7f85fc0048e8, result=0x7f85fc01a4a8, setup_tables_done_option=0) at /10.3/sql/sql_select.cc:382
          #17 0x000055e729072c1c in execute_sqlcom_select (thd=0x7f85fc000b00, all_tables=0x7f85fc018be0) at /10.3/sql/sql_parse.cc:6547
          #18 0x000055e729069073 in mysql_execute_command (thd=0x7f85fc000b00) at /10.3/sql/sql_parse.cc:3769
          #19 0x000055e729076ba1 in mysql_parse (thd=0x7f85fc000b00, rawbuf=0x7f85fc014d18 "SELECT id3\nFROM\n(SELECT t3.id3, t2.i2, t1.id2  \nFROM t3 \nJOIN t1 ON t3.i3=t1.id1\nJOIN t2 ON t2.id2=t1.id2 \nGROUP BY t3.id3, t1.id2\n) tbl\nJOIN t2 ON t2.id2=tbl.id2", length=162, parser_state=0x7f86501d9470, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:8089
          #20 0x000055e729063b46 in dispatch_command (command=COM_QUERY, thd=0x7f85fc000b00, packet=0x7f85fc00b221 "SELECT id3\nFROM\n(SELECT t3.id3, t2.i2, t1.id2  \nFROM t3 \nJOIN t1 ON t3.i3=t1.id1\nJOIN t2 ON t2.id2=t1.id2 \nGROUP BY t3.id3, t1.id2\n) tbl\nJOIN t2 ON t2.id2=tbl.id2", packet_length=162, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:1850
          #21 0x000055e729062563 in do_command (thd=0x7f85fc000b00) at /10.3/sql/sql_parse.cc:1395
          #22 0x000055e7291c8fe9 in do_handle_one_connection (connect=0x55e72c1c75c0) at /10.3/sql/sql_connect.cc:1402
          #23 0x000055e7291c8d3a in handle_one_connection (arg=0x55e72c1c75c0) at /10.3/sql/sql_connect.cc:1308
          #24 0x000055e729a9e0de in pfs_spawn_thread (arg=0x55e72c1daf60) at /10.3/storage/perfschema/pfs.cc:1862
          #25 0x00007f8656fc16ba in start_thread (arg=0x7f86501da700) at pthread_create.c:333
          #26 0x00007f865645641d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109
          
          

          alice Alice Sherepa added a comment - Thanks for the report and the test case! Reproducible on Mariadb 10.3 with Innodb. CREATE TABLE t1 ( id1 int , i1 int , id2 int , PRIMARY KEY (id1), KEY (i1), KEY (id2)) ENGINE=InnoDB; INSERT INTO t1 VALUES (1,1,1); CREATE TABLE t2 (id2 int , i2 int ) ENGINE=InnoDB; INSERT INTO t2 VALUES (1, 1); CREATE TABLE t3 (id3 int , i3 int , PRIMARY KEY (id3)) ENGINE=InnoDB; INSERT INTO t3 VALUES (1,1); SELECT id3 FROM ( SELECT t3.id3, t2.i2, t1.id2 FROM t3 JOIN t1 ON t3.i3=t1.id1 JOIN t2 ON t2.id2=t1.id2 GROUP BY t3.id3, t1.id2) tbl JOIN t2 ON t2.id2=tbl.id2; 10.3 c5a9a63293f868f074def37c985fe2     #4 0x000055e729126073 in Index_statistics::get_avg_frequency (this=0x8f8f8f8f8f8f8f8f, i=0) at /10.3/sql/sql_statistics.h:416 #5 0x000055e729189476 in st_key::actual_rec_per_key (this=0x7f85fc0965c0, i=0) at /10.3/sql/table.cc:8473 #6 0x000055e729272099 in st_join_table::choose_best_splitting (this=0x7f85fc0440d8, record_count=1, remaining_tables=1) at /10.3/sql/opt_split.cc:927 #7 0x000055e7290bdb09 in best_access_path (join=0x7f85fc01a4d0, s=0x7f85fc0440d8, remaining_tables=1, idx=1, disable_jbuf=false, record_count=1, pos=0x7f85fc044b68, loose_scan_pos=0x7f86501d7c90) at /10.3/sql/sql_select.cc:6723 #8 0x000055e7290c2910 in best_extension_by_limited_search (join=0x7f85fc01a4d0, remaining_tables=1, idx=1, record_count=1, read_time=1.2, search_depth=61, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8686 #9 0x000055e7290c2ded in best_extension_by_limited_search (join=0x7f85fc01a4d0, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:8757 #10 0x000055e7290c0ed4 in greedy_search (join=0x7f85fc01a4d0, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=1) at /10.3/sql/sql_select.cc:7920 #11 0x000055e7290c03bc in choose_plan (join=0x7f85fc01a4d0, join_tables=3) at /10.3/sql/sql_select.cc:7498 #12 0x000055e7290b9265 in make_join_statistics (join=0x7f85fc01a4d0, tables_list=..., keyuse_array=0x7f85fc01a7c0) at /10.3/sql/sql_select.cc:4992 #13 0x000055e7290ae1ba in JOIN::optimize_inner (this=0x7f85fc01a4d0) at /10.3/sql/sql_select.cc:1895 #14 0x000055e7290ac7f3 in JOIN::optimize (this=0x7f85fc01a4d0) at /10.3/sql/sql_select.cc:1448 #15 0x000055e7290b634a in mysql_select (thd=0x7f85fc000b00, tables=0x7f85fc018be0, wild_num=0, fields=..., conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f85fc01a4a8, unit=0x7f85fc0049b0, select_lex=0x7f85fc005120) at /10.3/sql/sql_select.cc:4220 #16 0x000055e7290a8340 in handle_select (thd=0x7f85fc000b00, lex=0x7f85fc0048e8, result=0x7f85fc01a4a8, setup_tables_done_option=0) at /10.3/sql/sql_select.cc:382 #17 0x000055e729072c1c in execute_sqlcom_select (thd=0x7f85fc000b00, all_tables=0x7f85fc018be0) at /10.3/sql/sql_parse.cc:6547 #18 0x000055e729069073 in mysql_execute_command (thd=0x7f85fc000b00) at /10.3/sql/sql_parse.cc:3769 #19 0x000055e729076ba1 in mysql_parse (thd=0x7f85fc000b00, rawbuf=0x7f85fc014d18 "SELECT id3\nFROM\n(SELECT t3.id3, t2.i2, t1.id2 \nFROM t3 \nJOIN t1 ON t3.i3=t1.id1\nJOIN t2 ON t2.id2=t1.id2 \nGROUP BY t3.id3, t1.id2\n) tbl\nJOIN t2 ON t2.id2=tbl.id2", length=162, parser_state=0x7f86501d9470, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:8089 #20 0x000055e729063b46 in dispatch_command (command=COM_QUERY, thd=0x7f85fc000b00, packet=0x7f85fc00b221 "SELECT id3\nFROM\n(SELECT t3.id3, t2.i2, t1.id2 \nFROM t3 \nJOIN t1 ON t3.i3=t1.id1\nJOIN t2 ON t2.id2=t1.id2 \nGROUP BY t3.id3, t1.id2\n) tbl\nJOIN t2 ON t2.id2=tbl.id2", packet_length=162, is_com_multi=false, is_next_command=false) at /10.3/sql/sql_parse.cc:1850 #21 0x000055e729062563 in do_command (thd=0x7f85fc000b00) at /10.3/sql/sql_parse.cc:1395 #22 0x000055e7291c8fe9 in do_handle_one_connection (connect=0x55e72c1c75c0) at /10.3/sql/sql_connect.cc:1402 #23 0x000055e7291c8d3a in handle_one_connection (arg=0x55e72c1c75c0) at /10.3/sql/sql_connect.cc:1308 #24 0x000055e729a9e0de in pfs_spawn_thread (arg=0x55e72c1daf60) at /10.3/storage/perfschema/pfs.cc:1862 #25 0x00007f8656fc16ba in start_thread (arg=0x7f86501da700) at pthread_create.c:333 #26 0x00007f865645641d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:109

          A fix for this bug was pushed into 10.3.

          igor Igor Babaev (Inactive) added a comment - A fix for this bug was pushed into 10.3.
          MrMark Mark Anstice added a comment -

          I can confirm this issue is fixed for me in 10.3.10. Thank you to all involved.

          MrMark Mark Anstice added a comment - I can confirm this issue is fixed for me in 10.3.10. Thank you to all involved.

          People

            igor Igor Babaev (Inactive)
            MrMark Mark Anstice
            Votes:
            1 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.