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

Wrong result HOUR(case_expression_with_time_and_datetime)

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.1, 10.2, 10.3, 10.0
    • Fix Version/s: 10.3.5
    • Component/s: Temporal Types
    • Labels:
      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

            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:
                Resolved: