Bug #101530 Partition pruning fails with explicit time_zone offset in datetime literal

• Submitted: 9 Nov 2020 22:49
• Modified: 27 Apr 23:22
• Reporter: Mattias Jonsson (OCA)
• Status: Closed
• Category: MySQL Server: Partitions
• Severity: S2 (Serious)
• Version: 8.0.22
• OS: Linux
• 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

[27 Apr 23:22] Jon Stephens

Documented fix as follows in the MySQL 8.0.26 changelog:

    When a table was partitioned by timestamp and a timestamp
    literal with time zone was used in the WHERE clause of a SELECT
    statement, it was possible for a partition to be omitted from
    the result set.

    When a time zone offset is specified in a timestamp literal, it
    is expected to be converted to a timestamp without a time zone
    offset, and then compared against a timestamp column, but this
    was not done properly in all cases, with the result that a
    partition could be pruned while selecting the partitions to be
    scanned for the query.

    We fix this by making sure that a timestamp with a time zone
    offset is always converted as described before comparing with
    values from the column.

Closed.