Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-4970

Wrong result with Aria table populated with disabled keys

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.4, 5.5.33
    • Fix Version/s: 5.5.35
    • Component/s: None
    • Labels:

      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)

        Attachments

          Activity

            People

            • Assignee:
              monty Michael Widenius
              Reporter:
              elenst Elena Stepanova
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: