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

Inconsistent behavior of conversion to DATETIME for comparison

    Details

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

      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: