Add test cases for MDEV-20595 and MDEV-21633 to show these are solved

MDEV-21633 Assertion `tmp >= 0' failed in best_access_path with
           rowid_filter=ON
MDEV-20595 Assertion `0 < sel && sel <= 2.0' failed in
           table_cond_selectivity

6 files changed, 184 insertions, 1 deletions

diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result
index cba4087148f..76ce2eb78d4 100644
--- a/mysql-test/main/rowid_filter.result
+++ b/mysql-test/main/rowid_filter.result
@@ -2219,3 +2219,49 @@ a	b
 drop table t1;
 SET @@optimizer_switch=@save_optimizer_switch;
 set @@use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-21633
+# Assertion `tmp >= 0' failed in best_access_path with rowid_filter=ON
+#
+set @save_optimizer_switch= @@optimizer_switch;
+SET optimizer_switch='rowid_filter=on';
+CREATE TABLE t1 (
+pk INT AUTO_INCREMENT,
+a INT,
+b VARCHAR(8),
+KEY(a),
+PRIMARY KEY(pk),
+KEY (a,pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 (a,b) VALUES
+(NULL,'d'),(9,'b'),(2,'x'),(5,'k'),(NULL,'d'),(3,'s'),(5,'k'),(1,'r'),
+(8,'l'),(3,'z'),(1,'c'),(1,'q'),(NULL,'x'),(NULL,'p'),(NULL,'z'),(7,'a'),
+(0,'i'),(3,'s'),(NULL,'h'),(4,'p'),(1,'i'),(4,'f'),(1,'c'),(NULL,'a'),
+(NULL,'x'),(1,'b'),(NULL,'n'),(NULL,'h'),(5,'i'),(6,'e'),(NULL,'i'),
+(7,'e'),(1,'r'),(NULL,'z'),(1,'i'),(14,'c'),(6,'u'),(3,'b'),(4,'z'),
+(2,'c'),(70,'d'),(NULL,'p'),(21,'j'),(6,'e'),(5,'c'),(13,'i'),(42,'d'),
+(80,'s'),(14,'t'),(9,'a'),(0,'2'),(0,NULL),(0,NULL),(0,NULL),(0,''),
+(0,''),(0,'1'),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,''),(0,''),
+(0,NULL),(0,''),(0,''),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,NULL),
+(0,NULL),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,NULL),
+(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,''),(0,''),(0,''),(0,''),
+(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,'');
+CREATE TABLE t2 (c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6);
+SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';
+pk	a	b	c
+6	3	s	3
+4	5	k	5
+7	5	k	5
+explain SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+1	SIMPLE	t1	ref|filter	PRIMARY,a,a_2	a|PRIMARY	5|4	test.t2.c	3 (4%)	Using where; Using rowid filter
+SET optimizer_switch='rowid_filter=off';
+SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';
+pk	a	b	c
+6	3	s	3
+4	5	k	5
+7	5	k	5
+SET @@optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1, t2;
diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test
index fbbd9746ef8..e4cb1f88039 100644
--- a/mysql-test/main/rowid_filter.test
+++ b/mysql-test/main/rowid_filter.test
@@ -412,5 +412,51 @@ SELECT * FROM t1 WHERE a > 0 AND b=0;
 drop table t1;
 SET @@optimizer_switch=@save_optimizer_switch;
 
-
 set @@use_stat_tables=@save_use_stat_tables;
