[CALCITE-6092] Invalid test cases in CAST String to Time

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Labels: pull-request-available
• Assignee: Unassigned
• Reporter: Jerin John
• Created: 03/Nov/23 22:36
• Updated: 28/Feb/24 06:03

Description

Encountered some tests 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)

Example test case (L1223):
cast('1241241' as TIME) | expected: "72:40:12" | actual: "16:40:12"

The string '1241241' is being parsed as number of hours, and internal conversion into milliseconds is done as {{{}1241241 * (int) MILLIS_PER_HOUR }} which results in na INT overflow.
The resultant overflowed value is 1701612160 milliseconds = 472.670044444 hours, and 472 % 24 is 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.

Issue Links

• is related to: CALCITE-5957 Valid DATE '1945-2-2' is not accepted due to regression
• is related to: CALCITE-2539 Several test case not passed in CalciteSqlOperatorTest.java
• is related to: CALCITE-5554 Add DAYOFWEEK and DAYOFYEAR as valid synonyms of DOW, DOY
• links to: GitHub Pull Request #3507