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.

3 files changed, 137 insertions, 1 deletions

diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index 37e32f0291a..eaad4efe977 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 4a6c4316aa8..cfca16287f6 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 9022252c34c..a50736047cd 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -1638,6 +1638,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;
@@ -7701,7 +7707,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);