Re: Query with join_cache_level = 6 returns extra rows in maria-5.3-dsmrr-cpk
Test case:

--source include/have_innodb.inc

CREATE TABLE `CC` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

INSERT INTO `CC` VALUES (10,7);
INSERT INTO `CC` VALUES (11,1);
INSERT INTO `CC` VALUES (12,5);
INSERT INTO `CC` VALUES (13,3);
INSERT INTO `CC` VALUES (14,6);
INSERT INTO `CC` VALUES (15,92);
INSERT INTO `CC` VALUES (16,7);
INSERT INTO `CC` VALUES (17,NULL);
INSERT INTO `CC` VALUES (18,3);
INSERT INTO `CC` VALUES (19,5);
INSERT INTO `CC` VALUES (20,1);
INSERT INTO `CC` VALUES (21,2);
INSERT INTO `CC` VALUES (22,NULL);
INSERT INTO `CC` VALUES (23,1);
INSERT INTO `CC` VALUES (24,0);
INSERT INTO `CC` VALUES (25,210);
INSERT INTO `CC` VALUES (26,8);
INSERT INTO `CC` VALUES (27,7);
INSERT INTO `CC` VALUES (28,5);
INSERT INTO `CC` VALUES (29,NULL);
CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_nokey` int(11) DEFAULT NULL,
PRIMARY KEY (`pk`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO `C` VALUES (1,NULL);
INSERT INTO `C` VALUES (2,7);
INSERT INTO `C` VALUES (3,9);
INSERT INTO `C` VALUES (4,7);
INSERT INTO `C` VALUES (5,4);
INSERT INTO `C` VALUES (6,2);
INSERT INTO `C` VALUES (7,6);
INSERT INTO `C` VALUES (8,8);
INSERT INTO `C` VALUES (9,NULL);
INSERT INTO `C` VALUES (10,5);
INSERT INTO `C` VALUES (11,NULL);
INSERT INTO `C` VALUES (12,6);
INSERT INTO `C` VALUES (13,188);
INSERT INTO `C` VALUES (14,2);
INSERT INTO `C` VALUES (15,1);
INSERT INTO `C` VALUES (16,1);
INSERT INTO `C` VALUES (17,0);
INSERT INTO `C` VALUES (18,9);
INSERT INTO `C` VALUES (19,NULL);
INSERT INTO `C` VALUES (20,4);

SET SESSION join_cache_level = 0;

SELECT table2 .`col_int_nokey`
FROM CC table1 JOIN C table2 ON table2 .`pk` = table1 .`col_int_nokey`
WHERE table1 .`pk` ;

SET SESSION join_cache_level = 6;

SELECT table2 .`col_int_nokey`
FROM CC table1 JOIN C table2 ON table2 .`pk` = table1 .`col_int_nokey`
WHERE table1 .`pk` ;

Re: Query with join_cache_level = 6 returns extra rows in maria-5.3-dsmrr-cpk
Can be observed in 5.3-main also. Since 5.3-main doesn't support DS-MRR over clustered primary keys, one must do s/PRIMARY KEY/KEY/g in the above posted testcase to observe the problem on 5.3.

Re: Query with join_cache_level = 6 returns extra rows in maria-5.3-dsmrr-cpk
The " ... WHERE table1.pk" part of the query seems to play some important role. When it is not present, MRR scan has 17 ranges (3 records with NULL values are excluded); When it is present, MRR scan is done over 20 ranges, and it seems that bka_range_seq_next() and co. are unable to figure out that there is no way they could unpack a NULL value into a key image of non-NULLable column. They unpack some value (garbage?), and MRR implementation uses it to make a lookup and produces extra row.

Re: Query with join_cache_level = 6 returns extra rows in maria-5.3-dsmrr-cpk
Ok, the presense of " ... WHERE table1.pk" plays a role because of a bug in "Early NULLs filtering" feature.

This feature works basically as follows: if we have a ref or eq_ref access on "t1.key=t0.col", then we will add "t0.col IS NOT NULL" to t0's part of WHERE condition. If t0 has no other condition, then everything is ok we execute with added IS NOT NULL (although that is not visible in EXPLAIN)
When t0 does have some other part of WHERE (e.g. WHERE table1.pk) then the code in make_join_select() will [accidentally] overwrite "t0.col IS NOT NULL" when attaching it to t0, and so, NULLs will not be filtered out and we'll get into BKA problem described above.

There are two things to fix there
1. Early NULLs filtering should work irrespectively of what other irrelevant conditions might be in the WHERE clause.

2. BKA code should have means to deal with "t.not_null_key=nullable_value" problem. (It can't rely on early NULLs filtering because it doesn't work for e.g. outer joins, while BKA does process outer joins)

Re: Query with join_cache_level = 6 returns extra rows in maria-5.3-dsmrr-cpk
The bug was fixed when doing BKA development for MWL#128. Added a testcase to DS-MRR works.

Launchpad bug id: 623300