[MDEV-17329] Inconsistency of NULL date value conversion to zero date in comparison context - Jira

Alexander Barkov created issue - 2018-09-30 12:02

Alexander Barkov made changes - 2018-09-30 12:02

Field	Original Value	New Value
Link		This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Alexander Barkov made changes - 2018-09-30 12:02

Link

This issue relates to ~~MDEV-17318~~ [ ~~MDEV-17318~~ ]

Alexander Barkov made changes - 2018-09-30 12:02

Link

This issue relates to ~~MDEV-16991~~ [ ~~MDEV-16991~~ ]

Alexander Barkov made changes - 2018-09-30 12:11

Description

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context, accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}
Looks fine so far.

This problem is reproducible in 10.4 only.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context, accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}
Looks fine so far.

Alexander Barkov made changes - 2018-09-30 13:22

Labels

regression

Alexander Barkov made changes - 2018-09-30 15:31

Description

This problem is reproducible in 10.4 only.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context, accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}
Looks fine so far.

This problem with the DATE data type is reproducible in 10.4 only.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context, accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 15:32

Description

This problem with the DATE data type is reproducible in 10.4 only.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context, accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context, accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 15:33

Description

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context, accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 15:34

Description

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 15:34

Description

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 15:38

Description

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
DATE('0000-00-00') < DATE'2001-01-01' AS c1,
TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 15:47

Description

This problem with the DATE data type is reproducible in 10.4 only. Earlier versions worked fine. However, the problem with DATETIME also presets in earlier version.

With NO_ZERO_DATE set, the function DATE() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Functions DATE() and TIMESTAMP() erroneously convert NULL to zero date.
- In 5.5 and 10.4 the problem happens with both functions DATE() and TIMESTAMP()
- 10.0, 10.1, 10.2, 10.3 the problem is repeatable for TIMESTAMP(), while DATE() works fine

h3. 10.4 returns a bad result for both DATE() and TIMESTAMP()
With NO_ZERO_DATE set, functions DATE() and TIMESTAMP() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 15:48

Description

Functions DATE() and TIMESTAMP() erroneously convert NULL to zero date.
- In 5.5 and 10.4 the problem happens with both functions DATE() and TIMESTAMP()
- 10.0, 10.1, 10.2, 10.3 the problem is repeatable for TIMESTAMP(), while DATE() works fine

h3. 10.4 returns a bad result for both DATE() and TIMESTAMP()
With NO_ZERO_DATE set, functions DATE() and TIMESTAMP() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Looks wrong. The second column should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Functions DATE() and TIMESTAMP() erroneously convert NULL to zero date.
- In 5.5 and 10.4 the problem happens with both functions DATE() and TIMESTAMP()
- 10.0, 10.1, 10.2, 10.3 the problem is repeatable for TIMESTAMP(), while DATE() works fine

h3. 10.4 returns a bad result for both DATE() and TIMESTAMP()
With NO_ZERO_DATE set, functions DATE() and TIMESTAMP() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Looks wrong. The second and the fourth columns should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 15:49

Description

Functions DATE() and TIMESTAMP() erroneously convert NULL to zero date.
- In 5.5 and 10.4 the problem happens with both functions DATE() and TIMESTAMP()
- 10.0, 10.1, 10.2, 10.3 the problem is repeatable for TIMESTAMP(), while DATE() works fine

h3. 10.4 returns a bad result for both DATE() and TIMESTAMP()
With NO_ZERO_DATE set, functions DATE() and TIMESTAMP() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Looks wrong. The second and the fourth columns should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Functions DATE() and TIMESTAMP() erroneously convert NULL to zero date.
- In 5.5 and 10.4 the problem happens with both functions DATE() and TIMESTAMP()
- 10.0, 10.1, 10.2, 10.3 the problem is repeatable for TIMESTAMP(), while DATE() works fine

