Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.37, 10.0.11
Description
I think this is the simplest way to repeat the bug:
MariaDB [test]> CREATE OR REPLACE 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') |
-> );
|
Query OK, 0 rows affected (1.00 sec) |
|
|
MariaDB [test]> INSERT INTO t (d) VALUES ('1991-01-01'); |
Query OK, 1 row affected (0.12 sec)
|
|
|
MariaDB [test]> EXPLAIN PARTITIONS SELECT * FROM t WHERE d = '1991-01-01'; |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra | |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | |
+------+-------------+-------+------------+------+---------------+------+---------+------+------+-----------------------------------------------------+ |
1 row in set (0.10 sec) |
|
|
MariaDB [test]> SELECT * FROM t WHERE d = '1991-01-01'; |
Empty set (0.00 sec) |
|
|
MariaDB [test]> SELECT * FROM t; |
+------------+ |
| d |
|
+------------+ |
| 1991-01-01 |
|
+------------+ |
1 row in set (0.00 sec) |
Notice that the inserted row DOES match the queries. Without the WHERE, the SELECT finds it.
If you re-define the table without the first value for p0, the bug disappears.
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- links to
Activity
I am using the following MTR .test file:
--source include/not_embedded.inc
|
--source include/have_partition.inc
|
--source include/have_innodb.inc
|
|
|
CREATE OR REPLACE 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 INTO t (d) VALUES ('1991-01-01');
|
EXPLAIN PARTITIONS SELECT * FROM t WHERE d = '1991-01-01';
|
SELECT * FROM t WHERE d = '1991-01-01';
|
|
SELECT * FROM t;
|
drop table t;
|
and I get this result:
CREATE OR REPLACE 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 INTO t (d) VALUES ('1991-01-01');
|
EXPLAIN PARTITIONS SELECT * FROM t WHERE d = '1991-01-01';
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
1 SIMPLE t p0 ALL NULL NULL NULL NULL 2 Using where
|
SELECT * FROM t WHERE d = '1991-01-01';
|
d
|
1991-01-01
|
SELECT * FROM t;
|
d
|
1991-01-01
|
drop table t;
|
both in 10.2.9 and in 10.2.6.
I can also the fix in the revision history and the test for this MDEV in the test suite.
So it looks like the Wrong "Impossible WHERE" part is fixed?
Also checked Frederico's testcase from the first comment (The issue reported there is different: it is about NOT detecting "Impossible WHERE" when it's actually possible to do so):
I get Impossible WHERE on both 10.2.6. and 10.2.9 :
CREATE OR REPLACE TABLE t (
|
d DATE
|
)
|
ENGINE = InnoDB
|
PARTITION BY LIST COLUMNS (d)
|
(
|
PARTITION p0 VALUES IN ('1981-01-01'),
|
PARTITION p1 VALUES IN ('1990-01-01')
|
);
|
EXPLAIN PARTITIONS
|
SELECT * FROM t WHERE d > '1990-01-01';
|
id select_type table partitions type possible_keys key key_len ref rows Extra
|
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables
|
bertrandop, could you please clarify what's not fixed – what exactly you tried, and what you got? I don't see any problem on 10.2.6 with any of the above test cases.
Neither do I!
I don't remember what I exactly did last week (normally it was the example given on the top of this page) but today there are no problem with it.
Sorry about that.
Not fixed in MariaDB 10.2.6