Iurii Gerzhedovich created CALCITE-7951:
-------------------------------------------
Summary: FLOOR, CEIL, TIMESTAMPADD and TIMESTAMPDIFF accept a time
frame larger than DAY for a TIME operand and return wrong results
Key: CALCITE-7951
URL: https://issues.apache.org/jira/browse/CALCITE-7951
Project: Calcite
Issue Type: Improvement
Components: core
Reporter: Iurii Gerzhedovich
The validator accepts a time frame larger than DAY, or a custom time frame, for
a TIME operand of FLOOR, CEIL, TIMESTAMPADD and TIMESTAMPDIFF. The query runs
and returns a wrong result. There is no error.
Reproduced on 1.42.0 with "jdbc:calcite:":
{noformat}
VALUES CAST(FLOOR(TIME '10:20:30' TO MONTH) AS VARCHAR) -- 10:20:29
VALUES CAST(FLOOR(TIME '10:20:30' TO YEAR) AS VARCHAR) -- 10:20:29
VALUES CAST(FLOOR(TIME '10:20:30' TO ISOWEEK) AS VARCHAR) -- 10:20:29
VALUES CAST(FLOOR(TIME '10:20:30' TO WEEK) AS VARCHAR) -- 10:20:30
VALUES CAST(FLOOR(TIME '10:20:30' TO DAY) AS VARCHAR) -- 10:20:30,
expected 00:00:00
VALUES CAST(TIMESTAMPADD(MONTH, 1, TIME '10:20:30') AS VARCHAR) -- 10:20:30
VALUES CAST(TIMESTAMPADD(ISOWEEK, 1, TIME '10:20:30') AS VARCHAR) -- 10:20:30
VALUES TIMESTAMPDIFF(MONTH, TIME '10:20:30', TIME '11:20:30') -- 118277
VALUES TIMESTAMPDIFF(ISOWEEK, TIME '10:20:30', TIME '11:20:30') -- 514285
{noformat}
EXTRACT already rejects such a unit for TIME:
{noformat}
VALUES EXTRACT(MONTH FROM TIME '10:20:30')
-- Cannot apply 'EXTRACT' to arguments of type 'EXTRACT(<INTERVAL MONTH> FROM
<TIME(0)>)'
{noformat}
Cause:
* SqlExtractFunction.validateCall checks that the unit is valid for the type of
the operand. SqlFloorFunction, SqlTimestampAddFunction and
SqlTimestampDiffFunction check only that the time frame exists.
* At run time, FloorImplementor (RexImpTable) gives a TIME value (milliseconds
of the day) to DateTimeUtils.unixDateFloor / unixDateCeil (for units from DAY
to MILLENNIUM) and to SqlFunctions.customDateFloor / customDateCeil (for custom
time frames). These methods expect a number of days. The same happens for the
custom time frame methods of TIMESTAMPADD and TIMESTAMPDIFF.
Proposal:
* For a TIME operand, the validator rejects a time unit larger than DAY and a
custom time frame in FLOOR, CEIL, TIMESTAMPADD and TIMESTAMPDIFF (the same way
EXTRACT does). An alias of a time unit that is valid for TIME (for example,
SQL_TSI_HOUR) stays valid.
* FLOOR and CEIL of TIME to DAY use arithmetic on the time value (the result of
FLOOR is 00:00:00), not the DATE methods.
* The check uses the derived types of the operands.
SqlExtractFunction.validateCall uses getValidatedNodeTypeIfKnown, and in some
clauses the type is not known yet, so the check is skipped there:
{noformat}
SELECT EXTRACT(MONTH FROM x) FROM (VALUES (TIME '10:20:30')) AS v(x)
-- Cannot apply 'EXTRACT' to arguments of type ... (correct)
SELECT x FROM (VALUES (TIME '10:20:30')) AS v(x) ORDER BY EXTRACT(MONTH FROM x)
-- java.lang.AssertionError: unexpected TIME (at run time)
SELECT COUNT(*) FILTER (WHERE EXTRACT(MONTH FROM x) = 1) FROM (VALUES (TIME
'10:20:30')) AS v(x)
-- java.lang.AssertionError: unexpected TIME (at run time)
{noformat}
The new checks must not have this problem, and the EXTRACT check can be fixed
in the same change.
Open question: what CEIL of TIME must return when the result is midnight of the
next day. Now CAST(CEIL(TIME '23:30:00' TO HOUR) AS VARCHAR) returns 24:00:00,
which is not a valid TIME value. Possible answers: 00:00:00 (TIME arithmetic
wraps, as TIME + INTERVAL does) or an error.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)