h3. 10.4 returns a bad result for both DATE() and TIMESTAMP()
With NO_ZERO_DATE set, functions DATE() and TIMESTAMP() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Looks wrong. The second and the fourth columns should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for the function TIMESTAMP():
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 15:50

Description

Functions DATE() and TIMESTAMP() erroneously convert NULL to zero date.
- In 5.5 and 10.4 the problem happens with both functions DATE() and TIMESTAMP()
- 10.0, 10.1, 10.2, 10.3 the problem is repeatable for TIMESTAMP(), while DATE() works fine

h3. 10.4 returns a bad result for both DATE() and TIMESTAMP()
With NO_ZERO_DATE set, functions DATE() and TIMESTAMP() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Looks wrong. The second and the fourth columns should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable at least since 10.0, but only for the function TIMESTAMP():
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Functions DATE() and TIMESTAMP() erroneously convert NULL to zero date.
- In 5.5 and 10.4 the problem happens with both functions DATE() and TIMESTAMP()
- 10.0, 10.1, 10.2, 10.3 the problem is repeatable for TIMESTAMP(), while DATE() works fine

h3. 10.4 returns a bad result for both DATE() and TIMESTAMP()
With NO_ZERO_DATE set, functions DATE() and TIMESTAMP() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Looks wrong. The second and the fourth columns should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 16:33

Summary

NULL date value is erroneously converted to zero date in comparison context

Inconsistency of NULL date value conversion to zero date in comparison context

Alexander Barkov made changes - 2018-09-30 16:46

Description

Functions DATE() and TIMESTAMP() erroneously convert NULL to zero date.
- In 5.5 and 10.4 the problem happens with both functions DATE() and TIMESTAMP()
- 10.0, 10.1, 10.2, 10.3 the problem is repeatable for TIMESTAMP(), while DATE() works fine

h3. 10.4 returns a bad result for both DATE() and TIMESTAMP()
With NO_ZERO_DATE set, functions DATE() and TIMESTAMP() returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Looks wrong. The second and the fourth columns should also return NULL.

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 2001-00-01
- 2001-01-00
- 2001-02-30

But converting NULL to non-NULL looks wrong.

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h2. The same problem is repeatable in version starting from 10.0 and up to 10.3, but only for DATETIME:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
The value for c2 is wrong. It should be NULL.
Notice, DATE works fine in these versions.

h2. The same problem is repeatable in 5.5, for both DATE and DATETIME
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, for both DATE and DATETIME convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 16:47

Description

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, for both DATE and DATETIME convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, for both DATE and DATETIME convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 16:55

Description

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, for both DATE and DATETIME convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  '0000-00-00' < DATE'2001-01-01' AS c1,
  '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+----+----+
| c1 | c2 |
+----+----+
| 1 | 1 |
+----+----+
{noformat}

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, for both DATE and DATETIME convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-09-30 16:57

Description

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  '0000-00-00' < DATE'2001-01-01' AS c1,
  '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+----+----+
| c1 | c2 |
+----+----+
| 1 | 1 |
+----+----+
{noformat}

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, for both DATE and DATETIME convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  '0000-00-00' < DATE'2001-01-01' AS c1,
  '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+----+----+
| c1 | c2 |
+----+----+
| 1 | 1 |
+----+----+
{noformat}
String literals and functions DATE() and TIMESTAMP() work the same way.

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, for both DATE and DATETIME convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov made changes - 2018-10-01 10:16

Description

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  '0000-00-00' < DATE'2001-01-01' AS c1,
  '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+----+----+
| c1 | c2 |
+----+----+
| 1 | 1 |
+----+----+
{noformat}
String literals and functions DATE() and TIMESTAMP() work the same way.

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, for both DATE and DATETIME convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  '0000-00-00' < DATE'2001-01-01' AS c1,
  '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+----+----+
