revision-id: 6bdde42d9964389894ab14480413f51055e385e0 (mariadb-10.3.7-101-g6bdde42) parent(s): 0c745c743c65ceb6533babdf21556b633e81566c author: Igor Babaev committer: Igor Babaev timestamp: 2018-08-03 14:54:02 -0700 message: MDEV-16801 seg_fault on a query The bug was in the in the code of JOIN::check_for_splittable_materialized() where the structures describing the fields of a materialized derived table that potentially could be used in split optimization were build. As a result of this bug some fields that were not usable for splitting were detected as usable. This could trigger crashes further in st_join_table::choose_best_splitting(). --- mysql-test/main/derived_cond_pushdown.result | 54 ++++++++++++++++++++++++++++ mysql-test/main/derived_cond_pushdown.test | 33 +++++++++++++++++ sql/opt_split.cc | 3 +- 3 files changed, 89 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 0cd8b4c..fea6d61 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -15960,3 +15960,57 @@ f c 9 1 DROP VIEW v1; DROP TABLE t1; +# +# MDEV-16801: splittable materialized derived/views with +# one grouping field from table without keys +# +CREATE TABLE t1 (a int, b int, INDEX idx_a(a), INDEX idx_b(b)) ENGINE=MYISAM; +CREATE TABLE t2 (c int) ENGINE=MYISAM; +CREATE TABLE t3 (d int) ENGINE=MYISAM; +INSERT INTO t1 VALUES +(77,7), (11,1), (33,3), (44,4), (8,88), +(78,7), (98,9), (38,3), (28,2), (79,7), +(58,5), (42,4), (71,7), (27,2), (91,9); +INSERT INTO t1 SELECT a+100, b+10 FROM t1; +INSERT INTO t2 VALUES +(100), (700), (200), (100), (200); +INSERT INTO t3 VALUES +(3), (4), (1), (8), (3); +ANALYZE tables t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status OK +test.t2 analyze status OK +test.t3 analyze status OK +SELECT * +FROM t3, +(SELECT t1.b, t2.c +FROM t1, t2 +GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; +d b c +3 3 700 +3 3 200 +3 3 100 +4 4 700 +4 4 200 +4 4 100 +1 1 700 +1 1 200 +1 1 100 +3 3 700 +3 3 200 +3 3 100 +EXPLAIN EXTENDED SELECT * +FROM t3, +(SELECT t1.b, t2.c +FROM t1, t2 +GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 5 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t3.d 2 100.00 +2 LATERAL DERIVED t1 ref idx_b idx_b 5 test.t3.d 2 100.00 Using index; Using temporary; Using filesort +2 LATERAL DERIVED t2 ALL NULL NULL NULL NULL 5 100.00 Using join buffer (flat, BNL join) +Warnings: +Note 1003 /* select#1 */ select `test`.`t3`.`d` AS `d`,`dt`.`b` AS `b`,`dt`.`c` AS `c` from `test`.`t3` join (/* select#2 */ select `test`.`t1`.`b` AS `b`,`test`.`t2`.`c` AS `c` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`b` = `test`.`t3`.`d` group by `test`.`t1`.`b`,`test`.`t2`.`c`) `dt` where `dt`.`b` = `test`.`t3`.`d` +DROP TABLE t1,t2,t3; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index e51646a..d918e48 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2906,3 +2906,36 @@ SELECT * FROM ( SELECT STRAIGHT_JOIN f, COUNT(*) as c FROM v1 GROUP BY f ) AS s; DROP VIEW v1; DROP TABLE t1; + +--echo # +--echo # MDEV-16801: splittable materialized derived/views with +--echo # one grouping field from table without keys +--echo # + +CREATE TABLE t1 (a int, b int, INDEX idx_a(a), INDEX idx_b(b)) ENGINE=MYISAM; +CREATE TABLE t2 (c int) ENGINE=MYISAM; +CREATE TABLE t3 (d int) ENGINE=MYISAM; +INSERT INTO t1 VALUES + (77,7), (11,1), (33,3), (44,4), (8,88), + (78,7), (98,9), (38,3), (28,2), (79,7), + (58,5), (42,4), (71,7), (27,2), (91,9); +INSERT INTO t1 SELECT a+100, b+10 FROM t1; +INSERT INTO t2 VALUES + (100), (700), (200), (100), (200); +INSERT INTO t3 VALUES + (3), (4), (1), (8), (3); + +ANALYZE tables t1,t2,t3; + +let $q= +SELECT * + FROM t3, + (SELECT t1.b, t2.c + FROM t1, t2 + GROUP BY t1.b,t2.c) dt +WHERE t3.d = dt.b; + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2,t3; diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 37853bd..611e703 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -413,7 +413,8 @@ bool JOIN::check_for_splittable_materialized() for (cand= cand_start; cand < cand_end; cand++) { - if (cand->underlying_field->field_index + 1 == fldnr) + if (cand->underlying_field->table == table && + cand->underlying_field->field_index + 1 == fldnr) { cand->is_usable_for_ref_access= true; break;