Diverging results in maria-5.3-mwl128-dsmrr-cpk with join buffer (incremental, BKA join)

[Philip Stoev]

The following query

SELECT table2.col_varchar_key FROM t2 STRAIGHT_JOIN ( t1 AS table2 JOIN t1 AS table3 ON table3.pk ) ON table3.col_varchar_key = table2.col_varchar_key AND table3.col_varchar_key = table2.col_varchar_nokey ;

Returns 999 rows when executed with BKA in maria-5.3-mwl128-dsmrr-cpk . All other execution plans and trees (maria-5.3 and maria-5.3-mwl128) agree that the query should return 1000 rows.

[Philip Stoev (pstoev-askmonty)]

Test case:

--disable_abort_on_error
SET SESSION optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_condition_pushdown=on,firstmatch=on,loosescan=on,materialization=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr_sort_keys=on,outer_join_with_cache=off,semijoin_with_cache=off,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,table_elimination=on';
SET SESSION optimizer_use_mrr = 'force';
SET SESSION join_cache_level = 6;
SET SESSION join_buffer_size = 136;
SET SESSION debug = '';
--enable_abort_on_error

--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_key int(11) NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar_nokey 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=MyISAM AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
INSERT INTO t1 VALUES (10,8,'v','v'),(11,8,'f','f'),(12,5,'v','v'),(13,8,'s','s'),(14,8,'a','a'),(15,6,'p','p'),(16,7,'z','z'),(17,2,'a','a'),(18,5,'h','h'),(19,7,'h','h'),(20,2,'v','v'),(21,9,'v','v'),(22,142,'b','b'),(23,3,'y','y'),(24,0,'v','v'),(25,3,'m','m'),(26,5,'z','z'),(27,9,'n','n'),(28,1,'d','d'),(29,107,'a','a');
CREATE TABLE t2 (
  pk int(11) NOT NULL AUTO_INCREMENT,
  col_int_key int(11) NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  col_varchar_nokey 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=MyISAM AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
INSERT INTO t2 VALUES (1,9,'x','x'),(2,5,'g','g'),(3,1,'o','o'),(4,0,'g','g'),(5,1,'v','v'),(6,190,'m','m'),(7,6,'x','x'),(8,3,'c','c'),(9,4,'z','z'),(10,3,'i','i'),(11,186,'x','x'),(12,1,'g','g'),(13,8,'q','q'),(14,226,'m','m'),(15,133,'p','p'),(16,6,'e','e'),(17,3,'t','t'),(18,8,'j','j'),(19,5,'h','h'),(20,7,'w','w');

SELECT COUNT(table2.col_varchar_key) FROM t2 STRAIGHT_JOIN ( t1 AS table2 JOIN t1 AS table3 ON table3.pk ) ON table3.col_varchar_key = table2.col_varchar_key AND table3.col_varchar_key = table2.col_varchar_nokey ;
EXPLAIN SELECT COUNT(table2.col_varchar_key) FROM t2 STRAIGHT_JOIN ( t1 AS table2 JOIN t1 AS table3 ON table3.pk ) ON table3.col_varchar_key = table2.col_varchar_key AND table3.col_varchar_key = table2.col_varchar_nokey ;

[Sergey Petrunia (sergefp)]

One can get a simpler testcase by doing

DELETE FROM t2 WHERE pk <> 1;

Then the query will produce 47 rows instead of 50 (as opposed to 999 instead of 1000).

[Sergey Petrunia (sergefp)]

* One can repeat the problem with default @@join_buffer_size setting (i.e. this is not related to buffer overflows). The only requirement is that @@join_cache_level==6.

* The problem goes away when one runs

set optimizer_switch='index_condition_pushdown=off';

and comes back when one runs

set optimizer_switch='index_condition_pushdown=on';

Looks like something with DS-MRR and index condition pushdown.

[Sergey Petrunia (sergefp)]

Also, the problem goes away if one turns off key sorting:

set optimizer_switch='mrr_sort_keys=off';

makes the query produce correct result, irrespectively of index_condition_pushdown setting.