[MDEV-19311] Change NO_ZERO_IN_DATE to disallow date '0000-01-01' Created: 2019-04-23  Updated: 2021-01-04  Resolved: 2021-01-04

Status: Closed
Project: MariaDB Server
Component/s: Data types
Fix Version/s: N/A

Type: Task Priority: Major
Reporter: Alexander Barkov Assignee: Oleksandr Byelkin
Resolution: Won't Fix Votes: 0
Labels: beginner-friendly

Epic Link: Data type cleanups

 Description   

According to the SQL standard, valid date values are described as follows:

Table 9 — Valid values for datetime fields

  • YEAR 0001 to 9999
  • MONTH 01 to 12
  • DAY Within the range 1 to 31, but further constrained by the value of MONTHand YEAR fields, according to the rules for well-formed dates in the Gregorian calendar.

MariaDB allows dates with zero YYYY, MM, DD components with an empty sql_mode, and has two flags to provide a more SQL standard behavior:

  • NO_ZERO_DATE disallows dates '0000-00-00' and datetimes '0000-00-00 00:00:00' (when all components are zeros)
  • NO_ZERO_IN_DATE disallows dates 'YYYY-00-00' (i.e. non-zero YYYY, but zero MM-DD)
  • NO_ZERO_IN_DATE disallows dates 'YYYY-MM-00' (i.e. non-zero YYYY and non-zero MM, but zero DD)
  • NO_ZERO_IN_DATE disallows dates 'YYYY-00-DD' (i.e. non-zero YYYY and non-zero DD, but zero MM)

This combination provides best SQL standard compatibility:

SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE';

However, dates with zero year are still possible:

SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
 
CREATE OR REPLACE TABLE t1 (a DATE);
 
INSERT INTO t1 VALUES ('0000-01-01');
 
SELECT * FROM t1;


+------------+
 
| a          |
 
+------------+
 
| 0000-01-01 |
 
+------------+


SET sql_mode='STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
 
CREATE OR REPLACE TABLE t1 (a DATETIME);
 
INSERT INTO t1 VALUES ('0000-01-01 00:00:00');
 
SELECT * FROM t1;


+---------------------+
 
| a                   |
 
+---------------------+
 
| 0000-01-01 00:00:00 |
 
+---------------------+

In the above example, '0000-01-01' is not standard, because it has zero YYYY.
But there is no a way to disallow such values, as neither NO_ZERO_DATE nor NO_ZERO_IN_DATE disallow it.

Under terms of this task we'll change the meaning of NO_ZERO_IN_DATE to disallow all DATE and DATETIME values that have zero in any component of YYYY-MM-DD.

The above two scripts will return errors.

 Comments   
Comment by Ralf Gebhardt [ 2020-02-06 ]

Moved to 10.6 after discussing with bar

Comment by Michael Widenius [ 2021-01-04 ]

The question is if we want to implement years according the Gregorian or Astronomical calender. The Astronomical calender has year zero and also supports negative years.
If anyone ever would like to store years before Christ in MariaDB, we should support both year 0 and negative years. I think this is a logical extension to the SQL standard and I would rather support this than remove year 0 support.

Comment by Sergei Golubchik [ 2021-01-04 ]

We cannot "extend" YEAR to use Astronomical year numbering, because Astronomical year numbering is incompatible with YEAR. The former uses Julian calendar for years before 1582, while MariaDB uses Gregorian calendar, this is called proleptic Gregorian calendar (see also What Calendar Is Used By MySQL?).

But we can extend YEAR to use ISO 8601 calendar which uses proleptic Gregorian from 1 to 1582 and has a year 0 too. It does not automatically allows negative years, it "permits the expansion of the year representation but only by prior agreement between the sender and the receiver".

According to SQL standard date values are "constrained by the natural rules for dates using the Gregorian calendar" (SQL:2016, Part II, 4.6.2 Datetimes). Which means the year cannot be below 1582. We don't want to do that, do we?

Both allowing and disallowing year 0 is a rather arbitrary decision in a artificial calendar that never actually existed. But at least there's an ISO standard that has year 0. Let's follow that.

Generated at Thu Feb 08 08:50:41 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.