Bug #118890 wrong supported ranges in the manual

• Submitted: 25 Aug 12:32
• Reporter: Kaiwang CHen (OCA)
• Status: Open
• Category: MySQL Server: Data Types
• Severity: S3 (Non-critical)
• Version: 8.0, 8.4, 9.2
• OS: Any
• CPU Architecture: Any

[25 Aug 12:32] Kaiwang CHen

Description:
The date part of the start of supported ranges in the manual is not same as in the implementation.

In the manual, https://dev.mysql.com/doc/refman/9.2/en/datetime.html

DATE: The supported range is '1000-01-01' to '9999-12-31'.
DATETIME: The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

However, the actual start is '0001-01-01'.

How to repeat:
Use date_add() to verify the supported range:

Documented supported range is '1000-01-01' to '9999-12-31'.

-- 0999-12-31
select date_add('1000-01-01', interval -1 day);
-- 1000-01-02
select date_add('1000-01-01', interval 1 day);

-- 9999-12-30
select date_add('9999-12-31', interval -1 day);
-- NULL
select date_add('9999-12-31', interval 1 day);

Actual supported range is '0001-01-01' to '9999-12-31'.

-- 0000-00-00
select date_add('0001-01-01', interval -1 day);
-- 0001-01-02
select date_add('0001-01-01', interval 1 day);

-- 0000-00-00 00:00:00 
select date_add('0001-01-01 00:00:00', interval -1 day);
-- 0001-01-02 00:00:00
select date_add('0001-01-01 00:00:00', interval 1 day);

-- 0000-00-00
select date_add('0000-01-01', interval -1 day);
-- 0000-00-00
select date_add('0000-01-01', interval 1 day);

Note that datediff() handles out-of-range dates.

-- -1
select datediff('0000-01-01', '0000-01-02');

Suggested fix:
Use '0001-01-01' as start of support ranges.