Description:
Partition pruning does not work with explicit time zone offsets in datetime literals when comparing to TIMESTAMPs.
How to repeat:
DROP TABLE IF EXISTS tp;
CREATE TABLE `tp` (
`id` int unsigned NOT NULL,
`ts` timestamp NOT NULL,
`v` float DEFAULT NULL,
PRIMARY KEY (`id`,`ts`)
)
PARTITION BY RANGE (unix_timestamp(`ts`))
(
PARTITION p202001 VALUES LESS THAN (1580515200),
PARTITION p202002 VALUES LESS THAN (1583020800)
);
SET @@session.time_zone = '+00:00';
SELECT @@SESSION.time_zone;
INSERT INTO tp VALUES (1, '2020-01-01', 1);
INSERT INTO tp VALUES (1, '2020-02-01', 2);
SELECT * FROM tp;
SELECT * FROM tp WHERE ts >= '2020-01-01 00:00:00' AND ts <= '2020-02-01 00:00:00';
EXPLAIN SELECT * FROM tp WHERE ts >= '2020-01-01 00:00:00' AND ts <= '2020-02-01 00:00:00';
SHOW WARNINGS;
SELECT * FROM tp WHERE ts >= '2020-01-01 00:00:00+00:00' AND ts <= '2020-02-01 00:00:00+00:00';
EXPLAIN SELECT * FROM tp WHERE ts >= '2020-01-01 00:00:00+00:00' AND ts <= '2020-02-01 00:00:00+00:00';
SHOW WARNINGS;
SET @@session.time_zone = '+01:00';
SELECT @@SESSION.time_zone;
SELECT * FROM tp;
-- These should be the same as above 00:00:00 in UTC
SELECT * FROM tp WHERE ts >= '2020-01-01 01:00:00' AND ts <= '2020-02-01 01:00:00';
EXPLAIN SELECT * FROM tp WHERE ts >= '2020-01-01 01:00:00' AND ts <= '2020-02-01 01:00:00';
SHOW WARNINGS;
-- This only finds the first row
SELECT * FROM tp WHERE ts >= '2020-01-01 00:00:00+00:00' AND ts <= '2020-02-01 00:00:00+00:00';
-- Because the second partition is pruned
EXPLAIN SELECT * FROM tp WHERE ts >= '2020-01-01 00:00:00+00:00' AND ts <= '2020-02-01 00:00:00+00:00';
SHOW WARNINGS;
ALTER TABLE tp REMOVE PARTITIONING;
SELECT * FROM tp WHERE ts >= '2020-01-01 00:00:00+00:00' AND ts <= '2020-02-01 00:00:00+00:00';
EXPLAIN SELECT * FROM tp WHERE ts >= '2020-01-01 00:00:00+00:00' AND ts <= '2020-02-01 00:00:00+00:00';
SHOW WARNINGS;
DROP TABLE IF EXISTS tp;
Suggested fix:
Fix so datetime literals with time_zone offset works consistently, including with partition pruning?
