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 created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            Environment {code:sql}
            SELECT
              COALESCE(TIME'800:00:00', NOW()) AS c,
              HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc;
            {code}
            {noformat}
            +---------------------+------+
            | c | hc |
            +---------------------+------+
            | 2018-03-22 08:00:00 | 800 |
            +---------------------+------+
            {noformat}
            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}}.

            {code:sql}
            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;
            {code}

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

            {code:sql}
            SELECT
              IF(TRUE,TIME'800:00:00', NOW()) AS c,
              HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc;
            {code}
            bar Alexander Barkov made changes -
            Description {code:sql}
            SELECT
              COALESCE(TIME'800:00:00', NOW()) AS c,
              HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc;
            {code}
            {noformat}
            +---------------------+------+
            | c | hc |
            +---------------------+------+
            | 2018-03-22 08:00:00 | 800 |
            +---------------------+------+
            {noformat}
            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}}.

            {code:sql}
            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;
            {code}

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

            {code:sql}
            SELECT
              IF(TRUE,TIME'800:00:00', NOW()) AS c,
              HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc;
            {code}
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            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 made changes -
            Description {code:sql}
            SELECT
              COALESCE(TIME'800:00:00', NOW()) AS c,
              HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc;
            {code}
            {noformat}
            +---------------------+------+
            | c | hc |
            +---------------------+------+
            | 2018-03-22 08:00:00 | 800 |
            +---------------------+------+
            {noformat}
            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}}.

            {code:sql}
            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;
            {code}

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

            {code:sql}
            SELECT
              IF(TRUE,TIME'800:00:00', NOW()) AS c,
              HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc;
            {code}
            		{code:sql}
            SELECT
              COALESCE(TIME'800:00:00', NOW()) AS c,
              HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc;
            {code}
            {noformat}
            +---------------------+------+
            | c | hc |
            +---------------------+------+
            | 2018-03-22 08:00:00 | 800 |
            +---------------------+------+
            {noformat}
            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:
            {code:sql}
            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;
            {code}

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

            {code:sql}
            SELECT
              IF(TRUE,TIME'800:00:00', NOW()) AS c,
              HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc;
            {code}
            bar Alexander Barkov added a comment -

            Pushed to bb-10.2-ext

            bar Alexander Barkov added a comment - Pushed to bb-10.2-ext
            bar Alexander Barkov made changes -
            issue.field.resolutiondate 2018-02-19 19:45:24.0 2018-02-19 19:45:24.099
            bar Alexander Barkov made changes -
            Fix Version/s 10.3.5 [ 22905 ]
            Fix Version/s 10.3 [ 22126 ]
            Resolution Fixed [ 1 ]
            Status Open [ 1 ] Closed [ 6 ]
            bar Alexander Barkov made changes -
            serg Sergei Golubchik made changes -
            Workflow MariaDB v3 [ 85623 ] MariaDB v4 [ 153816 ]

            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.