Wrong result with Aria and MRR in maria-5.3-mwl128-dsmrr-cpk

[Philip Stoev]

The following query:

SELECT count(table1.col_time_key )
FROM t1 AS table1 JOIN
( t2 AS table2 JOIN t2 AS table3 ON table3.col_int_key <= table2.col_int_nokey )
 ON table3.pk < table2.col_int_key ;

returns a smaller number of rows when executed under optimizer_use_mrr=force with maria-5.3-mwl128-dsmrr-cpk. maria-5.3 returns a correct result. maria-5.3-dsmrr-cpk crashes. Patch for bug 665049 was used for all trees (applied manually if not naturally present). engine_condition_pushdown and mrr_sort_keys do not seem to be involved.

Note that the EXPLAIN plan is the same with both optimizer_use_mrr="force" and with "disable". No MRR is mentioned, which is a cause for concern. MRR should be reflected in the EXPLAIN anytime it is used.

[Philip Stoev (pstoev-askmonty)]

Test case:

SET SESSION join_cache_level = 0;
SET SESSION join_buffer_size = 10240;

--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_time_key time NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_time_key (col_time_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=Maria AUTO_INCREMENT=30 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t1 VALUES (10,3,8,'00:00:00','v'),(11,3,8,'00:00:00','f'),(12,3,5,'00:55:47','v'),(13,2,8,'00:00:00','s'),(14,1,8,'20:51:59','a'),(15,0,6,'09:47:27','p'),(16,8,7,'21:58:29','z'),(17,5,2,'22:45:53','a'),(18,9,5,'14:06:48','h'),(19,5,7,'22:17:16','h'),(20,4,2,'14:59:37','v'),(21,2,9,'23:37:40','v'),(22,33,142,'14:14:01','b'),(23,5,3,'02:54:19','y'),(24,1,0,'06:34:26','v'),(25,9,3,'18:07:38','m'),(26,1,5,'13:55:23','z'),(27,3,9,'20:32:28','n'),(28,8,1,'11:57:44','d'),(29,231,107,'03:10:35','a');
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_time_key time NOT NULL,
  col_varchar_key varchar(1) NOT NULL,
  PRIMARY KEY (pk),
  KEY col_int_key (col_int_key),
  KEY col_time_key (col_time_key),
  KEY col_varchar_key (col_varchar_key,col_int_key)
) ENGINE=Maria AUTO_INCREMENT=21 DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1;
INSERT INTO t2 VALUES (1,2,9,'11:28:45','x'),(2,9,5,'12:56:25','g'),(3,6,1,'03:03:49','o'),(4,7,0,'12:43:48','g'),(5,0,1,'18:46:00','v'),(6,97,190,'00:35:27','m'),(7,3,6,'08:36:13','x'),(8,3,3,'08:38:26','c'),(9,4,4,'17:08:31','z'),(10,9,3,'06:19:11','i'),(11,101,186,'07:59:20','x'),(12,0,1,'09:25:46','g'),(13,8,8,'02:31:05','q'),(14,194,226,'02:52:34','m'),(15,148,133,'00:00:00','p'),(16,9,6,'00:27:57','e'),(17,9,3,'12:57:46','t'),(18,1,8,'09:29:35','j'),(19,1,5,'22:52:46','h'),(20,5,7,'21:50:03','w');

SET SESSION optimizer_use_mrr = 'force';

SELECT count(table1.col_time_key)
FROM t1 AS table1 JOIN
( t2 AS table2 JOIN t2 AS table3 ON table3.col_int_key <= table2.col_int_nokey )
 ON table3.pk < table2.col_int_key ;

EXPLAIN SELECT count(table1.col_time_key)
FROM t1 AS table1 JOIN
( t2 AS table2 JOIN t2 AS table3 ON table3.col_int_key <= table2.col_int_nokey )
 ON table3.pk < table2.col_int_key ;

SET SESSION optimizer_use_mrr = 'disable';

SELECT count(table1.col_time_key )
FROM t1 AS table1 JOIN
( t2 AS table2 JOIN t2 AS table3 ON table3.col_int_key <= table2.col_int_nokey )
 ON table3.pk < table2.col_int_key ;

EXPLAIN SELECT count(table1.col_time_key )
FROM t1 AS table1 JOIN
( t2 AS table2 JOIN t2 AS table3 ON table3.col_int_key <= table2.col_int_nokey )
 ON table3.pk < table2.col_int_key ;

The EXPLAIN in both cases looks like this:

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE table1 index NULL col_time_key 3 NULL 20 Using index
1 SIMPLE table2 ALL col_int_key NULL NULL NULL 20
1 SIMPLE table3 ALL PRIMARY,col_int_key NULL NULL ...

[Philip Stoev (pstoev-askmonty)]

Simpler test case:

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
pk int(11),
col_int_nokey int(11),
col_int_key int(11),
col_time_key time,
col_varchar_key varchar(1) COLLATE latin1_swedish_ci,
PRIMARY KEY (pk),
KEY (col_int_key),
KEY (col_time_key),
KEY (col_varchar_key),
KEY (col_int_key)) ENGINE=Aria;
INSERT INTO t2 VALUES ('1','2','9','11:28:45','x');
INSERT INTO t2 VALUES ('17','9','3','12:57:46','t');
INSERT INTO t2 VALUES ('20','5','7','21:50:03','w');

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
pk int(11),
col_int_nokey int(11),
col_int_key int(11),
col_time_key time,
col_varchar_key varchar(1) COLLATE latin1_swedish_ci,
PRIMARY KEY (pk),
KEY (col_int_key),
KEY (col_time_key),
KEY (col_varchar_key),
KEY (col_int_key)) ENGINE=Aria;
INSERT INTO t1 VALUES ('29','231','107','03:10:35','a');

                SELECT count(table1.col_time_key) FROM t1 AS table1 JOIN ( t2 AS table2 JOIN t2 AS table3 ON table3.col_int_key <= table2.col_int_nokey ) ON table3.pk < table2.col_int_key ;

[Philip Stoev (pstoev-askmonty)]

Even shorter test case:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
pk int(11),
col_int_nokey int(11),
col_int_key int(11),
col_time_key time,
col_varchar_key varchar(1) COLLATE latin1_swedish_ci) ENGINE=Aria;
INSERT INTO t1 VALUES ('2','2','1','0','a');

DROP TABLE IF EXISTS t2;
CREATE TABLE t2 (
pk int(11),
col_int_nokey int(11),
col_int_key int(11),
col_time_key time,
col_varchar_key varchar(1) COLLATE latin1_swedish_ci,
PRIMARY KEY (pk),
KEY (col_int_key)) ENGINE=Aria;
INSERT INTO t2 VALUES ('1','2','9','1','x');
INSERT INTO t2 VALUES ('17','9','3','1','t');
INSERT INTO t2 VALUES ('2','5','7','2','w');

SELECT count(table1.col_time_key) FROM t1 AS table1 JOIN ( t2 AS table2 JOIN t2 AS table3 ON table3.col_int_key <= table2.col_int_nokey ) ON table3.pk < table2.col_int_key;

[Sergey Petrunia (sergefp)]

Not repeatable anymore with the latest lp:~maria-captains/maria/maria-5.3-mwl128-dsmrr-cpk tree (as of <email address hidden>). Assuming fixed.