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

Wrong result after altering a partitioned table

    XMLWordPrintable

Details

    • 10.2.10

    Description

      After altering a partitioned table, some queries can return invalid results.

      To reproduce, first create a partitioned table and insert some data:

      drop table if exists t;
       
      CREATE TABLE `t` (
      `id` bigint(20) unsigned NOT NULL auto_increment,
      `d` date NOT NULL, 
      `a` bigint(20) unsigned NOT NULL,
      `b` smallint(5) unsigned DEFAULT NULL,
      PRIMARY KEY (`id`,`d`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_cs STATS_SAMPLE_PAGES=200
      /*!50500 PARTITION BY RANGE COLUMNS(d)
      (PARTITION p20170913 VALUES LESS THAN ('2017-09-14') ENGINE = InnoDB,
      PARTITION p20170914 VALUES LESS THAN ('2017-09-15') ENGINE = InnoDB,
      PARTITION p20170915 VALUES LESS THAN ('2017-09-16') ENGINE = InnoDB,
      PARTITION p20170916 VALUES LESS THAN ('2017-09-17') ENGINE = InnoDB,
      PARTITION p20170917 VALUES LESS THAN ('2017-09-18') ENGINE = InnoDB,
      PARTITION p99991231 VALUES LESS THAN (MAXVALUE) ENGINE = InnoDB) */;
       
      insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
      insert into t(d,a,b) values ('2017-09-15',rand()*10000,rand()*10);
       
      replace into t(d,a,b) select '2017-09-15',rand()*10000,rand()*10 from t t1, t t2, t t3, t t4, t t5, t t6, t t7, t t8, t t9, t t10, t t11, t t12, t t13, t t14;
      

      We can see that the table has over 16k rows:

      MariaDB [db1]> select count(*) from t where d ='2017-09-15';
      +----------+
      | count(*) |
      +----------+
      |    16386 |
      +----------+
      1 row in set (0.00 sec)
      

      But things break if we alter the table:

      ALTER TABLE t CHANGE b c smallint(5) unsigned , ADD KEY idx_d_a (d, a);
      analyze table t;
      

      Now that the table has been altered, let's compare the output of these two queries:

      select count(*) from t where d ='2017-09-15';
      select count(*) from t force index(primary) where d ='2017-09-15';
      

      Here's the actual output:

      MariaDB [db1]> select count(*) from t where d ='2017-09-15';
      +----------+
      | count(*) |
      +----------+
      |        0 |
      +----------+
      1 row in set (0.00 sec)
       
      MariaDB [db1]> select count(*) from t force index(primary) where d ='2017-09-15';
      +----------+
      | count(*) |
      +----------+
      |    16386 |
      +----------+
      1 row in set (0.00 sec)
      

      We can see that output for the first query is incorrect.

      Rebuilding the table seems to fix it:

      MariaDB [db1]> alter table t engine = InnoDB;
      Query OK, 0 rows affected (0.24 sec)
      Records: 0  Duplicates: 0  Warnings: 0
       
      MariaDB [db1]> select count(*) from t where d ='2017-09-15';
      +----------+
      | count(*) |
      +----------+
      |    16386 |
      +----------+
      1 row in set (0.00 sec)
      

      Attachments

        Issue Links

          Activity

            People

              jplindst Jan Lindström (Inactive)
              GeoffMontee Geoff Montee (Inactive)
              Votes:
              2 Vote for this issue
              Watchers:
              7 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.