Bug #34281 TIME and YEAR data types don't follow NO_ZERO_DATE and NO_ZERO_IN_DATE

• Submitted: 4 Feb 2008 14:28
• Modified: 4 Feb 2008 19:34
• Reporter: Alexander Nozdrin
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.0, 5.1, 6.0 BK
• OS: Any
• CPU Architecture: Any

[4 Feb 2008 14:28] Alexander Nozdrin

Description:
TIME and YEAR column types do not follow common rules for NO_ZERO_DATE/NO_ZERO_IN_DATE like other date types.

TIME may not obey these flags because 'time' is not a 'date'.
But 'year' seems to be a 'date' in any case.

How to repeat:
SET sql_mode = 'no_zero_date,no_zero_in_date,strict_all_tables';

CREATE TABLE t1(c1 TIME DEFAULT 0);
  -- Ok.
CREATE TABLE t2(c1 YEAR NOT NULL DEFAULT 0);
  -- Ok

INSERT INTO t1 VALUES ();
INSERT INTO t1 VALUES (0);
INSERT INTO t1 VALUES ('00:00:00');

INSERT INTO t2 VALUES ();
INSERT INTO t2 VALUES (0);
INSERT INTO t2 VALUES ('00:00:00');

SELECT * FROM t1;
+----------+
| c1       |
+----------+
| 00:00:00 |
| 00:00:00 |
| 00:00:00 |
+----------+
3 rows in set (0.00 sec)

SELECT * FROM t2;
+------+
| c1   |
+------+
| 0000 |
| 0000 |
| 0000 |
+------+
3 rows in set (0.00 sec)

[4 Feb 2008 19:34] Sveta Smirnova

Thank you for the report.

Verified with next change:

INSERT INTO t2 VALUES ('0000');

[5 Feb 2008 13:05] Sergei Golubchik

right, TIME should always allow zeros, 00:00:00 or, e.g., 11:00:22 are valid TIME values