+
+--echo #
+--echo # MDEV-21633
+--echo # Assertion `tmp >= 0' failed in best_access_path with rowid_filter=ON
+--echo #
+
+set @save_optimizer_switch= @@optimizer_switch;
+SET optimizer_switch='rowid_filter=on';
+
+CREATE TABLE t1 (
+    pk INT AUTO_INCREMENT,
+    a INT,
+    b VARCHAR(8),
+    KEY(a),
+    PRIMARY KEY(pk),
+    KEY (a,pk)
+) ENGINE=MyISAM;
+
+INSERT INTO t1 (a,b) VALUES
+    (NULL,'d'),(9,'b'),(2,'x'),(5,'k'),(NULL,'d'),(3,'s'),(5,'k'),(1,'r'),
+    (8,'l'),(3,'z'),(1,'c'),(1,'q'),(NULL,'x'),(NULL,'p'),(NULL,'z'),(7,'a'),
+    (0,'i'),(3,'s'),(NULL,'h'),(4,'p'),(1,'i'),(4,'f'),(1,'c'),(NULL,'a'),
+    (NULL,'x'),(1,'b'),(NULL,'n'),(NULL,'h'),(5,'i'),(6,'e'),(NULL,'i'),
+    (7,'e'),(1,'r'),(NULL,'z'),(1,'i'),(14,'c'),(6,'u'),(3,'b'),(4,'z'),
+    (2,'c'),(70,'d'),(NULL,'p'),(21,'j'),(6,'e'),(5,'c'),(13,'i'),(42,'d'),
+    (80,'s'),(14,'t'),(9,'a'),(0,'2'),(0,NULL),(0,NULL),(0,NULL),(0,''),
+    (0,''),(0,'1'),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,''),(0,''),
+    (0,NULL),(0,''),(0,''),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,NULL),
+    (0,NULL),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,NULL),
+    (0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,''),(0,''),(0,''),(0,''),
+    (0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,'');
+
+CREATE TABLE t2 (c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6);
+
+SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';
+
+explain SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';
+
+SET optimizer_switch='rowid_filter=off';
+
+SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';
+
+SET @@optimizer_switch=@save_optimizer_switch;
+
+# Cleanup
+DROP TABLE t1, t2;
diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result
index dd9f5615196..f1f9df36442 100644
--- a/mysql-test/main/rowid_filter_innodb.result
+++ b/mysql-test/main/rowid_filter_innodb.result
@@ -2194,6 +2194,52 @@ a	b
 drop table t1;
 SET @@optimizer_switch=@save_optimizer_switch;
 set @@use_stat_tables=@save_use_stat_tables;
+#
+# MDEV-21633
+# Assertion `tmp >= 0' failed in best_access_path with rowid_filter=ON
+#
+set @save_optimizer_switch= @@optimizer_switch;
+SET optimizer_switch='rowid_filter=on';
+CREATE TABLE t1 (
+pk INT AUTO_INCREMENT,
+a INT,
+b VARCHAR(8),
+KEY(a),
+PRIMARY KEY(pk),
+KEY (a,pk)
+) ENGINE=MyISAM;
+INSERT INTO t1 (a,b) VALUES
+(NULL,'d'),(9,'b'),(2,'x'),(5,'k'),(NULL,'d'),(3,'s'),(5,'k'),(1,'r'),
+(8,'l'),(3,'z'),(1,'c'),(1,'q'),(NULL,'x'),(NULL,'p'),(NULL,'z'),(7,'a'),
+(0,'i'),(3,'s'),(NULL,'h'),(4,'p'),(1,'i'),(4,'f'),(1,'c'),(NULL,'a'),
+(NULL,'x'),(1,'b'),(NULL,'n'),(NULL,'h'),(5,'i'),(6,'e'),(NULL,'i'),
+(7,'e'),(1,'r'),(NULL,'z'),(1,'i'),(14,'c'),(6,'u'),(3,'b'),(4,'z'),
+(2,'c'),(70,'d'),(NULL,'p'),(21,'j'),(6,'e'),(5,'c'),(13,'i'),(42,'d'),
+(80,'s'),(14,'t'),(9,'a'),(0,'2'),(0,NULL),(0,NULL),(0,NULL),(0,''),
+(0,''),(0,'1'),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,''),(0,''),
+(0,NULL),(0,''),(0,''),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,NULL),
+(0,NULL),(0,''),(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,NULL),
+(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,''),(0,''),(0,''),(0,''),
+(0,''),(0,''),(0,''),(0,NULL),(0,''),(0,NULL),(0,'');
+CREATE TABLE t2 (c INT) ENGINE=MyISAM;
+INSERT INTO t2 VALUES (1),(2),(3),(4),(5),(6);
+SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';
+pk	a	b	c
+6	3	s	3
+4	5	k	5
+7	5	k	5
+explain SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	6	Using where
+1	SIMPLE	t1	ref|filter	PRIMARY,a,a_2	a|PRIMARY	5|4	test.t2.c	3 (4%)	Using where; Using rowid filter
+SET optimizer_switch='rowid_filter=off';
+SELECT * FROM t1 JOIN t2 WHERE a = c AND pk BETWEEN 4 AND 7 AND a BETWEEN 2 AND 12 AND b != 'foo';
+pk	a	b	c
+6	3	s	3
+4	5	k	5
+7	5	k	5
+SET @@optimizer_switch=@save_optimizer_switch;
+DROP TABLE t1, t2;
 SET GLOBAL innodb_stats_persistent=@save_stats_persistent;
 #
 # MDEV-18755: possible RORI-plan and possible plan with range filter
diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result
index f0b9f8ee362..ace9d2e66fe 100644
--- a/mysql-test/main/selectivity.result
+++ b/mysql-test/main/selectivity.result
@@ -1941,3 +1941,18 @@ set use_stat_tables= @save_use_stat_tables;
 DROP TABLE t1;
 # End of 10.2 tests
 set @@global.histogram_size=@save_histogram_size;
+#
+# MDEV-20595
+# Assertion `0 < sel && sel <= 2.0' failed in table_cond_selectivity
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_use_condition_selectivity=2;
+EXPLAIN  SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	const	PRIMARY,a	PRIMARY	4	const	1	
+1	SIMPLE	B	ref	a	a	5	const	1	
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+drop table t1,t2;
diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test
index 1d96dc0bf80..fba3a5b156b 100644
--- a/mysql-test/main/selectivity.test
+++ b/mysql-test/main/selectivity.test
@@ -1331,3 +1331,18 @@ DROP TABLE t1;
 #
 --source include/restore_charset.inc
 set @@global.histogram_size=@save_histogram_size;
+
+--echo #
+--echo # MDEV-20595
+--echo # Assertion `0 < sel && sel <= 2.0' failed in table_cond_selectivity
+--echo #
+
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+
+set optimizer_use_condition_selectivity=2;
+EXPLAIN  SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+drop table t1,t2;
diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result
index 10c5eb0f142..bfe1e57f683 100644
--- a/mysql-test/main/selectivity_innodb.result
+++ b/mysql-test/main/selectivity_innodb.result
@@ -1948,6 +1948,21 @@ set use_stat_tables= @save_use_stat_tables;
 DROP TABLE t1;
 # End of 10.2 tests
 set @@global.histogram_size=@save_histogram_size;
+#
+# MDEV-20595
+# Assertion `0 < sel && sel <= 2.0' failed in table_cond_selectivity
+#
+create table t1 (id int, a int, PRIMARY KEY(id), key(a));
+insert into t1 select seq,seq from seq_1_to_100;
+create table t2 (id int, a int, b int, PRIMARY KEY(id), key(a), key(b));
+insert into t2 select seq,seq,seq from seq_1_to_100;
+set optimizer_use_condition_selectivity=2;
+EXPLAIN  SELECT * FROM t1 A, t1 B WHERE A.a = B.a and A.id = 65;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	A	const	PRIMARY,a	PRIMARY	4	const	1	
+1	SIMPLE	B	ref	a	a	5	const	1	Using index
+set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity;
+drop table t1,t2;
 set optimizer_switch=@save_optimizer_switch_for_selectivity_test;
 set @tmp_ust= @@use_stat_tables;
 set @tmp_oucs= @@optimizer_use_condition_selectivity;