[
https://issues.apache.org/jira/browse/CALCITE-6092?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17783356#comment-17783356
]
Julian Hyde commented on CALCITE-6092:
--------------------------------------
Here's the diff I used:
{noformat}
$ git log -p 625a2e03c4 -n1 | grep -C1 _MESSAGE
import static
org.apache.calcite.sql.test.SqlOperatorFixture.INVALID_EXTRACT_UNIT_VALIDATION_ERROR;
import static
org.apache.calcite.sql.test.SqlOperatorFixture.LITERAL_OUT_OF_RANGE_MESSAGE;
import static
org.apache.calcite.sql.test.SqlOperatorFixture.OUT_OF_RANGE_MESSAGE;
-import static
org.apache.calcite.sql.test.SqlOperatorFixture.STRING_TRUNC_MESSAGE;
import static org.apache.calcite.util.DateTimeStringUtils.getDateFormatter;
--
- if (Bug.CALCITE_2539_FIXED) {
- f.checkFails("cast(2.523 as char(2))", STRING_TRUNC_MESSAGE, true);
- }
--
- if (Bug.CALCITE_2539_FIXED) {
- f.checkFails("cast(1.3243232e0 as varchar(4))", STRING_TRUNC_MESSAGE,
- true);
- f.checkFails("cast(1.9e5 as char(4))", STRING_TRUNC_MESSAGE,
- true);
--
- if (Bug.CALCITE_2539_FIXED) {
- f.checkFails("cast(true as char(3))", INVALID_CHAR_MESSAGE, true);
- f.checkFails("cast(false as char(4))", INVALID_CHAR_MESSAGE, true);
- f.checkFails("cast(true as varchar(3))", INVALID_CHAR_MESSAGE, true);
- f.checkFails("cast(false as varchar(4))", INVALID_CHAR_MESSAGE, true);
- }
--
f.checkFails("cast('nottime' as TIME)", BAD_DATETIME_MESSAGE, true);
- if (Bug.CALCITE_2539_FIXED) {
- f.checkFails("cast('1241241' as TIME)", BAD_DATETIME_MESSAGE, true);
- f.checkFails("cast('12:54:78' as TIME)", BAD_DATETIME_MESSAGE, true);
- f.checkFails("cast('12:34:5' as TIME)", BAD_DATETIME_MESSAGE, true);
- f.checkFails("cast('12:3:45' as TIME)", BAD_DATETIME_MESSAGE, true);
- f.checkFails("cast('1:23:45' as TIME)", BAD_DATETIME_MESSAGE, true);
- }
--
}
f.checkFails("cast('nottime' as TIMESTAMP)", BAD_DATETIME_MESSAGE, true);
-
--
- f.checkFails("cast('1241241' as TIMESTAMP)",
- BAD_DATETIME_MESSAGE, true);
- f.checkFails("cast('1945-20-24 12:42:25.34' as TIMESTAMP)",
- BAD_DATETIME_MESSAGE, true);
- f.checkFails("cast('1945-01-24 25:42:25.34' as TIMESTAMP)",
- BAD_DATETIME_MESSAGE, true);
- f.checkFails("cast('1945-1-24 12:23:34.454' as TIMESTAMP)",
- BAD_DATETIME_MESSAGE, true);
- }
--
"1945-02-24", "DATE NOT NULL");
f.checkFails("cast('notdate' as DATE)", BAD_DATETIME_MESSAGE, true);
- if (Bug.CALCITE_2539_FIXED) {
- f.checkFails("cast('52534253' as DATE)", BAD_DATETIME_MESSAGE, true);
- f.checkFails("cast('1945-30-24' as DATE)", BAD_DATETIME_MESSAGE, true);
- }
--
- if (Bug.CALCITE_2539_FIXED) {
- f.checkFails("100.1 / 0.00000000000000001", OUT_OF_RANGE_MESSAGE,
- true);
{noformat}
> 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)
