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

CAST(AS TIME) returns bad results for LAST_VALUE(),NAME_CONST(),SP variable

    XMLWordPrintable

Details

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

    Description

      SELECT CAST(DATE'2001-01-01' AS TIME), CAST(LAST_VALUE(DATE'2001-01-01') AS TIME);
      

      +--------------------------------+--------------------------------------------+
      | CAST(DATE'2001-01-01' AS TIME) | CAST(LAST_VALUE(DATE'2001-01-01') AS TIME) |
      +--------------------------------+--------------------------------------------+
      | 00:00:00                       | 00:20:01                                   |
      +--------------------------------+--------------------------------------------+
      1 row in set, 1 warning (0.00 sec)
      

      The expected behavior would be to return '00:00:00' in both columns.

      The same problem is repeatable with NAME_CONST:

      SELECT CAST(NAME_CONST('name',DATE'2001-01-01') AS TIME);
      

      +---------------------------------------------------+
      | CAST(NAME_CONST('name',DATE'2001-01-01') AS TIME) |
      +---------------------------------------------------+
      | 00:20:01                                          |
      +---------------------------------------------------+
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      

      The same problem is repeatable with SP variables:

      DELIMITER $$
      BEGIN NOT ATOMIC
        DECLARE a DATE DEFAULT '2001-01-01';
        SELECT CAST(a AS TIME);
      END;
      $$
      DELIMITER ;
      

      +-----------------+
      | CAST(a AS TIME) |
      +-----------------+
      | 00:20:01        |
      +-----------------+
      1 row in set (0.00 sec)
      Query OK, 0 rows affected, 1 warning (0.00 sec)
      

      The problem is that the underlying classes Item_func_last_value, Item_name_const, Item_splocal do not implement their own version of the virtual method get_date(), and the default implementation in Item::get_date() does not handle conversion between different temporal data types.
      So instead of doing direct date->time conversion, execution goes throw two conversions: date->string->time.

      The same problem is repeatable with Window functions, both with CAST(AS TIME), and with functions that expect a TIME inpute, like HOUR():

      CREATE OR REPLACE TABLE t1 (dt DATE,country VARCHAR(10), amount INT);
      INSERT INTO t1 VALUES ('2000-01-01','DE',102);
      INSERT INTO t1 VALUES ('2000-01-02','DE',101);
      SELECT
        dt, country, amount,
        FIRST_VALUE(dt) OVER () AS first,
        MINUTE(FIRST_VALUE(dt) OVER ()) AS m_first,
        LAST_VALUE(dt) OVER () AS last,
        MINUTE(LAST_VALUE(dt) OVER ()) AS m_last
      FROM t1
      ORDER BY country, dt;
      

      +------------+---------+--------+------------+---------+------------+--------+
      | dt         | country | amount | first      | m_first | last       | m_last |
      +------------+---------+--------+------------+---------+------------+--------+
      | 2000-01-01 | DE      |    102 | 2000-01-01 |      20 | 2000-01-02 |     20 |
      | 2000-01-02 | DE      |    101 | 2000-01-01 |      20 | 2000-01-02 |     20 |
      +------------+---------+--------+------------+---------+------------+--------+
      2 rows in set, 4 warnings (0.00 sec)
      

      Notice, the result of HOUR() is 20, which is something unexpected. It should be 0.

      Attachments

        Issue Links

          Activity

            People

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