[MDEV-17335] COALESCE() respects NO_ZERO_DATE, NO_ZERO_IN_DATE but ignores unset ALLOW_INVALID_DATES - Jira

Type: Bug
Status: Open (View Workflow)
Priority: Major
Resolution: Unresolved
Affects Version/s: 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
Fix Version/s: 10.4(EOL)
Component/s: Data types, Temporal Types
Labels:
None

SET sql_mode=ALLOW_INVALID_DATES;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (a DATE);

INSERT INTO t1 VALUES ('0000-00-00'),('2001-00-01'),('2001-02-30');

SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';

SELECT *, COALESCE(a) FROM t1;

Versions 10.0, 10.1, 10.2, 10.3, 10.4 return these results:

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

| a          | COALESCE(a) |

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

| 0000-00-00 | NULL        |

| 2001-00-01 | NULL        |

| 2001-02-30 | 2001-02-30  |

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

COALESCE() respects NO_ZERO_DATE and NO_ZERO_IN_DATE, but ignores unset ALLOW_INVALID_DATE.

(Note, table fields ignore sql_mode. But this is by design, it's hard to fix, and it's out of scope of this MDEV)

5.5 returns these results

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

| a          | COALESCE(a) |

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

| 0000-00-00 | 0000-00-00  |

| 2001-00-01 | 2001-00-01  |

| 2001-02-30 | 2001-02-30  |

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

So in 5.5 COALESCE() ignores sql_mode completely.

relates to

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

No workflow transitions have been executed yet.

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

MariaDB Server