Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Major
-
Resolution: Fixed
-
Affects Version/s: 5.5.37, 10.0.11
-
Component/s: None
-
Labels:
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.
Gliffy Diagrams
Attachments
Issue Links
- is duplicated by
-
-
- Closed
-
- links to
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Hi Federico,
Thanks for the report and the great test case.
It seems to be an upstream bug, and as you know we report those to Oracle as well. Would you like to do it yourself, or should I create a bug report on your behalf?
Sorry, I didn't test it on MySQL before. Reported:
Thanks!
Let's see if it gets fixed in 5.5 any time soon; otherwise we might need to fix it ourselves.
Apparently it is a MySQL regression bug that is or will be Fixed in MySQL 5.5.39, 5.6.20, 5.7.5.
You can see the fix as [6 Dec 2013 17:11] Mattias Jonsson in:
http://bugs.mysql.com/bug.php?id=71095#
I applied this patch to my MariaDB source project and it works!
Thanks for the pointer Olivier. Looked at their fix, seems to be correct. I've backported it and added all the testcases.
In this case, an impossible where is NOT detected. I suspect it is the same bug, but if you want me to report it separately, please let me know.
MariaDB [test]> 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') -> ); Query OK, 0 rows affected (1.02 sec) MariaDB [test]> 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 | t | p1 | ALL | NULL | NULL | NULL | NULL | 2 | Using where | +------+-------------+-------+------------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.01 sec)