revision-id: d64d583aa46a1ec618812497dbe0a15c877f198f (mariadb-10.4.10-25-gd64d583) parent(s): f95288211ce1023e0d268229fbe5febbf0b2edd3 author: Igor Babaev committer: Igor Babaev timestamp: 2019-11-25 17:40:47 -0800 message: MDEV-20056 Assertion `!prebuilt->index->is_primary()' failed in row_search_idx_cond_check When usage of rowid filter is evaluated by the optimizer to join a table to the current partial join employing a certain index it should be checked that a key for at least the major component of this index can be constructed using values from the columns of the partial join. --- mysql-test/main/rowid_filter_innodb.result | 76 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter_innodb.test | 53 +++++++++++++++++++++ sql/sql_select.cc | 9 +++- 3 files changed, 137 insertions(+), 1 deletion(-) diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index 37e32f0..eaad4ef 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -2245,3 +2245,79 @@ a 5 DROP TABLE t1; SET GLOBAL innodb_stats_persistent= @stats.save; +# +# MDEV-20056: index to build range filter should not be +# the same as table access index +# +SET @stats.save= @@innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent= ON; +CREATE TABLE t1 (ch varchar(1), id int, id2 int) ENGINE=InnoDB; +INSERT INTO t1 VALUES +('l',3,2), ('e',NULL,NULL), ('r',7,3), ('h',NULL,2), (NULL,4,4), ('c',4,NULL), +('k',NULL,NULL), ('h',NULL,NULL), ('b',9,NULL), ('f',6,NULL); +CREATE TABLE t2 ( +pk int NOT NULL, col_date_key date, ch2 varchar(1), id2 int, +PRIMARY KEY (pk), KEY (col_date_key), KEY (ch2), KEY (id2) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES +(1,'2034-04-21','g',9), (2,'2006-09-08','y',1), (3,NULL,'h',2), +(4,'1987-03-02','s',2), (5,'2019-07-02','u',NULL),(6,'2012-12-18','z',1), +(7,NULL,'w',4), (8,'2005-03-10','o',8), (9,'1987-02-12','d',4); +CREATE TABLE t3 (id int) ENGINE=InnoDB; +INSERT INTO t3 VALUES (6); +ANALYZE TABLE t1,t2,t3; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected +test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected +test.t3 analyze status OK +EXPLAIN EXTENDED SELECT 1 FROM t3 +WHERE EXISTS ( SELECT 1 FROM t1 +WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 +WHERE bt1.id = t2.pk AND +t2.ch2 <= 'g' ) OR +t1.id2 = t1.id); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 100.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 10 100.00 Using where +3 MATERIALIZED t2 range PRIMARY,col_date_key,ch2,id2 ch2 4 NULL 2 100.00 Using where; Using index +3 MATERIALIZED bt1 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1276 Field or reference 'test.t3.id' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t3` where <in_optimizer>(1,<expr_cache><`test`.`t3`.`id`>(exists(/* select#2 */ select 1 from `test`.`t1` where <expr_cache><`test`.`t3`.`id`>(<in_optimizer>(`test`.`t3`.`id`,`test`.`t3`.`id` in ( <materialize> (/* select#3 */ select `test`.`bt1`.`id` from `test`.`t2` join `test`.`t1` `bt1` where `test`.`bt1`.`id` = `test`.`t2`.`pk` and `test`.`t2`.`ch2` <= 'g' ), <primary_index_lookup>(`test`.`t3`.`id` in <temporary table> on distinct_key where `test`.`t3`.`id` = `<subquery3>`.`id`)))) or `test`.`t1`.`id2` = `test`.`t1`.`id` limit 1))) +SELECT 1 FROM t3 +WHERE EXISTS ( SELECT 1 FROM t1 +WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 +WHERE bt1.id = t2.pk AND +t2.ch2 <= 'g' ) OR +t1.id2 = t1.id); +1 +1 +EXPLAIN EXTENDED SELECT 1 FROM t3 +WHERE EXISTS ( SELECT 1 FROM t1 +WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 +WHERE bt1.ch = t2.ch2 AND +bt1.id = t2.pk AND +t2.ch2 <= 'g' ) OR +t1.id2 = t1.id); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t3 ALL NULL NULL NULL NULL 1 100.00 Using where +2 DEPENDENT SUBQUERY t1 ALL NULL NULL NULL NULL 10 100.00 Using where +3 MATERIALIZED t2 range PRIMARY,col_date_key,ch2,id2 ch2 4 NULL 2 100.00 Using where; Using index +3 MATERIALIZED bt1 ALL NULL NULL NULL NULL 10 100.00 Using where; Using join buffer (flat, BNL join) +Warnings: +Note 1276 Field or reference 'test.t3.id' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t3` where <in_optimizer>(1,<expr_cache><`test`.`t3`.`id`>(exists(/* select#2 */ select 1 from `test`.`t1` where <expr_cache><`test`.`t3`.`id`>(<in_optimizer>(`test`.`t3`.`id`,`test`.`t3`.`id` in ( <materialize> (/* select#3 */ select `test`.`bt1`.`id` from `test`.`t2` join `test`.`t1` `bt1` where `test`.`bt1`.`ch` = `test`.`t2`.`ch2` and `test`.`bt1`.`id` = `test`.`t2`.`pk` and `test`.`t2`.`ch2` <= 'g' ), <primary_index_lookup>(`test`.`t3`.`id` in <temporary table> on distinct_key where `test`.`t3`.`id` = `<subquery3>`.`id`)))) or `test`.`t1`.`id2` = `test`.`t1`.`id` limit 1))) +SELECT 1 FROM t3 +WHERE EXISTS ( SELECT 1 FROM t1 +WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 +WHERE bt1.ch = t2.ch2 AND +bt1.id = t2.pk AND +t2.ch2 <= 'g' ) OR +t1.id2 = t1.id); +1 +1 +DROP TABLE t1, t2, t3; +SET GLOBAL innodb_stats_persistent= @stats.save; diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index 4a6c431..cfca162 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -129,3 +129,56 @@ SELECT a FROM t1 WHERE c < 'k' AND b > 't' ORDER BY a; DROP TABLE t1; SET GLOBAL innodb_stats_persistent= @stats.save; + +--echo # +--echo # MDEV-20056: index to build range filter should not be +--echo # the same as table access index +--echo # + +SET @stats.save= @@innodb_stats_persistent; +SET GLOBAL innodb_stats_persistent= ON; + +CREATE TABLE t1 (ch varchar(1), id int, id2 int) ENGINE=InnoDB; +INSERT INTO t1 VALUES +('l',3,2), ('e',NULL,NULL), ('r',7,3), ('h',NULL,2), (NULL,4,4), ('c',4,NULL), +('k',NULL,NULL), ('h',NULL,NULL), ('b',9,NULL), ('f',6,NULL); + +CREATE TABLE t2 ( + pk int NOT NULL, col_date_key date, ch2 varchar(1), id2 int, + PRIMARY KEY (pk), KEY (col_date_key), KEY (ch2), KEY (id2) +) ENGINE=InnoDB; +INSERT INTO t2 VALUES +(1,'2034-04-21','g',9), (2,'2006-09-08','y',1), (3,NULL,'h',2), +(4,'1987-03-02','s',2), (5,'2019-07-02','u',NULL),(6,'2012-12-18','z',1), +(7,NULL,'w',4), (8,'2005-03-10','o',8), (9,'1987-02-12','d',4); + +CREATE TABLE t3 (id int) ENGINE=InnoDB; +INSERT INTO t3 VALUES (6); + +ANALYZE TABLE t1,t2,t3; + +let $q1= +SELECT 1 FROM t3 +WHERE EXISTS ( SELECT 1 FROM t1 + WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 + WHERE bt1.id = t2.pk AND + t2.ch2 <= 'g' ) OR + t1.id2 = t1.id); + +eval EXPLAIN EXTENDED $q1; +eval $q1; + +let $q2= +SELECT 1 FROM t3 +WHERE EXISTS ( SELECT 1 FROM t1 + WHERE t3.id IN ( SELECT bt1.id FROM t2, t1 AS bt1 + WHERE bt1.ch = t2.ch2 AND + bt1.id = t2.pk AND + t2.ch2 <= 'g' ) OR + t1.id2 = t1.id); + +eval EXPLAIN EXTENDED $q2; +eval $q2; + +DROP TABLE t1, t2, t3; +SET GLOBAL innodb_stats_persistent= @stats.save; diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 1ee2a17..3286224 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1602,6 +1602,12 @@ bool JOIN::make_range_rowid_filters() { if (!tab->range_rowid_filter_info) continue; + + DBUG_ASSERT(!(tab->ref.key >= 0 && + tab->ref.key == (int) tab->range_rowid_filter_info->key_no)); + DBUG_ASSERT(!(tab->ref.key == -1 && tab->quick && + tab->quick->index == tab->range_rowid_filter_info->key_no)); + int err; SQL_SELECT *sel= NULL; Rowid_filter_container *filter_container= NULL; @@ -7665,7 +7671,8 @@ best_access_path(JOIN *join, found_ref); } /* not ft_key */ - if (records < DBL_MAX) + if (records < DBL_MAX && + (found_part & 1)) // start_key->key can be used for index access { double rows= record_count * records; double access_cost_factor= MY_MIN(tmp / rows, 1.0);