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

CAST(MAX(DATE'2001-01-01') AS TIME) returns a wrong result

Details

    • 10.2.11

    Description

      CAST from DATE to TIME usually returns zero time 00:00:00:

      SELECT CAST(DATE'2001-01-01' AS TIME)


      +--------------------------------+
      		 
      | CAST(DATE'2001-01-01' AS TIME) |
      		 
      +--------------------------------+
      		 
      | 00:00:00                       |
      		 
      +--------------------------------+

      However, in some cases it works differently:

      SELECT CAST(MAX(DATE'2001-01-01') AS TIME);
      		 
      SHOW WARNINGS;


      +-------------------------------------+
      		 
      | CAST(MAX(DATE'2001-01-01') AS TIME) |
      		 
      +-------------------------------------+
      		 
      | 00:20:01                            |
      		 
      +-------------------------------------+
      		 
      1 row in set, 1 warning (0.00 sec)
      		 
       
      		 
      +---------+------+----------------------------------------------+
      		 
      | Level   | Code | Message                                      |
      		 
      +---------+------+----------------------------------------------+
      		 
      | Warning | 1292 | Truncated incorrect time value: '2001-01-01' |
      		 
      +---------+------+----------------------------------------------+
      		 
      1 row in set (0.00 sec)

      The problem happens because Item_sum_max does not implement its own get_date() method, so execution goes through the generic Item::get_date() which calls val_str() followed by string-to-time conversion.

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov created issue -
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Description CAST from DATE to TIME usually returns zero time {{00:00:00}}:
          {code:sql}
          SELECT CAST(DATE'2001-01-01' AS TIME)
          {code}
          {noformat}
          +--------------------------------+
          | CAST(DATE'2001-01-01' AS TIME) |
          +--------------------------------+
          | 00:00:00 |
          +--------------------------------+
          {noformat}

          However, in some cases it works differently:
          {code:sql}
          SELECT CAST(MAX(DATE'2001-01-01') AS TIME);
          SHOW WARNINGS;
          {code}
          {noformat}
          +-------------------------------------+
          | CAST(MAX(DATE'2001-01-01') AS TIME) |
          +-------------------------------------+
          | 00:20:01 |
          +-------------------------------------+
          1 row in set, 1 warning (0.00 sec)

          +---------+------+----------------------------------------------+
          | Level | Code | Message |
          +---------+------+----------------------------------------------+
          | Warning | 1292 | Truncated incorrect time value: '2001-01-01' |
          +---------+------+----------------------------------------------+
          1 row in set (0.00 sec)
          {noforrnat}
          		CAST from DATE to TIME usually returns zero time {{00:00:00}}:
          {code:sql}
          SELECT CAST(DATE'2001-01-01' AS TIME)
          {code}
          {noformat}
          +--------------------------------+
          | CAST(DATE'2001-01-01' AS TIME) |
          +--------------------------------+
          | 00:00:00 |
          +--------------------------------+
          {noformat}

          However, in some cases it works differently:
          {code:sql}
          SELECT CAST(MAX(DATE'2001-01-01') AS TIME);
          SHOW WARNINGS;
          {code}
          {noformat}
          +-------------------------------------+
          | CAST(MAX(DATE'2001-01-01') AS TIME) |
          +-------------------------------------+
          | 00:20:01 |
          +-------------------------------------+
          1 row in set, 1 warning (0.00 sec)

          +---------+------+----------------------------------------------+
          | Level | Code | Message |
          +---------+------+----------------------------------------------+
          | Warning | 1292 | Truncated incorrect time value: '2001-01-01' |
          +---------+------+----------------------------------------------+
          1 row in set (0.00 sec)
          {noformat}
          bar Alexander Barkov made changes -
          Description CAST from DATE to TIME usually returns zero time {{00:00:00}}:
          {code:sql}
          SELECT CAST(DATE'2001-01-01' AS TIME)
          {code}
          {noformat}
          +--------------------------------+
          | CAST(DATE'2001-01-01' AS TIME) |
          +--------------------------------+
          | 00:00:00 |
          +--------------------------------+
          {noformat}

          However, in some cases it works differently:
          {code:sql}
          SELECT CAST(MAX(DATE'2001-01-01') AS TIME);
          SHOW WARNINGS;
          {code}
          {noformat}
          +-------------------------------------+
          | CAST(MAX(DATE'2001-01-01') AS TIME) |
          +-------------------------------------+
          | 00:20:01 |
          +-------------------------------------+
          1 row in set, 1 warning (0.00 sec)

          +---------+------+----------------------------------------------+
          | Level | Code | Message |
          +---------+------+----------------------------------------------+
          | Warning | 1292 | Truncated incorrect time value: '2001-01-01' |
          +---------+------+----------------------------------------------+
          1 row in set (0.00 sec)
          {noformat}
          		CAST from DATE to TIME usually returns zero time {{00:00:00}}:
          {code:sql}
          SELECT CAST(DATE'2001-01-01' AS TIME)
          {code}
          {noformat}
          +--------------------------------+
          | CAST(DATE'2001-01-01' AS TIME) |
          +--------------------------------+
          | 00:00:00 |
          +--------------------------------+
          {noformat}

          However, in some cases it works differently:
          {code:sql}
          SELECT CAST(MAX(DATE'2001-01-01') AS TIME);
          SHOW WARNINGS;
          {code}
          {noformat}
          +-------------------------------------+
          | CAST(MAX(DATE'2001-01-01') AS TIME) |
          +-------------------------------------+
          | 00:20:01 |
          +-------------------------------------+
          1 row in set, 1 warning (0.00 sec)

          +---------+------+----------------------------------------------+
          | Level | Code | Message |
          +---------+------+----------------------------------------------+
          | Warning | 1292 | Truncated incorrect time value: '2001-01-01' |
          +---------+------+----------------------------------------------+
          1 row in set (0.00 sec)
          {noformat}

          The problem happens because Item_sum_max does not implement its own get_date() method, so execution goes through the generic Item::get_date() which calls val_str() followed by string-to-time conversion.
          bar Alexander Barkov added a comment -

          Also repeatable in this scenario:

          DROP FUNCTION IF EXISTS f1;
          		 
          CREATE FUNCTION f1() RETURNS DATE RETURN DATE'2001-01-01';
          		 
          SELECT CAST(f1() AS TIME);

          bar Alexander Barkov added a comment - Also repeatable in this scenario: DROP FUNCTION IF EXISTS f1; CREATE FUNCTION f1() RETURNS DATE RETURN DATE '2001-01-01' ; SELECT CAST (f1() AS TIME );
          bar Alexander Barkov made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          bar Alexander Barkov made changes -
          Status In Progress [ 3 ] Stalled [ 10000 ]
          bar Alexander Barkov made changes -
          Fix Version/s 10.2.11 [ 22634 ]
          Fix Version/s 10.3.3 [ 22644 ]
          Fix Version/s 10.2 [ 14601 ]
          Resolution Fixed [ 1 ]
          Status Stalled [ 10000 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Sprint 10.2.11 [ 203 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 77112 ] MariaDB v4 [ 150922 ]

          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.