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

Inconsistent behavior of conversion to DATETIME for comparison

    XMLWordPrintable

    Details

      Description

      Conversion to DATETIME for comparison from other data types works differently, both in terms of the result and in terms of warnings generated.

      Summary:

      • Implicit conversion never returns warnings
      • Explicit conversion always returns a warning
      • Implicit conversion always returns zero datetime
      • Explicit conversion mostly returns zero datetime, but TIME-to-DATETIME returns NULL

      Implicit conversion from TIME to DATETIME - zero datetime without warnings

      SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
      SET old_mode=ZERO_DATE_TIME_CAST;
      SELECT TIMESTAMP'2001-01-01 00:00:00' > TIME'00:00:00';
      

      +-------------------------------------------------+
      | TIMESTAMP'2001-01-01 00:00:00' > TIME'00:00:00' |
      +-------------------------------------------------+
      |                                               1 |
      +-------------------------------------------------+
      1 row in set (0.00 sec)
      

      Implicit conversion from VARCHAR to DATETIME - zero datetime without warnings

      SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
      SET old_mode=ZERO_DATE_TIME_CAST;
      SELECT TIMESTAMP'2001-01-01 00:00:00' > '00:00:00';
      

      +---------------------------------------------+
      | TIMESTAMP'2001-01-01 00:00:00' > '00:00:00' |
      +---------------------------------------------+
      |                                           1 |
      +---------------------------------------------+
      1 row in set (0.00 sec)
      

      Implicit conversion from INT to DATETIME - zero datetime without warnings

      SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
      SELECT TIMESTAMP'2001-01-01 00:00:00' > 0;
      

      +------------------------------------+
      | TIMESTAMP'2001-01-01 00:00:00' > 0 |
      +------------------------------------+
      |                                  1 |
      +------------------------------------+
      1 row in set (0.00 sec)
      

      Explicit conversion from TIME to DATETIME - NULL with a warning

      SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
      SET old_mode=ZERO_DATE_TIME_CAST;
      SELECT TIMESTAMP'2001-01-01 00:00:00' > CAST(TIME'00:00:00' AS DATETIME);
      

      +-------------------------------------------------------------------+
      | TIMESTAMP'2001-01-01 00:00:00' > CAST(TIME'00:00:00' AS DATETIME) |
      +-------------------------------------------------------------------+
      |                                                              NULL |
      +-------------------------------------------------------------------+
      1 row in set, 1 warning (0.00 sec)
      

      Explicit conversion from VARCHAR to DATETIME - zero datetime with a warning

      SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
      SET old_mode=ZERO_DATE_TIME_CAST;
      SELECT TIMESTAMP'2001-01-01 00:00:00' > CAST('00:00:00' AS DATETIME);
      

      +---------------------------------------------------------------+
      | TIMESTAMP'2001-01-01 00:00:00' > CAST('00:00:00' AS DATETIME) |
      +---------------------------------------------------------------+
      |                                                             1 |
      +---------------------------------------------------------------+
      1 row in set, 1 warning (0.00 sec)
      

      Explicit conversion from INT to DATETIME - zero datetime with a warning

      SET sql_mode='NO_ZERO_IN_DATE,NO_ZERO_DATE';
      SELECT TIMESTAMP'2001-01-01 00:00:00' > CAST(0 AS DATETIME);
      

      +------------------------------------------------------+
      | TIMESTAMP'2001-01-01 00:00:00' > CAST(0 AS DATETIME) |
      +------------------------------------------------------+
      |                                                    1 |
      +------------------------------------------------------+
      1 row in set, 1 warning (0.00 sec)
      

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                Created:
                Updated: