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

Wrong result with Aria table populated with disabled keys

    XMLWordPrintable

Details

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

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.