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

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 5.5, 10.0, 10.1, 10.2, 10.3, 10.4
    • Fix Version/s: 10.4
    • Component/s: Data types, Temporal Types
    • Labels:
      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

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

                Dates

                • Created:
                  Updated: