| Bug #72803 | Wrong "Impossible where" with LIST partitioning | ||
|---|---|---|---|
| Submitted: | 29 May 2014 22:48 | Modified: | 30 Jun 2014 12:32 |
| Reporter: | Federico Razzoli | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: Partitions | Severity: | S1 (Critical) |
| Version: | 5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[29 May 2014 22:49]
Federico Razzoli
I originally reported this to MariaDB, but it turned out to be an upstream bug: https://mariadb.atlassian.net/browse/MDEV-6240
[30 May 2014 2:35]
MySQL Verification Team
this is a duplicate of something that has been already already in the current source tree.. let me find it...
testcase:
----------
drop table if exists `t`;
create table `t` (`d` date) engine = innodb partition by list columns (`d`)
(partition `p0` values in ('1990-01-01','1991-01-01'),partition `p1` values in ('1981-01-01'));
insert `t`(`d`) values('1991-01-01');
select * from `t` where `d` = '1991-01-01';
select * from `t`;
[30 May 2014 2:37]
MySQL Verification Team
maybe this: http://bugs.mysql.com/bug.php?id=71095
[30 May 2014 9:16]
Federico Razzoli
Here an impossible WHERE is *not* detected. Seems to me the same bug, but please let me know if it should be reported separatedly.
mysql> CREATE OR REPLACE TABLE test.t (
-> d DATE
-> )
-> ENGINE = InnoDB
-> PARTITION BY LIST COLUMNS (d)
-> (
-> PARTITION p0 VALUES IN ('1981-01-01'),
-> PARTITION p1 VALUES IN ('1990-01-01')
-> );
Query OK, 0 rows affected (0,65 sec)
mysql> EXPLAIN PARTITIONS SELECT * FROM test.t WHERE d > '1990-01-01';
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | t | p1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0,04 sec)
[30 May 2014 12:32]
MySQL Verification Team
Please re-open this bug report or close it when testing against the 5.6.20 (the bug pointed by Shane will be fixed in this version). Thanks.
[1 Jul 2014 1:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: See the example below. Impossible WHERE detected, empty resultset... but the WHERE is not impossible: it searches for an existing row, which is returned if I omit the WHERE. How to repeat: mysql> SELECT VERSION(); +-----------+ | VERSION() | +-----------+ | 5.6.17 | +-----------+ 1 row in set (0,00 sec) mysql> CREATE TABLE test.t ( -> d DATE -> ) -> ENGINE = InnoDB -> PARTITION BY LIST COLUMNS (d) -> ( -> PARTITION p0 VALUES IN ('1990-01-01','1991-01-01'), -> PARTITION p1 VALUES IN ('1981-01-01') -> ); Query OK, 0 rows affected (0,62 sec) mysql> INSERT INTO test.t (d) VALUES ('1991-01-01'); Query OK, 1 row affected (0,06 sec) mysql> EXPLAIN PARTITIONS SELECT * FROM test.t WHERE d = '1991-01-01'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 0 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0,02 sec) mysql> SELECT * FROM test.t WHERE d = '1991-01-01'; Empty set (0,00 sec) mysql> SELECT * FROM test.t; +------------+ | d | +------------+ | 1991-01-01 | +------------+ 1 row in set (0,00 sec)