Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.5
-
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
Attachments
Activity
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;
|
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.
|
The execution starts to differ in JOIN_TAB::choose_best_splitting, here:
in both cases:
record_count=1spl_plan->cost=1.15spl_opt_info->unsplit_cost=1.15In 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.