| c1 | c2 |
+----+----+
| 1 | 1 |
+----+----+
{noformat}
String literals and functions DATE() and TIMESTAMP() work the same way.

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, both DATE() and DATETIME() convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

Alexander Barkov added a comment - 2018-10-01 10:27 - edited

A related problem is reported in MDEV-17335.

Alexander Barkov added a comment - 2018-10-01 10:27 - edited A related problem is reported in MDEV-17335 .

Alexander Barkov made changes - 2018-10-01 15:42

Link

This issue blocks ~~MDEV-16991~~ [ ~~MDEV-16991~~ ]

Alexander Barkov made changes - 2018-10-01 15:44

Link

This issue relates to MDEV-17335 [ MDEV-17335 ]

Alexander Barkov made changes - 2018-10-05 01:38

Description

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  '0000-00-00' < DATE'2001-01-01' AS c1,
  '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+----+----+
| c1 | c2 |
+----+----+
| 1 | 1 |
+----+----+
{noformat}
String literals and functions DATE() and TIMESTAMP() work the same way.

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, both DATE() and DATETIME() convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  '0000-00-00' < DATE'2001-01-01' AS c1,
  '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+----+----+
| c1 | c2 |
+----+----+
| 1 | 1 |
+----+----+
{noformat}
String literals and functions DATE() and TIMESTAMP() work the same way.

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, both DATE() and DATETIME() convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

h3. Conclusions
During a Slack discussion between Sergei and Bar, the following desicion was made:

h4. Functions:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;```
{code}
should return NULL in all columns. A result of a function, DATE() and DATETIME() in this example, should not depend on its context. So it should work as follows:
- Functions values are calculated normally, NULL is returned.
- NULLs is passed into comparison operators.
- Comparison operators return NULL.

h4. Literal:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
'0000-00-00' < DATE'2001-01-01' AS cmpd,
'0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
will still return non-NULL values. In this query we convert string literals '0000-00-00' to DATE and DATETIME for comparison.

h4. Tests
Tests, in addition to functions DATE() and TIMESTAMP(), should also conver:
- CAST(AS DATE)
- CAST(AS DATETIME(N)).

Alexander Barkov made changes - 2018-10-05 01:39

Description

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  '0000-00-00' < DATE'2001-01-01' AS c1,
  '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+----+----+
| c1 | c2 |
+----+----+
| 1 | 1 |
+----+----+
{noformat}
String literals and functions DATE() and TIMESTAMP() work the same way.

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, both DATE() and DATETIME() convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

h3. Conclusions
During a Slack discussion between Sergei and Bar, the following desicion was made:

h4. Functions:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;```
{code}
should return NULL in all columns. A result of a function, DATE() and DATETIME() in this example, should not depend on its context. So it should work as follows:
- Functions values are calculated normally, NULL is returned.
- NULLs is passed into comparison operators.
- Comparison operators return NULL.

h4. Literal:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
'0000-00-00' < DATE'2001-01-01' AS cmpd,
'0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
will still return non-NULL values. In this query we convert string literals '0000-00-00' to DATE and DATETIME for comparison.

h4. Tests
Tests, in addition to functions DATE() and TIMESTAMP(), should also conver:
- CAST(AS DATE)
- CAST(AS DATETIME(N)).

MariaDB performs loose date validation in comparison context (even with NO_ZERO_DATE, NO_ZERO_IN_DATE and without INVALID_DATES set in the current @@sql_mode), accepting wrong dates such as:
- 0000-00-00
- 2001-00-01
- 2001-01-00
- 2001-02-30

Functions DATE() and TIMESTAMP() convert invalid dates to zero date (instead of NULL) in comparison context.
However, this conversion works differently in different versions.

- In 5.5 and 10.4 both functions DATE() and TIMESTAMP() convert invalid dates to NULL, so comparison operators return non-NULL values
- 10.0, 10.1, 10.2, 10.3, DATE() does not convert invalid dates to zero dates, while the function TIMESTAMP() does.

