Details

    Description

      Fails in new buildbot on amd64-ubuntu-2004-icc with

      CURRENT_TEST: main.derived_split_innodb
      --- /buildbot/amd64-ubuntu-2004-icc/build/mysql-test/main/derived_split_innodb.result	2021-03-24 16:56:51.000000000 +0000
      +++ /buildbot/amd64-ubuntu-2004-icc/build/mysql-test/main/derived_split_innodb.reject	2021-03-24 20:07:01.754138195 +0000
      @@ -48,9 +48,9 @@
       id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
       1	PRIMARY	t2	ALL	NULL	NULL	NULL	NULL	1	Using where
       1	PRIMARY	<derived2>	ref	key0	key0	5	test.t2.id2	2	
      -2	DERIVED	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using temporary; Using filesort
      -2	DERIVED	t1	eq_ref	PRIMARY,id2	PRIMARY	4	test.t3.i3	1	
      -2	DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
      +2	LATERAL DERIVED	t3	ALL	NULL	NULL	NULL	NULL	1	Using where; Using temporary; Using filesort
      +2	LATERAL DERIVED	t1	eq_ref	PRIMARY,id2	PRIMARY	4	test.t3.i3	1	Using where
      +2	LATERAL DERIVED	t2	ALL	NULL	NULL	NULL	NULL	1	Using where; Using join buffer (flat, BNL join)
       SELECT id3
       FROM (SELECT t3.id3, t2.i2, t1.id2  FROM t3,t1,t2
       WHERE t3.i3=t1.id1 AND t2.id2=t1.id2
      

      This is random failure but is important to get fixed so to make protected branches work

      Attachments

        Activity

          It is hitting this testcase:

          commit 5ec144cfab58a77b67277d0687fdc09ed17f8029
          Author: Igor Babaev <igor@askmonty.org>
          Date:   Mon Sep 17 18:49:53 2018 -0700
           
              MDEV-17211 Server crash on query
              
              The function JOIN_TAB::choose_best_splitting() did not take into account
              that for some tables whose fields were used in the GROUP BY list of
              the specification of a splittable materialized derived there might exist
              no elements in the array ext_keyuses_for_splitting.
          

          psergei Sergei Petrunia added a comment - It is hitting this testcase: commit 5ec144cfab58a77b67277d0687fdc09ed17f8029 Author: Igor Babaev <igor@askmonty.org> Date: Mon Sep 17 18:49:53 2018 -0700   MDEV-17211 Server crash on query The function JOIN_TAB::choose_best_splitting() did not take into account that for some tables whose fields were used in the GROUP BY list of the specification of a splittable materialized derived there might exist no elements in the array ext_keyuses_for_splitting.

          Testcase

          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);
          set optimizer_trace=1;
           
          EXPLAIN
          SELECT id3
          FROM 
            (SELECT t3.id3, t2.i2, t1.id2  
             FROM
               t3,t1,t2
             WHERE 
               t3.i3=t1.id1 AND t2.id2=t1.id2
             GROUP BY t3.id3, t1.id2
            ) AS t,
            t2
          WHERE
            t2.id2=t.id2;
          

          psergei Sergei Petrunia added a comment - Testcase 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); set optimizer_trace=1;   EXPLAIN SELECT id3 FROM ( SELECT t3.id3, t2.i2, t1.id2 FROM t3,t1,t2 WHERE t3.i3=t1.id1 AND t2.id2=t1.id2 GROUP BY t3.id3, t1.id2 ) AS t, t2 WHERE t2.id2=t.id2;

          The execution starts to differ in JOIN_TAB::choose_best_splitting, here:

                if(record_count * spl_plan->cost < spl_opt_info->unsplit_cost - 0.01)
          

          in both cases:

            record_count=1
            spl_plan->cost=1.15
            spl_opt_info->unsplit_cost=1.15
          

          In the testcase (and on common platforms), the branch is not taken.
          in RelWithDebugInfo binary compiled with ICC, the branch is taken and the
          optimizer constructs the query plan with LATERAL DERIVED.

          psergei Sergei Petrunia added a comment - The execution starts to differ in JOIN_TAB::choose_best_splitting, here: if (record_count * spl_plan->cost < spl_opt_info->unsplit_cost - 0.01) in both cases: record_count=1 spl_plan->cost=1.15 spl_opt_info->unsplit_cost=1.15 In the testcase (and on common platforms), the branch is not taken. in RelWithDebugInfo binary compiled with ICC, the branch is taken and the optimizer constructs the query plan with LATERAL DERIVED.

          People

            psergei Sergei Petrunia
            monty Michael Widenius
            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.