INTERVAL 1 MONTH partition bounds drift to the 28th when the first bound is at the end of a month

Likely cause: commit e6fc36cfe3d4831c1ffff80b8401b9d841fc89ac (MDEV-15621), in partition_info::range_interval_set_count() and check_range_interval_constants(). INTERVAL partitioning came with that commit.

--source include/have_partition.inc

SET timestamp= UNIX_TIMESTAMP('2026-06-15 00:00:00');

--disable_abort_on_error

CREATE TABLE t1 (c DATE) PARTITION BY RANGE COLUMNS (c) INTERVAL 1 MONTH (PARTITION p0 VALUES LESS THAN ('2026-01-31'));

--enable_abort_on_error

if ($mysql_errno == 1064)

{

  --skip Needs RANGE COLUMNS ... INTERVAL partitioning

}

if (!$mysql_errno)

{

  INSERT INTO t1 VALUES ('2026-06-01');

  SELECT PARTITION_NAME, PARTITION_DESCRIPTION FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' ORDER BY PARTITION_ORDINAL_POSITION;

  --let $b= query_get_value(SELECT PARTITION_DESCRIPTION AS b FROM information_schema.PARTITIONS WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1' AND PARTITION_ORDINAL_POSITION=3, b, 1)

  if ($b != "'2026-03-31'")

  {

    --die The bound of the third partition is not the first bound plus 2 months: the INTERVAL 1 MONTH bounds drift to the 28th

  }

  DROP TABLE t1;

}

Leads to:

mysqltest: At line 18: The bound of the third partition is not the first bound plus 2 months: the INTERVAL 1 MONTH bounds drift to the 28th

With the first bound '2026-01-31' and the current date 2026-06-15, one INSERT adds the bounds '2026-02-28', '2026-03-28', '2026-04-28', '2026-05-28' and '2026-06-28'. DATE_ADD('2026-01-31', INTERVAL n MONTH) gives '2026-02-28', '2026-03-31', '2026-04-30', '2026-05-31' and '2026-06-30', so the rows of March 28 to 30 go to the next partition. INTERVAL 1 YEAR from '2024-02-29' drifts to February 28 in the same way. ADD PARTITION, REORGANIZE PARTITION and DROP PARTITION can also leave the last bound on day 29 to 31, with the same drift. Debug builds give the same result.

MDEV-15621 quotes Oracle: "the lower boundary for the July 2007 interval is July 1, 2007, regardless of whether the June 2007 partition was created". In Oracle 23ai, the same CREATE TABLE with NUMTOYMINTERVAL(1,'MONTH') gives ORA-14767 "Cannot specify this interval with existing high bounds".

From the code it seems that partition_info::range_interval_set_count() and check_range_interval_constants() (sql/sql_partition_auto_interval.cc) add the interval to the previous bound in a loop, instead of adding n intervals to the first bound.