[MDEV-3410] LP:665049 - Different result with different join_cache_level and/or join_cache options in maria-5.3-mwl128 Created: 2010-10-22  Updated: 2015-02-02  Resolved: 2012-10-04

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major
Reporter: Philip Stoev (Inactive) Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: Launchpad

Attachments: XML File LPexportBug665049.xml    

 Description   

The following query:

SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

Returns a different result in maria-5.3-mwl128 depending on the join_cache_level and/or the join_cache optimizer_switch options. Maria 5.3-main returns the same result in all join_cache_levels.

 Comments   
Comment by Philip Stoev (Inactive) [ 2010-10-22 ]

Re: Different result with different join_cache_level and/or join_cache options in maria-5.3-mwl128
Test case with varying optimizer_switch:

CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) NOT NULL,
`col_varchar_key` varchar(1) NOT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MARIA AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO `C` VALUES (1,9,'x');
INSERT INTO `C` VALUES (2,5,'g');
INSERT INTO `C` VALUES (3,1,'o');
INSERT INTO `C` VALUES (4,0,'g');
INSERT INTO `C` VALUES (5,1,'v');
INSERT INTO `C` VALUES (6,190,'m');
INSERT INTO `C` VALUES (7,6,'x');
INSERT INTO `C` VALUES (8,3,'c');
INSERT INTO `C` VALUES (9,4,'z');
INSERT INTO `C` VALUES (10,3,'i');
INSERT INTO `C` VALUES (11,186,'x');
INSERT INTO `C` VALUES (12,1,'g');
INSERT INTO `C` VALUES (13,8,'q');
INSERT INTO `C` VALUES (14,226,'m');
INSERT INTO `C` VALUES (15,133,'p');
INSERT INTO `C` VALUES (16,6,'e');
INSERT INTO `C` VALUES (17,3,'t');
INSERT INTO `C` VALUES (18,8,'j');
INSERT INTO `C` VALUES (19,5,'h');
INSERT INTO `C` VALUES (20,7,'w');

SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION join_cache_level = 6;
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION optimizer_switch='join_cache_incremental=off';
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION optimizer_switch='join_cache_bka=off';
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION optimizer_switch='join_cache_hashed=off';
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

Comment by Philip Stoev (Inactive) [ 2010-10-22 ]

Re: Different result with different join_cache_level and/or join_cache options in maria-5.3-mwl128
Test case with different join_cache_levels

CREATE TABLE `C` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`col_int_key` int(11) NOT NULL,
`col_varchar_key` varchar(1) NOT NULL,
PRIMARY KEY (`pk`),
KEY `col_int_key` (`col_int_key`),
KEY `col_varchar_key` (`col_varchar_key`,`col_int_key`)
) ENGINE=MARIA AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO `C` VALUES (1,9,'x');
INSERT INTO `C` VALUES (2,5,'g');
INSERT INTO `C` VALUES (3,1,'o');
INSERT INTO `C` VALUES (4,0,'g');
INSERT INTO `C` VALUES (5,1,'v');
INSERT INTO `C` VALUES (6,190,'m');
INSERT INTO `C` VALUES (7,6,'x');
INSERT INTO `C` VALUES (8,3,'c');
INSERT INTO `C` VALUES (9,4,'z');
INSERT INTO `C` VALUES (10,3,'i');
INSERT INTO `C` VALUES (11,186,'x');
INSERT INTO `C` VALUES (12,1,'g');
INSERT INTO `C` VALUES (13,8,'q');
INSERT INTO `C` VALUES (14,226,'m');
INSERT INTO `C` VALUES (15,133,'p');
INSERT INTO `C` VALUES (16,6,'e');
INSERT INTO `C` VALUES (17,3,'t');
INSERT INTO `C` VALUES (18,8,'j');
INSERT INTO `C` VALUES (19,5,'h');
INSERT INTO `C` VALUES (20,7,'w');

SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION join_cache_level = 1;
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION join_cache_level = 2;
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION join_cache_level = 3;
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION join_cache_level = 4;
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION join_cache_level = 5;
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION join_cache_level = 6;
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION join_cache_level = 7;
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

SET SESSION join_cache_level = 8;
SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;
EXPLAIN SELECT COUNT(table1 .`col_varchar_key`) FROM C table1 STRAIGHT_JOIN ( C table2 STRAIGHT_JOIN C table3 ON table3 .`col_varchar_key` = table2 .`col_varchar_key` AND table3 .`col_int_key` < table2 .`col_int_key` ) ON table3 .`pk` ;

Comment by Igor Babaev [ 2010-10-30 ]

Re: Different result with different join_cache_level and/or join_cache options in maria-5.3-mwl128
The problem is reproducible for the 5.3 tree.
The fix has been pushed into this tree.

Comment by Rasmus Johansson (Inactive) [ 2011-12-13 ]

Launchpad bug id: 665049

Generated at Thu Feb 08 06:48:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.