Re: Wrong result with non-equality ON clause and join_cache_level > 0
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 (
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_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES (10,3,8,'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');
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_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t2 VALUES (1,2,9,'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');
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 ;
Bug
Major
Re: Wrong result with non-equality ON clause and join_cache_level > 0
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 (
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_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES (10,3,8,'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');
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_key,col_int_key)
) ENGINE=Aria AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t2 VALUES (1,2,9,'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');
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 ;