[MDEV-25251] main.derived_split_innodb Created: 2021-03-25  Updated: 2021-03-31  Resolved: 2021-03-29

Status: Closed
Project: MariaDB Server
Component/s: Optimizer, Storage Engine - InnoDB
Affects Version/s: 10.5
Fix Version/s: 10.5.10

Type: Bug Priority: Major
Reporter: Michael Widenius Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None


 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

 Comments   
Comment by Sergei Petrunia [ 2021-03-27 ]

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.

Comment by Sergei Petrunia [ 2021-03-28 ]

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;

Comment by Sergei Petrunia [ 2021-03-28 ]

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.

Generated at Thu Feb 08 09:36:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.