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)

Reply via email to