Wrong result with non-equality ON clause and join_cache_level > 0 and "(flat, BNL join)"
Bug #669420 reported by
Philip Stoev
This bug affects 1 person
Affects | Status | Importance | Assigned to | Milestone | |
---|---|---|---|---|---|
MariaDB |
Fix Released
|
High
|
Igor Babaev |
Bug Description
The following query:
SELECT COUNT(table1 .col_int_nokey) FROM t1 table1 JOIN ( t2 table2 JOIN t2 table3 ON table2 .col_int_key AND table3 .pk >= table2 .col_int_key ) ON table3 .col_varchar_key >= table2 .col_varchar_key ;
returns different results when executed in maria-5.3 with join_cache_level = 0 and join_cache_level > 0; Also reproducible in maria-5.3-mwl128 with BKA = off.
Changed in maria: | |
status: | New → Confirmed |
importance: | Undecided → High |
Changed in maria: | |
status: | Confirmed → In Progress |
Changed in maria: | |
status: | In Progress → Fix Committed |
Changed in maria: | |
status: | Fix Committed → Fix Released |
To post a comment you must log in.
Test case:
SET SESSION join_buffer_size = 102400;
--disable_warnings
DROP TABLE /*! IF EXISTS */ t1;
DROP TABLE /*! IF EXISTS */ t2;
--enable_warnings
CREATE TABLE t1 ( key,col_ int_key) 'v'),(11, 3,8,'f' ),(12,3, 5,'v'), (13,2,8, 's'),(14, 1,8,'a' ),(15,0, 6,'p'), (16,8,7, 'z'),(17, 5,2,'a' ),(18,9, 5,'h'), (19,5,7, 'h'); key,col_ int_key) 'x'),(2, 9,5,'g' ),(3,6, 1,'o'), (4,7,0, 'g'),(5, 0,1,'v' ),(6,97, 190,'m' ),(7,3, 6,'x'), (8,3,3, 'c'),(9, 4,4,'z' ),(10,9, 3,'i'), (11,101, 186,'x' ),(12,0, 1,'g'), (13,8,8, 'q'),(14, 194,226, 'm'),(15, 148,133, 'p'),(16, 9,6,'e' ),(17,9, 3,'t'), (18,1,8, 'j'),(19, 1,5,'h' ),(20,5, 7,'w');
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_nokey int(11) NOT NULL,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_
) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES (10,3,8,
CREATE TABLE t2 (
pk int(11) NOT NULL AUTO_INCREMENT,
col_int_nokey int(11) NOT NULL,
col_int_key int(11) NOT NULL,
col_varchar_key varchar(1) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL,
PRIMARY KEY (pk),
KEY col_int_key (col_int_key),
KEY col_varchar_key (col_varchar_
) ENGINE=Aria AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t2 VALUES (1,2,9,
SET SESSION join_cache_level=0;
SELECT COUNT(table1 .col_int_nokey) FROM t1 table1 JOIN ( t2 table2 JOIN t2 table3 ON table2 .col_int_key AND table3 .pk >= table2 .col_int_key ) ON table3 .col_varchar_key >= table2 .col_varchar_key ;
EXPLAIN SELECT COUNT(table1 .col_int_nokey) FROM t1 table1 JOIN ( t2 table2 JOIN t2 table3 ON table2 .col_int_key AND table3 .pk >= table2 .col_int_key ) ON table3 .col_varchar_key >= table2 .col_varchar_key ;
SET SESSION join_cache_level=1;
SELECT COUNT(table1 .col_int_nokey) FROM t1 table1 JOIN ( t2 table2 JOIN t2 table3 ON table2 .col_int_key AND table3 .pk >= table2 .col_int_key ) ON table3 .col_varchar_key >= table2 .col_varchar_key ;
EXPLAIN SELECT COUNT(table1 .col_int_nokey) FROM t1 table1 JOIN ( t2 table2 JOIN t2 table3 ON table2 .col_int_key AND table3 .pk >= table2 .col_int_key ) ON table3 .col_varchar_key >= table2 .col_varchar_key ;