It's not clear which way is correct.

Details:

h3. In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
{noformat}
+------+------+------+-------+
| d | cmpd | dt | cmpdd |
+------+------+------+-------+
| NULL | 1 | NULL | 1 |
+------+------+------+-------+
{noformat}

Just for information: with NO_ZERO_DATE set, zero date literals are rejected by the parser:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}

At the same time, comparison between an invalid-date-in-string converts invalid dates to zero dates:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  '0000-00-00' < DATE'2001-01-01' AS c1,
  '0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+----+----+
| c1 | c2 |
+----+----+
| 1 | 1 |
+----+----+
{noformat}
String literals and functions DATE() and TIMESTAMP() work the same way.

h3. In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') < DATE'2001-01-01' AS c1,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2;
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| NULL | 1 |
+------+------+
{noformat}
Notice, DATE() converted an invalid date to NULL, TIMESTAMP() converted an invalid datetime value to zero.

h3. In 5.5, both DATE() and DATETIME() convert invalid dates to zero dates for comparison
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
   DATE('0000-00-00') < DATE'2001-01-01' AS c1,
   TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS c2
{code}
{noformat}
+------+------+
| c1 | c2 |
+------+------+
| 1 | 1 |
+------+------+
{noformat}

h3. Conclusions
During a Slack discussion between [~serg] and [~bar], the following decision was made:

