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

Wrong "Impossible where" with LIST partitioning

    XMLWordPrintable

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

              psergei Sergei Petrunia
              f_razzoli Federico Razzoli
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.