revision-id: 85183b7e7b008d3374cb9f2c10fa270e1f4b0188 (mariadb-10.3.7-183-g85183b7) parent(s): c5a9a63293f868f074def37c985fe23a9c01c276 author: Igor Babaev committer: Igor Babaev timestamp: 2018-09-17 18:49:53 -0700 message: 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. --- mysql-test/main/derived_split_innodb.result | 34 +++++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 29 ++++++++++++++++++++++++ sql/opt_split.cc | 2 ++ 3 files changed, 65 insertions(+) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 6fa20b7..7e4ba8e 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -24,3 +24,37 @@ n1 0 1 DROP TABLE t1; +# +# MDEV-17211: splittable materialized derived joining 3 tables with +# GROUP BY list containing fields from 2 of them +# +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); +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; +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) +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; +id3 +1 +DROP TABLE t1,t2,t3; diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 2abd6fa..5e5e3d5 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -24,3 +24,32 @@ eval $q; DROP TABLE t1; +--echo # +--echo # MDEV-17211: splittable materialized derived joining 3 tables with +--echo # GROUP BY list containing fields from 2 of them +--echo # + +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); + +let $q= +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; + +eval EXPLAIN $q; +eval $q; + +DROP TABLE t1,t2,t3; diff --git a/sql/opt_split.cc b/sql/opt_split.cc index c5e31ba..fc3f084 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -895,6 +895,8 @@ SplM_plan_info * JOIN_TAB::choose_best_splitting(double record_count, continue; JOIN_TAB *tab= join->map2table[tablenr]; TABLE *table= tab->table; + if (keyuse_ext->table != table) + continue; do { uint key= keyuse_ext->key;