[MDEV-8336] The meaning of NO_ZERO_DATE is not clear for DATETIME - Jira

The documentation days:

The NO_ZERO_DATE mode affects whether the server permits '0000-00-00' as a valid date. Its effect also depends on whether strict SQL mode is enabled.

But it does not say if DATETIME values with zero YYYYMMDD and non-zero hhmmss.uuuuuu parts (e.g. '0000-00 10:20:30') is permitted.

Different parts of the code tread it differently for DATETIME.

Example1: '0000-00-00 10:20:30' is not permitted

SET sql_mode='NO_ZERO_DATE';

SELECT TIMESTAMP'0000-00-00 10:20:30', TIMESTAMP'0000-00-01 10:20:30';

returns

+--------------------------------+--------------------------------+

| TIMESTAMP'0000-00-00 10:20:30' | TIMESTAMP'0000-00-01 10:20:30' |

+--------------------------------+--------------------------------+

| NULL                           | 0000-00-01 10:20:30            |

+--------------------------------+--------------------------------+

1 row in set, 1 warning (0.00 sec)

That is only the YYYYMMDD part is used to decide if the value is "ZERO DATE".

Example2: '0000-00-00 10:20:30' behaves differently in the same script:

SET sql_mode='NO_ZERO_DATE';

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('0000-00-00 10:20:30');

SELECT a, LEAST(a,'2001-01-01 10:20:30') FROM t1;

returns:

+---------------------+--------------------------------+

| a                   | LEAST(a,'2001-01-01 10:20:30') |

+---------------------+--------------------------------+

| 0000-00-00 10:20:30 | NULL                           |

+---------------------+--------------------------------+

1 row in set, 1 warning (0.00 sec)

That is INSERT and "SELECT a FROM t1" treated '0000-00-00 10:20:30' as a permitted value, while LEAST() treated it as a non-permitted value.

It should be fixed to treat in the same way in all context.

Example3:

SET sql_mode='NO_ZERO_DATE';

SELECT STR_TO_DATE('0000-00-00 10:20:30','%Y-%m-%d %h:%i:%s');

Example4:

SET sql_mode='NO_ZERO_DATE';

SELECT TIMESTAMP'0000-00-00 10:20:30';

Exapmple 5:

SET old_mode=zero_date_time_cast;

SET SQL_MODE='NO_ZERO_DATE';

SELECT CAST(TIME'10:20:30' AS DATETIME);

Example 6:

SET old_mode=zero_date_time_cast;

SET SQL_MODE='NO_ZERO_DATE';

SELECT CAST((CAST('10:20:30' AS TIME)) AS DATETIME);

Example 7:

SET old_mode=zero_date_time_cast;

SET SQL_MODE='NO_ZERO_DATE';

SELECT CAST((CAST(TIMESTAMP'0000-00-00 10:20:30' AS TIME)) AS DATETIME);

There are no comments yet on this issue.

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server