diff options
Diffstat (limited to 'mysql-test/main/rowid_filter_innodb.test')
| -rw-r--r-- | mysql-test/main/rowid_filter_innodb.test | 34 |
1 files changed, 34 insertions, 0 deletions
diff --git a/mysql-test/main/rowid_filter_innodb.test b/mysql-test/main/rowid_filter_innodb.test index bc5518eba8d..97b9aa0102c 100644 --- a/mysql-test/main/rowid_filter_innodb.test +++ b/mysql-test/main/rowid_filter_innodb.test @@ -540,3 +540,37 @@ set join_cache_level=@save_join_cache_level; drop table filt, acei, acli; set global innodb_stats_persistent= @stats.save; + +--echo # +--echo # MDEV-22846: ref access with full scan on keys with NULLs + rowid_filter +--echo # + +set @stats.save= @@innodb_stats_persistent; +set global innodb_stats_persistent=0; + +CREATE TABLE t1 (pk int NOT NULL, c1 varchar(1)) engine=innodb; +INSERT INTO t1 VALUES +(1,NULL),(15,'o'),(16,'x'),(19,'t'),(35,'k'),(36,'h'),(42,'t'),(43,'h'), +(53,'l'),(62,'a'),(71,NULL),(79,'u'),(128,'y'),(129,NULL),(133,NULL); + +CREATE TABLE t2 ( +i1 int, c1 varchar(1) NOT NULL, KEY c1 (c1), KEY i1 (i1) +) engine=innodb; +INSERT INTO t2 VALUES +(1,'1'),(NULL,'1'),(42,'t'),(NULL,'1'),(79,'u'),(NULL,'1'), +(NULL,'4'),(NULL,'4'),(NULL,'1'),(NULL,'u'),(2,'1'),(NULL,'w'); +INSERT INTO t2 SELECT * FROM t2; + +let $q= +SELECT * FROM t1 +WHERE t1.c1 NOT IN (SELECT t2.c1 FROM t2, t1 AS a1 + WHERE t2.i1 = t1.pk AND t2.i1 IS NOT NULL); + +eval $q; +eval EXPLAIN EXTENDED $q; + +DROP TABLE t1,t2; + +set global innodb_stats_persistent= @stats.save; + +--echo # End of 10.4 tests |