Bug #101530 Partition pruning fails with explicit time_zone offset in datetime literal
Submitted: 9 Nov 2020 22:49 Modified: 10 Nov 2020 4:40
Reporter: Mattias Jonsson (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S2 (Serious)
Version:8.0.22 OS:Linux
Assigned to: CPU Architecture:Any

[9 Nov 2020 22:49] Mattias Jonsson
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?
[10 Nov 2020 4:40] MySQL Verification Team
Hello Mattias,

Thank you for the report and feedback!

regards,
Umesh