[MDEV-6240] Wrong "Impossible where" with LIST partitioning Created: 2014-05-15  Updated: 2020-12-08  Resolved: 2017-10-03

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.5.37, 10.0.11
Fix Version/s: 5.5.39, 10.0.13

Type: Bug Priority: Major
Reporter: Federico Razzoli Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: partitioning, upstream

Issue Links:
Duplicate
is duplicated by MDEV-6322 The PARTITION engine can return wrong... Closed

 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.

 Comments   
Comment by Federico Razzoli [ 2014-05-15 ]

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)

Comment by Elena Stepanova [ 2014-05-30 ]

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?

Comment by Federico Razzoli [ 2014-05-30 ]

Sorry, I didn't test it on MySQL before. Reported:

http://bugs.mysql.com/bug.php?id=72803

Comment by Elena Stepanova [ 2014-05-30 ]

Thanks!

Let's see if it gets fixed in 5.5 any time soon; otherwise we might need to fix it ourselves.

Comment by Olivier Bertrand [ 2014-06-10 ]

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!

Comment by Sergei Petrunia [ 2014-07-23 ]

Thanks for the pointer Olivier. Looked at their fix, seems to be correct. I've backported it and added all the testcases.

Comment by Olivier Bertrand [ 2017-09-25 ]

Testing recently this example with MariaDB 10.2.6 this bug is not fixed!

Comment by Olivier Bertrand [ 2017-09-25 ]

Not fixed in MariaDB 10.2.6

Comment by Sergei Petrunia [ 2017-10-02 ]

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?

Comment by Sergei Petrunia [ 2017-10-02 ]

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

Comment by Elena Stepanova [ 2017-10-02 ]

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.

Comment by Olivier Bertrand [ 2017-10-03 ]

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.

Generated at Thu Feb 08 07:10:23 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.