Description:
Partitioning itself on TIMESTAMP(N) appears to work correctly [1] and partition pruning by equality works [3], but does not prune when it contains inequalities [4]. Partition pruning with TIMESTAMP (not TIMESTAMP(N)) works fine [5]. Full steps to reproduce on MySQL 5.7, 8.0 are attached. optimizer_trace didn't provide any meaningful information. A blog post in the past pointing out this issue has been published (http://minsql.com/mysql/MySQL-Fractional-timestamp-and-Partition-pruning/).
I understand https://dev.mysql.com/doc/refman/8.0/en/partitioning-range.html says "Any other expressions involving TIMESTAMP values are not permitted. (See Bug #42849.)", but the bug only mentions the need to have a UNIX_TIMESTAMP for timezone alignment, and it doesn't seem like partitioning with FLOOR would cause problems.
I also suspected it's because RANGE does not support non-integer types, according to "Other partitioning types require a partitioning expression that yields an integer value or NULL." /partitioning-types.html), but I don't believe this is the case because the input type is integer thanks to the FLOOR function in the definition of the partition.
TIMESTAMP partitions are often practically used for partitions by date and month. I think it's a big problem for such a heavily used feature to have such a huge performance impact due to silent optimizations not working. In fact, I'm working as a Technical Engineer for his GCP, and a Google Cloud Platform customer was hit by this misoptimization, causing some features in their production environment to go completely down under certain conditions.
Experiments were conducted on Cloud SQL for MySQL on GCP.
Questions:
Do you plan to support partitioning using TIMESTAMP(N) (not TIMESTAMP) in the future?
If yes, I think this silent and non-intuitive behavior should be fixed as a bug or documented that inequality partition pruning does not currently work. For example, users referencing the answer at https://stackoverflow.com/a/58339986 may silently hit this issue.
If no, the partitioning definition itself with TIMESTAMP(N) should be rejected as a syntax error.
Anyway, I think it's worth investigating why this problem occurs in any case.
How to repeat:
CREATE DATABASE test;
USE test;
CREATE TABLE pruning_test_tab (
id int NOT NULL,
created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (FLOOR(UNIX_TIMESTAMP(created_at))) (
PARTITION p202306 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01')),
PARTITION p202307 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-01')),
PARTITION p202308 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-01')),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO pruning_test_tab values (1, '2023-06-01 08:30:00');
INSERT INTO pruning_test_tab values (2, '2023-07-01 08:30:00');
INSERT INTO pruning_test_tab values (3, '2023-08-01 08:30:00');
INSERT INTO pruning_test_tab values (4, '2023-09-01 08:30:00');
-- [1] Data is correctly partitioned
SELECT * FROM pruning_test_tab PARTITION (p202306);
SELECT * FROM pruning_test_tab PARTITION (p202307);
SELECT * FROM pruning_test_tab PARTITION (p202308);
SELECT * FROM pruning_test_tab PARTITION (p3);
-- [3] Partition pruning about equility is enforced.
EXPLAIN SELECT * FROM pruning_test_tab
WHERE
created_at = '2023-08-01 08:30:00';
-- [4] Partition pruning about inequility is NOT enforced.
EXPLAIN SELECT * FROM pruning_test_tab
WHERE
created_at < '2023-06-01 08:30:00';
CREATE DATABASE test;
USE test;
CREATE TABLE pruning_test_tab (
id int NOT NULL,
created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (FLOOR(UNIX_TIMESTAMP(created_at))) (
PARTITION p202306 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01')),
PARTITION p202307 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-01')),
PARTITION p202308 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-01')),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO pruning_test_tab values (1, '2023-06-01 08:30:00');
INSERT INTO pruning_test_tab values (2, '2023-07-01 08:30:00');
INSERT INTO pruning_test_tab values (3, '2023-08-01 08:30:00');
INSERT INTO pruning_test_tab values (4, '2023-09-01 08:30:00');
-- [1] Data is correctly partitioned
SELECT * FROM pruning_test_tab PARTITION (p202306);
SELECT * FROM pruning_test_tab PARTITION (p202307);
SELECT * FROM pruning_test_tab PARTITION (p202308);
SELECT * FROM pruning_test_tab PARTITION (p3);
-- [3] Partition pruning about equility is enforced.
EXPLAIN SELECT * FROM pruning_test_tab
WHERE
created_at = '2023-08-01 08:30:00';
-- [4] Partition pruning about inequility is NOT enforced.
EXPLAIN SELECT * FROM pruning_test_tab
WHERE
created_at < '2023-06-01 08:30:00';
CREATE TABLE pruning_test_tab2 (
id int NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
PARTITION p202306 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01')),
PARTITION p202307 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-01')),
PARTITION p202308 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-01')),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO pruning_test_tab2 values (1, '2023-06-01 08:30:00');
INSERT INTO pruning_test_tab2 values (2, '2023-07-01 08:30:00');
INSERT INTO pruning_test_tab2 values (3, '2023-08-01 08:30:00');
INSERT INTO pruning_test_tab2 values (4, '2023-09-01 08:30:00');
SELECT * FROM pruning_test_tab2 PARTITION (p202306);
SELECT * FROM pruning_test_tab2 PARTITION (p202307);
SELECT * FROM pruning_test_tab2 PARTITION (p202308);
SELECT * FROM pruning_test_tab2 PARTITION (p3);
-- [5] Partition pruning DID WORK for TIMESTAMP (not TIMESTAMP(N))
SET optimizer_trace="enabled=on";
EXPLAIN SELECT * FROM pruning_test_tab2
WHERE
created_at >= '2023-08-01 01:00:00' AND created_at <= '2023-08-02 01:00:00';
SELECT * FROM information_schema.optimizer_trace\G
CREATE TABLE pruning_test_tab2 (
id int NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (UNIX_TIMESTAMP(created_at)) (
PARTITION p202306 VALUES LESS THAN (UNIX_TIMESTAMP('2023-07-01')),
PARTITION p202307 VALUES LESS THAN (UNIX_TIMESTAMP('2023-08-01')),
PARTITION p202308 VALUES LESS THAN (UNIX_TIMESTAMP('2023-09-01')),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
INSERT INTO pruning_test_tab2 values (1, '2023-06-01 08:30:00');
INSERT INTO pruning_test_tab2 values (2, '2023-07-01 08:30:00');
INSERT INTO pruning_test_tab2 values (3, '2023-08-01 08:30:00');
INSERT INTO pruning_test_tab2 values (4, '2023-09-01 08:30:00');
SELECT * FROM pruning_test_tab2 PARTITION (p202306);
SELECT * FROM pruning_test_tab2 PARTITION (p202307);
SELECT * FROM pruning_test_tab2 PARTITION (p202308);
SELECT * FROM pruning_test_tab2 PARTITION (p3);
-- [5] Partition pruning DID WORK for TIMESTAMP (not TIMESTAMP(N))
SET optimizer_trace="enabled=on";
EXPLAIN SELECT * FROM pruning_test_tab2
WHERE
created_at >= '2023-08-01 01:00:00' AND created_at <= '2023-08-02 01:00:00';
SELECT * FROM information_schema.optimizer_trace\G
[1]
MySQL [test]> SELECT * FROM pruning_test_tab PARTITION (p202306)
-> ;
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 1 | 2023-06-01 08:30:00.000 |
+----+-------------------------+
1 row in set (0.002 sec)
MySQL [test]> SELECT * FROM pruning_test_tab PARTITION (p202307);
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 2 | 2023-07-01 08:30:00.000 |
+----+-------------------------+
1 row in set (0.004 sec)
MySQL [test]> SELECT * FROM pruning_test_tab PARTITION (p202308);
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 3 | 2023-08-01 08:30:00.000 |
+----+-------------------------+
1 row in set (0.002 sec)
MySQL [test]> SELECT * FROM pruning_test_tab PARTITION (p3);
+----+-------------------------+
| id | created_at |
+----+-------------------------+
| 4 | 2023-09-01 08:30:00.000 |
+----+-------------------------+
1 row in set (0.002 sec)
[3]
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | pruning_test_tab | p202308 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
[4]
+----+-------------+------------------+----------------------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------+----------------------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | pruning_test_tab | p202306,p202307,p202308,p3 | ALL | NULL | NULL | NULL | NULL | 4 | 33.33 | Using where |
+----+-------------+------------------+----------------------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.002 sec)
[5]
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | pruning_test_tab2 | p202308 | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.001 sec)
