Re: Query returns less rows when run with join_cache_level=6 on 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,
`col_int_key` int(11) DEFAULT NULL,
`col_varchar_key` varchar(1) DEFAULT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO `CC` VALUES (10,7,8,'v');
INSERT INTO `CC` VALUES (11,1,9,'r');
INSERT INTO `CC` VALUES (12,5,9,'a');
INSERT INTO `CC` VALUES (13,3,186,'m');
INSERT INTO `CC` VALUES (14,6,NULL,'y');
INSERT INTO `CC` VALUES (15,92,2,'j');
INSERT INTO `CC` VALUES (16,7,3,'d');
INSERT INTO `CC` VALUES (17,NULL,0,'z');
INSERT INTO `CC` VALUES (18,3,133,'e');
INSERT INTO `CC` VALUES (19,5,1,'h');
INSERT INTO `CC` VALUES (20,1,8,'b');
INSERT INTO `CC` VALUES (21,2,5,'s');
INSERT INTO `CC` VALUES (22,NULL,5,'e');
INSERT INTO `CC` VALUES (23,1,8,'j');
INSERT INTO `CC` VALUES (24,0,6,'e');
INSERT INTO `CC` VALUES (25,210,51,'f');
INSERT INTO `CC` VALUES (26,8,4,'v');
INSERT INTO `CC` VALUES (27,7,7,'x');
INSERT INTO `CC` VALUES (28,5,6,'m');
INSERT INTO `CC` VALUES (29,NULL,4,'c');

SET SESSION join_optimizer_level=6;

SELECT table2 .`pk`
FROM CC LEFT JOIN CC table2 JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ON table3 .`col_int_nokey` ;

SET SESSION join_optimizer_level=0;

SELECT table2 .`pk`
FROM CC LEFT JOIN CC table2 JOIN CC table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` ON table3 .`col_int_nokey` ;

Re: Query returns less rows when run with join_cache_level=6 on maria-5.3-dsmrr-cpk
Not repeatable after the most recent fixes.

Tip changeset I've tried with:
revno: 2831
committer: Sergey Petrunya <psergey@askmonty.org>
branch nick: maria-5.3-dsmrr-cpk-r5
timestamp: Wed 2010-09-15 20:58:38 +0400
message:
BUG#623300: Query with join_cache_level = 6 returns extra rows in maria-5.3-dsmrr-cpk

• First part of the fix: enable Early NULLs filtering to work when WHERE clause is present

Re: [Bug 623315] Re: Query returns less rows when run with join_cache_level=6 on maria-5.3-dsmrr-cpk
Hi Philip, Sergey

On 16/09/2010, at 5:31 PM, Sergey Petrunia wrote:
> Not repeatable after the most recent fixes.
>
> Tip changeset I've tried with:
> revno: 2831
> committer: Sergey Petrunya <psergey@askmonty.org>
> branch nick: maria-5.3-dsmrr-cpk-r5
> timestamp: Wed 2010-09-15 20:58:38 +0400
> message:
> BUG#623300: Query with join_cache_level = 6 returns extra rows in
> maria-5.3-dsmrr-cpk
> - First part of the fix: enable Early NULLs filtering to work when
> WHERE clause is present

Will Philip's test be put into the test suite anyway, to be sure we
catch regression?

Cheers,
Arjen.
–
Arjen Lentz, Exec.Director @ Open Query (http://openquery.com)
Remote expertise & maintenance for MySQL/MariaDB server environments.

Follow us at http://openquery.com/blog/ & http://twitter.com/openquery

Re: Query returns less rows when run with join_cache_level=6 on maria-5.3-dsmrr-cpk
Yes, added testcase to the testsuite.

Launchpad bug id: 623315