Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-17335

COALESCE() respects NO_ZERO_DATE, NO_ZERO_IN_DATE but ignores unset ALLOW_INVALID_DATES

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.4(EOL)
    • Data types, Temporal Types
    • None

    Description

      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.

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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