Details
Major
Description
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 1 2 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 1 2 3 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
The failure started happening on 5.5 since this revision:
http://bazaar.launchpad.net/~maria-captains/maria/5.5/revision/3766
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)
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Pushed into maria-5.5-monty to be merged into 5.5.35
Fixed in my tree. Will be in 5.5.35
Problem was that ALTER TABLE DISABLE KEYS incremented create_trid for the table, which made the new index entries invisible until the global trid catched up.
Fixed by only updating create_trid if we are rewriting all rows and indexes.