[#MDEV-4970] Wrong result with Aria table populated with disabled keys

The following test case produces wrong results:

CREATE TABLE t1 (pk INT, d DATETIME, PRIMARY KEY(pk), KEY(d)) ENGINE=Aria;

ALTER TABLE t1 DISABLE KEYS;

INSERT INTO t1 VALUES (1,'2000-01-01 22:22:22'),(2,'2012-12-21 12:12:12');

INSERT INTO t1 VALUES (3, '2008-07-24');

ALTER TABLE t1 ENABLE KEYS;

SELECT t1a.pk FROM t1 AS t1a LEFT JOIN t1 AS t1b ON t1a.pk = t1b.pk;

SELECT * FROM t1 AS t1a LEFT JOIN t1 AS t1b ON t1a.pk = t1b.pk;

Actual result:

SELECT t1a.pk FROM t1 AS t1a LEFT JOIN t1 AS t1b ON t1a.pk = t1b.pk;

pk

SELECT * FROM t1 AS t1a LEFT JOIN t1 AS t1b ON t1a.pk = t1b.pk;

pk	d	pk	d

1	2000-01-01 22:22:22	1	2000-01-01 22:22:22

2	2012-12-21 12:12:12	2	2012-12-21 12:12:12

3	2008-07-24 00:00:00	NULL	NULL

The result is not persistent, the next execution of the same queries produces the expected one:

SELECT t1a.pk FROM t1 AS t1a LEFT JOIN t1 AS t1b ON t1a.pk = t1b.pk;

pk

SELECT * FROM t1 AS t1a LEFT JOIN t1 AS t1b ON t1a.pk = t1b.pk;

pk	d	pk	d

1	2000-01-01 22:22:22	1	2000-01-01 22:22:22

2	2012-12-21 12:12:12	2	2012-12-21 12:12:12

3	2008-07-24 00:00:00	3	2008-07-24 00:00:00

revno: 3766

revision-id: monty@askmonty.org-20130514153216-1xrgzjog47dtoppn

parent: knielsen@knielsen-hq.org-20130514124952-u11q81uyrk9iqkwg

committer: Michael Widenius <monty@askmonty.org>

branch nick: maria-5.5

timestamp: Tue 2013-05-14 18:32:16 +0300

message:

  When one does 'REPAIR TABLE', update uuid() to the current system

Here is a simpler test case, without LEFT JOIN. I didn't check whether it appeared with the same revision, but it looks similar:

CREATE TABLE t1 (pk INT PRIMARY KEY, i INT, KEY(i)) ENGINE=Aria;

ALTER TABLE t1 DISABLE KEYS;

INSERT INTO t1 VALUES (1,11);

INSERT INTO t1 VALUES (2,0),(3,33),(4,0),(5,55),(6,66),(7,0),(8,88),(9,99);

ALTER TABLE t1 ENABLE KEYS;

SELECT * FROM t1 WHERE i = 0 OR pk BETWEEN 6 AND 10;

DROP TABLE t1;

Actual result:

SELECT * FROM t1 WHERE i = 0 OR pk BETWEEN 6 AND 10;

pk	i

2	0

4	0

7	0

(obviously, rows 6,8,9 are missing)

[MDEV-4970] Wrong result with Aria table populated with disabled keys Created: 2013-08-29 Updated: 2014-01-29 Resolved: 2014-01-26
Status:	Closed
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.0.4, 5.5.33
Fix Version/s:	5.5.35