[jira] [Commented] (CALCITE-6092) Invalid test cases in CAST String to Time

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6092?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17783354#comment-17783354 ] 

Julian Hyde commented on CALCITE-6092:
--------------------------------------

I agree. It's possible that {{CAST(string AS TIME)}} should be a bit more liberal than {{TIME string}} but not this liberal. The same commit also introduced some problems for DATE, CHAR. For example, the following tests were removed but should not have been:
{code}
f.checkFails("cast(1.9e5 as char(4))", STRING_TRUNC_MESSAGE, true);
f.checkFails("cast(true as char(3))", INVALID_CHAR_MESSAGE, true);
f.checkFails("cast(false as varchar(4))", INVALID_CHAR_MESSAGE, true);
f.checkFails("cast('1945-20-24 12:42:25.34' as TIMESTAMP)", BAD_DATETIME_MESSAGE, true);
f.checkFails("cast('52534253' as DATE)", BAD_DATETIME_MESSAGE, true);
f.checkFails("cast('1945-30-24' as DATE)", BAD_DATETIME_MESSAGE, true);
{code}
I may have missed some.

Some negative tests were too strict, and were correctly converted to positive tests. For example,
{code}
f.checkFails("cast('1945-1-24 12:23:34.454' as TIMESTAMP)", BAD_DATETIME_MESSAGE, true);
{code}
(Note that these cases may overlap with CALCITE-5957.)

> Invalid test cases in CAST String to Time
> -----------------------------------------
>
>                 Key: CALCITE-6092
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6092
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Jerin John
>            Priority: Major
>
> Encountered some [tests|https://github.com/apache/calcite/blob/590ec85f0fcff7173c288c350c3f60e640976a34/testkit/src/main/java/org/apache/calcite/test/SqlOperatorTest.java#L1237-L1238] within SqlOperatorTest file for the CAST operator on String to Datetime conversions, which are found to be invalid time strings on some tested instances of DBs like BQ, MySql, Postgres.
> It seems these tests were originally ignored as `BAD_DATETIME_MESSAGE` but then updated to verify conversion performed by the CAST operator (refer to JIRA ticket: CALCITE-5554 and [commit|https://github.com/apache/calcite/commit/625a2e03c4c5583279350bf04e3db2a31b1ec411#diff-fdd0c725fc7c6fd56965f59b1d51e4c7a9b5b5be27da2e54b8b8273dc980cd64R1118-R1119])
> Example test case (L1223):
> The string '1241241' is being parsed as number of hours, and internal conversion into milliseconds is done as {{1241241 * (int) MILLIS_PER_HOUR}}.
> The resultant value is 1701612160 milliseconds = 472.670044444 hours, and 472 % 24 is indeed 16 (and .67 is roughly 2/3 so 40ish minutes seems legit)
> Considering that multiple dialects catch these cases as invalid time strings for conversion, code should be updated to handle them as exceptions and the tests to be corrected to reflect this behavior.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)