Wrong result with non-equality ON clause and join_cache_level > 0 and "(flat, BNL join)"

[Philip Stoev]

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.

[Philip Stoev (pstoev-askmonty)]

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 ;