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

Wrong result HOUR(case_expression_with_time_and_datetime)

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 10.3.5
    • Temporal Types
    • None

    Description

      SELECT
      		 
        COALESCE(TIME'800:00:00', NOW()) AS c,
      		 
        HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc;


      +---------------------+------+
      		 
      | c                   | hc   |
      		 
      +---------------------+------+
      		 
      | 2018-03-22 08:00:00 |  800 |
      		 
      +---------------------+------+

      The result for hc is wrong. It returns HOURS() of the first argument of COALESCE() before its conversion to DATETIME. The expected result is to return hours after conversion, i,e. 8, like the hour part in the first column c.

      The same problem is repeatable with CASE and its abbreviations:

      SELECT
      		 
        CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END AS c,
      		 
        HOUR(CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END) AS hc;


      SELECT
      		 
        IFNULL(TIME'800:00:00', NOW()) AS c,
      		 
        HOUR(IFNULL(TIME'800:00:00', NOW())) AS hc;


      SELECT
      		 
        IF(TRUE,TIME'800:00:00', NOW()) AS c,
      		 
        HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc;

      Attachments

        Issue Links

          blocks

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-8894 Inserting fractional seconds into MySQL 5.6 master breaks consistency on MariaDB 10 slave

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-15363 Wrong result for CAST(LAST_DAY(TIME'00:00:00') AS TIME)

          • Major - Major loss of function.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment -

            A related problem:

            SELECT
            		 
              ADDTIME(TIME'10:20:30', TIMESTAMP'2001-01-01 00:00:00') AS c1,
            		 
              ADDTIME(TIME'10:20:30', COALESCE(TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:00')) AS c2,
            		 
              ADDTIME(TIME'10:20:30', DATE'2001-01-01') AS c3, 
              ADDTIME(TIME'10:20:30', COALESCE(DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00')) AS c4;


            +------+------+----------+----------+
            		 
            | c1   | c2   | c3       | c4       |
            		 
            +------+------+----------+----------+
            		 
            | NULL | NULL | 34:20:30 | 34:20:30 |
            		 
            +------+------+----------+----------+

            The result for c3 and c4 is wrong. The expected result is NULL for all four columns.

            bar Alexander Barkov added a comment - A related problem: SELECT ADDTIME( TIME '10:20:30' , TIMESTAMP '2001-01-01 00:00:00' ) AS c1, ADDTIME( TIME '10:20:30' , COALESCE ( TIMESTAMP '2001-01-01 00:00:00' , TIMESTAMP '2001-01-01 00:00:00' )) AS c2, ADDTIME( TIME '10:20:30' , DATE '2001-01-01' ) AS c3, ADDTIME( TIME '10:20:30' , COALESCE ( DATE '2001-01-01' , TIMESTAMP '2001-01-01 00:00:00' )) AS c4; +------+------+----------+----------+ | c1 | c2 | c3 | c4 | +------+------+----------+----------+ | NULL | NULL | 34:20:30 | 34:20:30 | +------+------+----------+----------+ The result for c3 and c4 is wrong. The expected result is NULL for all four columns.
            bar Alexander Barkov added a comment -

            Pushed to bb-10.2-ext

            bar Alexander Barkov added a comment - Pushed to bb-10.2-ext

            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.