Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6240

Wrong "Impossible where" with LIST partitioning

    Details

      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

            Activity

              People

              • Assignee:
                psergey Sergei Petrunia
                Reporter:
                f_razzoli Federico Razzoli
              • Votes:
                0 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: