Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
10.1.14, 5.5, 10.0, 10.1
Description
Let's create two tables:
CREATE TABLE partition_test ( |
id int not null, |
status int not null, |
descr varchar(255), |
PRIMARY KEY (id, status) |
) ENGINE=innodb
|
PARTITION BY LIST(status) ( |
partition s1 values in (0,1,2), |
partition s2 values in (3) |
);
|
|
|
CREATE TABLE non_partition_test ( |
id int not null, |
status int not null, |
descr varchar(255), |
PRIMARY KEY (id, status) |
) ENGINE=innodb;
|
And then insert the same data into each:
INSERT INTO partition_test VALUES (1,0,'1/0'), (3,0,'3/0'), (5,0,'5/0'), (2,3,'2/3'), (4,3,'4/3'), (6,3,'6/3'); |
INSERT INTO non_partition_test VALUES (1,0,'1/0'), (3,0,'3/0'), (5,0,'5/0'), (2,3,'2/3'), (4,3,'4/3'), (6,3,'6/3'); |
And then let's compare the results of the following two queries:
MariaDB [db1]> SELECT DISTINCT id, descr FROM partition_test ORDER BY id LIMIT 0,4;
|
+----+-------+
|
| id | descr |
|
+----+-------+
|
| 1 | 1/0 |
|
| 3 | 3/0 |
|
| 5 | 5/0 |
|
| 2 | 2/3 |
|
+----+-------+
|
4 rows in set (0.00 sec)
|
|
|
MariaDB [db1]> SELECT DISTINCT id, descr FROM non_partition_test ORDER BY id LIMIT 0,4;
|
+----+-------+
|
| id | descr |
|
+----+-------+
|
| 1 | 1/0 |
|
| 2 | 2/3 |
|
| 3 | 3/0 |
|
| 4 | 4/3 |
|
+----+-------+
|
4 rows in set (0.00 sec)
|
As you can see from the above results, the results from the partitioned table are not being sorted.
If we remove the DISTINCT keyword, it works:
MariaDB [db1]> SELECT id, descr FROM partition_test ORDER BY id LIMIT 0,4;
|
+----+-------+
|
| id | descr |
|
+----+-------+
|
| 1 | 1/0 |
|
| 2 | 2/3 |
|
| 3 | 3/0 |
|
| 4 | 4/3 |
|
+----+-------+
|
4 rows in set (0.00 sec)
|
If we remove the LIMIT, it also works:
MariaDB [db1]> SELECT DISTINCT id, descr FROM partition_test ORDER BY id;
|
+----+-------+
|
| id | descr |
|
+----+-------+
|
| 1 | 1/0 |
|
| 2 | 2/3 |
|
| 3 | 3/0 |
|
| 4 | 4/3 |
|
| 5 | 5/0 |
|
| 6 | 6/3 |
|
+----+-------+
|
6 rows in set (0.00 sec)
|
Query plan:
MariaDB [db1]> EXPLAIN EXTENDED SELECT DISTINCT id, descr FROM partition_test ORDER BY id LIMIT 0,4;
|
+------+-------------+----------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+----------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|
| 1 | SIMPLE | partition_test | index | NULL | PRIMARY | 8 | NULL | 4 | 100.00 | Using temporary |
|
+------+-------------+----------------+-------+---------------+---------+---------+------+------+----------+-----------------+
|
1 row in set, 1 warning (0.00 sec)
|
|
|
MariaDB [db1]> SHOW WARNINGS;
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | select distinct `db1`.`partition_test`.`id` AS `id`,`db1`.`partition_test`.`descr` AS `descr` from `db1`.`partition_test` order by `db1`.`partition_test`.`id` limit 0,4 |
|
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
1 row in set (0.00 sec)
|
Attachments
Issue Links
- duplicates
-
-
- Closed
-