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

NO_ZERO_IN_DATE erroneously affects how CAST(AS DATE) warns about fractional digit truncation

    XMLWordPrintable

Details

    Description

      I set an empty sql_mode and run a query with CAST(AS DATE):

      SET sql_mode='';
      SELECT CAST(20061108.01 AS DATE);
      

      +---------------------------+
      | CAST(20061108.01 AS DATE) |
      +---------------------------+
      | 2006-11-08                |
      +---------------------------+
      1 row in set (0.00 sec)
      

      Notice, the value was returned without any warnings.

      Now I set sql_mode to NO_ZERO_IN_DATE and re-run the query:

      SET sql_mode=NO_ZERO_IN_DATE;
      SELECT CAST(20061108.01 AS DATE);
      

      +---------------------------+
      | CAST(20061108.01 AS DATE) |
      +---------------------------+
      | 2006-11-08                |
      +---------------------------+
      1 row in set, 1 warning (0.00 sec)
      

      Hmm, a warning was generated. Let's check it:

      SHOW WARNINGS;
      

      +---------+------+---------------------------------------------------+
      | Level   | Code | Message                                           |
      +---------+------+---------------------------------------------------+
      | Warning | 1292 | Truncated incorrect datetime value: '20061108.01' |
      +---------+------+---------------------------------------------------+
      

      Now let's remove fractional digits from the CAST argument:

      SET sql_mode=NO_ZERO_IN_DATE;
      SELECT CAST(20061108 AS DATE);
      

      +------------------------+
      | CAST(20061108 AS DATE) |
      +------------------------+
      | 2006-11-08             |
      +------------------------+
      1 row in set (0.00 sec)
      

      The warning has gone.

      So the warning happens:

      • only if there are some fractional digits, and
      • only if NO_ZERO_IN_DATE is set

      This looks wrong. Fractional digit truncation has no any relevance to NO_ZERO_IN_DATE.
      CAST(20061108.01 AS DATE) should return the same warnings no matter if NO_ZERO_IN_DATE is set.
      Possible ways:

      • Return the result silently, without any messages
      • Return the result with a NOTE rather than a WARNING

      Note, in case of an implicit conversion on INSERT, fractional digit truncation always generates a note, independently from NO_ZERO_IN_DATE flag:

      SET sql_mode='';
      CREATE OR REPLACE TABLE t1 (a DATE);
      INSERT INTO t1 VALUES (20061108.01);
      SHOW WARNINGS;
      

      +-------+------+----------------------------------------+
      | Level | Code | Message                                |
      +-------+------+----------------------------------------+
      | Note  | 1265 | Data truncated for column 'a' at row 1 |
      +-------+------+----------------------------------------+
      

      SET sql_mode=NO_ZERO_IN_DATE;
      CREATE OR REPLACE TABLE t1 (a DATE);
      INSERT INTO t1 VALUES (20061108.01);
      SHOW WARNINGS;
      

      +-------+------+----------------------------------------+
      | Level | Code | Message                                |
      +-------+------+----------------------------------------+
      | Note  | 1265 | Data truncated for column 'a' at row 1 |
      +-------+------+----------------------------------------+
      

      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:
                Resolved:

                Git Integration

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