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

Partitioning does not work with 'ALLOW_INVALID_DATES'

        Details

        • Type: Bug
        • Status: Open
        • Priority: Minor
        • Resolution: Unresolved
        • Affects Version/s: 10.0.8
        • Fix Version/s: 10.0
        • Component/s: None
        • Labels:
          None

          Description

          I create a partitioned table using TO_SECONDS as a range function:

          SET sql_mode='allow_invalid_dates';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (a DATE, KEY(a))
PARTITION BY RANGE (TO_SECONDS(a))
(PARTITION `p0001-01-01` VALUES LESS THAN (TO_SECONDS('0001-01-02')),
 PARTITION `p1001-01-01` VALUES LESS THAN (TO_SECONDS('1001-01-02')),
 PARTITION `p2001-01-01` VALUES LESS THAN (TO_SECONDS('2001-01-02')),
 PARTITION `pmax` VALUES LESS THAN (MAXVALUE));

INSERT INTO t1 VALUES ('0001-01-01');
INSERT INTO t1 VALUES ('0001-02-30');
INSERT INTO t1 VALUES ('1001-01-01');
INSERT INTO t1 VALUES ('1001-02-30');
INSERT INTO t1 VALUES ('2001-01-01');
INSERT INTO t1 VALUES ('2001-02-30');

          Now I want to search for '1001-02-30':
          EXPLAIN PARTITIONS for this value says:

          EXPLAIN PARTITIONS SELECT * FROM t1 WHERE a='1001-02-30';
+------+-------------+-------+-------------+------+---------------+------+---------+-------+------+-------------+
| id   | select_type | table | partitions  | type | possible_keys | key  | key_len | ref   | rows | Extra       |
+------+-------------+-------+-------------+------+---------------+------+---------+-------+------+-------------+
|    1 | SIMPLE      | t1    | p0001-01-01 | ref  | a             | a    | 4       | const |    1 | Using index |
+------+-------------+-------+-------------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)

          It's going to search in the smallest possible partition,
          probably because '1001-02-30' is an invalid date,
          while TO_SECONDS() normally expects a valid date only.

          Now do actual search:

          SELECT * FROM t1 WHERE a='1001-02-30';
Empty set (0.00 sec)

          Oops.

          Let's check which partition it's actually in:

          SELECT * FROM t1 PARTITION(`p2001-01-01`);
+------------+
| a          |
+------------+
| 1001-02-30 |
| 2001-01-01 |
+------------+
2 rows in set (0.00 sec)

          Oops. Why?

          Let's check if it works without partitions:

          ALTER TABLE t1 REMOVE PARTITIONING;
SELECT * FROM t1 WHERE a='1001-02-30';
+------------+
| a          |
+------------+
| 1001-02-30 |
+------------+
1 row in set (0.00 sec)

          It does.

          Partitioning should be fixed to return the same
          row set for the same WHERE clause.

            Gliffy Diagrams

              Attachments

                Activity

                There are no comments yet on this issue.

                  People

                  • Assignee:
                    bar Alexander Barkov
                    Reporter:
                    bar Alexander Barkov
                  • Votes:
                    0 Vote for this issue
                    Watchers:
                    2 Start watching this issue

                    Dates

                    • Created:
                      Updated: