Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.4, 5.5.33
-
None
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)