Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
13.1, 13.2
-
None
Description
--source include/have_partition.inc
|
--source include/have_innodb.inc
|
 |
SET time_zone='+00:00'; |
SET timestamp= UNIX_TIMESTAMP('2026-05-02 00:00:00'); |
--error 0,ER_PARSE_ERROR,ER_PARTITIONS_MUST_BE_DEFINED_ERROR,ER_FIELD_TYPE_NOT_ALLOWED_AS_PARTITION_FIELD
|
CREATE TABLE t1 (ts TIMESTAMP) ENGINE=InnoDB PARTITION BY RANGE COLUMNS (ts) INTERVAL 1 DAY (PARTITION p0 VALUES LESS THAN ('2026-04-30 12:00:00')); |
if ($mysql_errno) |
{
|
--skip Needs RANGE COLUMNS ... INTERVAL partitioning |
}
|
INSERT INTO t1 VALUES ('2026-04-30 11:00:00'); |
# The table is opened again, by a session with another time zone |
FLUSH TABLES;
|
SET time_zone='+05:00'; |
--let $n= query_get_value(SELECT COUNT(*) AS n FROM t1 WHERE ts = '2026-04-30 16:00:00', n, 1)
|
if ($n != 1) |
{
|
--die The row is not found: the TIMESTAMP partition bounds depend on the time zone of the session that opens the table |
}
|
DROP TABLE t1; |
Leads to:
|
CS 13.1.2 87e62ca17270e163a6d41b3fc8d6f4d440f1bb88 (Debug, Clang 22.1.8-20260622) Build 06/10/2026 |
mysqltest: At line 19: The row is not found: the TIMESTAMP partition bounds depend on the time zone of the session that opens the table
|
Partitioning changes the query result. Without partitioning, the query finds the row after the same time_zone change. In the +05:00 session, SELECT COUNT(*) FROM t1 gives 1 and the WHERE ts = '2026-04-30 16:00:00' query gives 0. EXPLAIN PARTITIONS reads p1 only, while the row is in p0. CHECK TABLE t1 in that session gives Found a misplaced row for p0. A row added by a +05:00 session after FLUSH TABLES is not found by a +00:00 session in the same way, and CHECK TABLE then reports p1. Optimized builds give the same result.
Without INTERVAL, PARTITION BY RANGE COLUMNS on a TIMESTAMP column is refused with ERROR 1659. From the code it seems that the INTERVAL checks in sql/sql_partition.cc allow a TIMESTAMP column, the bound is kept as local time text, and partition_info::fix_column_value_functions() turns it into a TIMESTAMP value in the time zone of the session that opens the table.
Attachments
Issue Links
- is caused by
-
MDEV-15621 Implement interval partitioning similar to Oracle
-
- Closed
-
