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

          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17563 Different results using table or view when comparing values of time type

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17625 Different warnings when comparing a garbage to DATETIME vs TIME

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-17634 Regression: TIME(0)=TIME('z') returns NULL vs 1

          • Major - Major loss of function.
          • Closed

          Activity

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.