h4. Functions:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
  DATE('0000-00-00') AS d,
  DATE('0000-00-00') < DATE'2001-01-01' AS cmpd,
  TIMESTAMP('0000-00-00 00:00:00') AS dt,
  TIMESTAMP('0000-00-00 00:00:00') < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;```
{code}
should return NULL in all columns. A result of a function, DATE() and DATETIME() in this example, should not depend on its context. So it should work as follows:
- Functions values are calculated normally, NULL is returned.
- NULLs is passed into comparison operators.
- Comparison operators return NULL.

h4. Literal:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
'0000-00-00' < DATE'2001-01-01' AS cmpd,
'0000-00-00 00:00:00' < TIMESTAMP'2001-01-01 00:00:00' AS cmpdd;
{code}
will still return non-NULL values. In this query we convert string literals '0000-00-00' to DATE and DATETIME for comparison.

h4. Tests
Tests, in addition to functions DATE() and TIMESTAMP(), should also conver:
- CAST(AS DATE)
- CAST(AS DATETIME(N)).

Alexander Barkov added a comment - 2018-10-05 01:44

Tests for this bug should include the following:

--echo #

--echo # MDEV-17351 MICROSECOND(XXX(int_number_out_of_range)) erroneously returns 999999

--echo #

SET sql_mode='';

CREATE TABLE t1 AS SELECT

  GREATEST(8395959, TIME'00:00:00') AS c1,

  ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)  AS c2,

  TIMESTAMP('2001-01-01 00:00:00', 8395959) AS c3;

SHOW CREATE TABLE t1;

SELECT * FROM t1;

DROP TABLE t1;

SET sql_mode='';

SELECT

  MICROSECOND(GREATEST(8395959, TIME'00:00:00')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959)) AS c3;

SELECT

  MICROSECOND(GREATEST(8395959.0, TIME'00:00:00')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959.0)) AS c3;

SELECT

  MICROSECOND(GREATEST(8395959.00, TIME'00:00:00')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.00)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959.00)) AS c3;

SELECT

  MICROSECOND(GREATEST(8395959.000000, TIME'00:00:00')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.000000)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959.000000)) AS c3;

SELECT

  MICROSECOND(GREATEST(8395959.0000000, TIME'00:00:00')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00', 8395959.0000000)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00', 8395959.0000000)) AS c3;

SELECT

  MICROSECOND(GREATEST(8395959, TIME'00:00:00.0')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.0', 8395959)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.0', 8395959)) AS c3;

SELECT

  MICROSECOND(GREATEST(8395959, TIME'00:00:00.00')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.00', 8395959)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.00', 8395959)) AS c3;

SELECT

  MICROSECOND(GREATEST(8395959, TIME'00:00:00.00')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.00', 8395959)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.00', 8395959)) AS c3;

SELECT

  MICROSECOND(GREATEST(8395959, TIME'00:00:00.0000000')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.0000000', 8395959)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.0000000', 8395959)) AS c3;

SELECT

  MICROSECOND(GREATEST(8395959, TIME'00:00:00.0000000')) AS c1,

  MICROSECOND(ADDTIME(TIMESTAMP'2001-01-01 00:00:00.0000000', 8395959)) AS c2,

  MICROSECOND(TIMESTAMP('2001-01-01 00:00:00.0000000', 8395959)) AS c3;

SELECT TIME(8395959), MICROSECOND(8395959);

SELECT TIME(8395959.0), MICROSECOND(8395959.0);

SELECT TIME(8395959.00), MICROSECOND(8395959.00);

SELECT TIME(8395959.000), MICROSECOND(8395959.000);

SELECT TIME(8395959.0000), MICROSECOND(8395959.0000);

SELECT TIME(8395959.00000), MICROSECOND(8395959.00000);

SELECT TIME(8395959.000000), MICROSECOND(8395959.000000);

SELECT TIME(8395959.0000000), MICROSECOND(8395959.0000000);

SELECT TIME('839:59:59'), MICROSECOND('839:59:59');

SELECT TIME('839:59:59.0'), MICROSECOND('839:59:59.0');

SELECT TIME('839:59:59.00'), MICROSECOND('839:59:59.00');

SELECT TIME('839:59:59.000'), MICROSECOND('839:59:59.000');

SELECT TIME('839:59:59.0000'), MICROSECOND('839:59:59.0000');

SELECT TIME('839:59:59.00000'), MICROSECOND('839:59:59.00000');

SELECT TIME('839:59:59.000000'), MICROSECOND('839:59:59.000000');

SELECT TIME('839:59:59.0000000'), MICROSECOND('839:59:59.0000000');

SELECT

  TIME(CAST(8395959 AS SIGNED)) AS c1,

  MICROSECOND(CAST(8395959 AS SIGNED)) AS c2;

SELECT

  TIME(CAST(8395959 AS DECIMAL(30,0))) AS c1,

  MICROSECOND(CAST(8395959 AS DECIMAL(30,0))) AS c2;

SELECT

  TIME(CAST(8395959 AS DECIMAL(30,1))) AS c1,

  MICROSECOND(CAST(8395959 AS DECIMAL(30,1))) AS c2;

SELECT

  TIME(CAST(8395959 AS DECIMAL(30,2))) AS c1,

  MICROSECOND(CAST(8395959 AS DECIMAL(30,2))) AS c2;

SELECT

  TIME(CAST(8395959 AS DECIMAL(30,3))) AS c1,

  MICROSECOND(CAST(8395959 AS DECIMAL(30,3))) AS c2;

SELECT

  TIME(CAST(8395959 AS DECIMAL(30,4))) AS c1,

  MICROSECOND(CAST(8395959 AS DECIMAL(30,4))) AS c2;

SELECT

  TIME(CAST(8395959 AS DECIMAL(30,5))) AS c1,

  MICROSECOND(CAST(8395959 AS DECIMAL(30,5))) AS c2;

SELECT

  TIME(CAST(8395959 AS DECIMAL(30,6))) AS c1,

  MICROSECOND(CAST(8395959 AS DECIMAL(30,6))) AS c2;

SELECT

  TIME(CAST(8395959 AS DECIMAL(30,7))) AS c1,

  MICROSECOND(CAST(8395959 AS DECIMAL(30,7))) AS c2;

SELECT

  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c1,

  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c2;

SELECT

  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,0))) AS c1,

  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,0))) AS c2;

SELECT

  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,1))) AS c1,

  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,1))) AS c2;

SELECT

  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,2))) AS c1,

  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,2))) AS c2;

SELECT

  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,3))) AS c1,

  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,3))) AS c2;

SELECT

  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,4))) AS c1,

  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,4))) AS c2;

SELECT

  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,5))) AS c1,

  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,5))) AS c2;

SELECT

  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,6))) AS c1,

  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,6))) AS c2;

SELECT

  TIME(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,7))) AS c1,

  MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL(30,7))) AS c2;

Alexander Barkov added a comment - 2018-10-05 01:44 Tests for this bug should include the following: --echo # --echo # MDEV-17351 MICROSECOND(XXX(int_number_out_of_range)) erroneously returns 999999 --echo # SET sql_mode= '' ; CREATE TABLE t1 AS SELECT GREATEST(8395959, TIME '00:00:00' ) AS c1, ADDTIME( TIMESTAMP '2001-01-01 00:00:00' , 8395959) AS c2, TIMESTAMP ( '2001-01-01 00:00:00' , 8395959) AS c3; SHOW CREATE TABLE t1; SELECT * FROM t1; DROP TABLE t1; SET sql_mode= '' ; SELECT MICROSECOND(GREATEST(8395959, TIME '00:00:00' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00' , 8395959)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00' , 8395959)) AS c3; SELECT MICROSECOND(GREATEST(8395959.0, TIME '00:00:00' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00' , 8395959.0)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00' , 8395959.0)) AS c3; SELECT MICROSECOND(GREATEST(8395959.00, TIME '00:00:00' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00' , 8395959.00)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00' , 8395959.00)) AS c3; SELECT MICROSECOND(GREATEST(8395959.000000, TIME '00:00:00' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00' , 8395959.000000)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00' , 8395959.000000)) AS c3; SELECT MICROSECOND(GREATEST(8395959.0000000, TIME '00:00:00' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00' , 8395959.0000000)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00' , 8395959.0000000)) AS c3; SELECT MICROSECOND(GREATEST(8395959, TIME '00:00:00.0' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00.0' , 8395959)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00.0' , 8395959)) AS c3; SELECT MICROSECOND(GREATEST(8395959, TIME '00:00:00.00' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00.00' , 8395959)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00.00' , 8395959)) AS c3; SELECT MICROSECOND(GREATEST(8395959, TIME '00:00:00.00' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00.00' , 8395959)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00.00' , 8395959)) AS c3; SELECT MICROSECOND(GREATEST(8395959, TIME '00:00:00.0000000' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00.0000000' , 8395959)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00.0000000' , 8395959)) AS c3; SELECT MICROSECOND(GREATEST(8395959, TIME '00:00:00.0000000' )) AS c1, MICROSECOND(ADDTIME( TIMESTAMP '2001-01-01 00:00:00.0000000' , 8395959)) AS c2, MICROSECOND( TIMESTAMP ( '2001-01-01 00:00:00.0000000' , 8395959)) AS c3; SELECT TIME (8395959), MICROSECOND(8395959); SELECT TIME (8395959.0), MICROSECOND(8395959.0); SELECT TIME (8395959.00), MICROSECOND(8395959.00); SELECT TIME (8395959.000), MICROSECOND(8395959.000); SELECT TIME (8395959.0000), MICROSECOND(8395959.0000); SELECT TIME (8395959.00000), MICROSECOND(8395959.00000); SELECT TIME (8395959.000000), MICROSECOND(8395959.000000); SELECT TIME (8395959.0000000), MICROSECOND(8395959.0000000); SELECT TIME ( '839:59:59' ), MICROSECOND( '839:59:59' ); SELECT TIME ( '839:59:59.0' ), MICROSECOND( '839:59:59.0' ); SELECT TIME ( '839:59:59.00' ), MICROSECOND( '839:59:59.00' ); SELECT TIME ( '839:59:59.000' ), MICROSECOND( '839:59:59.000' ); SELECT TIME ( '839:59:59.0000' ), MICROSECOND( '839:59:59.0000' ); SELECT TIME ( '839:59:59.00000' ), MICROSECOND( '839:59:59.00000' ); SELECT TIME ( '839:59:59.000000' ), MICROSECOND( '839:59:59.000000' ); SELECT TIME ( '839:59:59.0000000' ), MICROSECOND( '839:59:59.0000000' ); SELECT TIME ( CAST (8395959 AS SIGNED)) AS c1, MICROSECOND( CAST (8395959 AS SIGNED)) AS c2; SELECT TIME ( CAST (8395959 AS DECIMAL (30,0))) AS c1, MICROSECOND( CAST (8395959 AS DECIMAL (30,0))) AS c2; SELECT TIME ( CAST (8395959 AS DECIMAL (30,1))) AS c1, MICROSECOND( CAST (8395959 AS DECIMAL (30,1))) AS c2; SELECT TIME ( CAST (8395959 AS DECIMAL (30,2))) AS c1, MICROSECOND( CAST (8395959 AS DECIMAL (30,2))) AS c2; SELECT TIME ( CAST (8395959 AS DECIMAL (30,3))) AS c1, MICROSECOND( CAST (8395959 AS DECIMAL (30,3))) AS c2; SELECT TIME ( CAST (8395959 AS DECIMAL (30,4))) AS c1, MICROSECOND( CAST (8395959 AS DECIMAL (30,4))) AS c2; SELECT TIME ( CAST (8395959 AS DECIMAL (30,5))) AS c1, MICROSECOND( CAST (8395959 AS DECIMAL (30,5))) AS c2; SELECT TIME ( CAST (8395959 AS DECIMAL (30,6))) AS c1, MICROSECOND( CAST (8395959 AS DECIMAL (30,6))) AS c2; SELECT TIME ( CAST (8395959 AS DECIMAL (30,7))) AS c1, MICROSECOND( CAST (8395959 AS DECIMAL (30,7))) AS c2; SELECT TIME (COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c1, MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS SIGNED)) AS c2; SELECT TIME (COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,0))) AS c1, MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,0))) AS c2; SELECT TIME (COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,1))) AS c1, MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,1))) AS c2; SELECT TIME (COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,2))) AS c1, MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,2))) AS c2; SELECT TIME (COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,3))) AS c1, MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,3))) AS c2; SELECT TIME (COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,4))) AS c1, MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,4))) AS c2; SELECT TIME (COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,5))) AS c1, MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,5))) AS c2; SELECT TIME (COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,6))) AS c1, MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,6))) AS c2; SELECT TIME (COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,7))) AS c1, MICROSECOND(COLUMN_GET(COLUMN_CREATE(0, 8395959), 0 AS DECIMAL (30,7))) AS c2;

Alexander Barkov made changes - 2018-11-26 11:22

Link

This issue blocks ~~MDEV-16991~~ [ ~~MDEV-16991~~ ]

Julien Fritsch made changes - 2019-11-18 11:41

Epic Link

MDEV-21071 [ 80504 ]

Julien Fritsch made changes - 2019-11-18 11:58

Link

This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Sergei Golubchik made changes - 2021-12-06 21:33

Workflow

MariaDB v3 [ 89825 ]

MariaDB v4 [ 140950 ]

MariaDB Server

Inconsistency of NULL date value conversion to zero date in comparison context

Details

Description

In 10.4 both DATE() and TIMESTAMP() convert invalid dates to zero dates (rather than NULL) for comparison

In version starting from 10.0 and up to 10.3 only TIMESTAMP() converts invalid dates to zero dates for comparison

In 5.5, both DATE() and DATETIME() convert invalid dates to zero dates for comparison

Conclusions

Functions:

Literal:

Tests

Attachments

Issue Links

Activity

People

Dates

Git Integration