Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.27, 10.1.26, 10.0(EOL), 10.1(EOL), 10.2(EOL)
-
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
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-