revision-id: b04c6d9fbdf4fb0da5c2729193a00306dae26853 (mariadb-10.3.7-180-gb04c6d9) parent(s): 87609324e0eeb8f00810604e570a3b2b0b45d1a4 author: Igor Babaev committer: Igor Babaev timestamp: 2018-09-15 14:28:19 -0700 message: MDEV-16917 Index affects query results The optimizer erroneously allowed to use join cache when joining a splittable materialized table together with splitting optimization. As a consequence in some rare cases the server returned wrong result sets for queries with materialized derived. This patch allows to use either join cache without usage of splitting technique for materialization of a splittable derived table or splitting without usage of join cache when joining such table. The costs the these alternatives are compared and the best variant is chosen. --- mysql-test/main/derived_split_innodb.result | 26 ++++++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 26 ++++++++++++++++++++++++++ sql/opt_split.cc | 7 +++++++ sql/sql_select.cc | 7 ++++++- sql/table.h | 1 + 5 files changed, 66 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result new file mode 100644 index 0000000..6fa20b7 --- /dev/null +++ b/mysql-test/main/derived_split_innodb.result @@ -0,0 +1,26 @@ +# +# MDEV-16917: do not use splitting for derived with join cache +# +CREATE TABLE t1 ( +n1 int(10) NOT NULL, +n2 int(10) NOT NULL, +c1 char(1) NOT NULL, +KEY c1 (c1), +KEY n1_c1_n2 (n1,c1,n2) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a'); +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t +WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index c1,n1_c1_n2 n1_c1_n2 9 NULL 2 Using where; Using index +1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 2 +2 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index +SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t +WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; +n1 +0 +1 +DROP TABLE t1; diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test new file mode 100644 index 0000000..2abd6fa --- /dev/null +++ b/mysql-test/main/derived_split_innodb.test @@ -0,0 +1,26 @@ +--source include/have_innodb.inc + +--echo # +--echo # MDEV-16917: do not use splitting for derived with join cache +--echo # + +CREATE TABLE t1 ( + n1 int(10) NOT NULL, + n2 int(10) NOT NULL, + c1 char(1) NOT NULL, + KEY c1 (c1), + KEY n1_c1_n2 (n1,c1,n2) +) ENGINE=InnoDB; +INSERT INTO t1 VALUES (0, 2, 'a'), (1, 3, 'a'); + +ANALYZE TABLE t1; + +Let $q= +SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t + WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; + +eval EXPLAIN $q; +eval $q; + +DROP TABLE t1; + diff --git a/sql/opt_split.cc b/sql/opt_split.cc index 611e703..c5e31ba 100644 --- a/sql/opt_split.cc +++ b/sql/opt_split.cc @@ -267,6 +267,13 @@ void TABLE::deny_splitting() } +double TABLE::get_materialization_cost() +{ + DBUG_ASSERT(spl_opt_info != NULL); + return spl_opt_info->unsplit_cost; +} + + /* This structure is auxiliary and used only in the function that follows it */ struct SplM_field_ext_info: public SplM_field_info { diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 7ff29bb..e97ea68 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -7254,7 +7254,11 @@ best_access_path(JOIN *join, } } - tmp += s->startup_cost; + /* Splitting technique cannot be used with join cache */ + if (s->table->is_splittable()) + tmp+= s->table->get_materialization_cost(); + else + tmp+= s->startup_cost; /* We estimate the cost of evaluating WHERE clause for found records as record_count * rnd_records / TIME_FOR_COMPARE. This cost plus @@ -7276,6 +7280,7 @@ best_access_path(JOIN *join, best_ref_depends_map= 0; best_uses_jbuf= MY_TEST(!disable_jbuf && !((s->table->map & join->outer_join))); + spl_plan= 0; } } diff --git a/sql/table.h b/sql/table.h index 80f5e12..a14a82e 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1507,6 +1507,7 @@ struct TABLE bool is_splittable() { return spl_opt_info != NULL; } void set_spl_opt_info(SplM_opt_info *spl_info); void deny_splitting(); + double get_materialization_cost(); // Now used only if is_splittable()==true void add_splitting_info_for_key_field(struct KEY_FIELD *key_field); /**