[
https://issues.apache.org/jira/browse/CALCITE-7878?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18130393#comment-18130393
]
Mihai Budiu commented on CALCITE-7878:
--------------------------------------
There are lots of bugs in the handling of timestamps related to precision.
> A java.sql.Timestamp column is typed TIMESTAMP(0), but comparisons on it see
> milliseconds
> -----------------------------------------------------------------------------------------
>
> Key: CALCITE-7878
> URL: https://issues.apache.org/jira/browse/CALCITE-7878
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Vladimir Sitnikov
> Priority: Major
>
> A column declared with
> {{typeFactory.createJavaType(java.sql.Timestamp.class)}} holds
> {{java.sql.Timestamp.valueOf("2018-12-14 18:29:34.123")}}. In one row,
> comparisons see the milliseconds, while {{CAST}} to {{VARCHAR}} and {{MAX}}
> return the value without them:
> ||Query||Result on main ({{6ff13eaccd}})||
> |{{select 1 from T where "ts" = TIMESTAMP '2018-12-14 18:29:34.123'}}|one row|
> |{{select 1 from T where "ts" > TIMESTAMP '2018-12-14 18:29:34.122'}}|one row|
> |{{select cast("ts" as varchar(30)) from T}}|{{2018-12-14 18:29:34}}|
> |{{select cast("ts" as TIMESTAMP(3)) from T}}|{{2018-12-14 18:29:34.123}}|
> |{{select max("ts") from T}}|{{2018-12-14 18:29:34}}|
> Table setup (Object[] rows over a {{QueryableTable}}):
> {code:java}
> typeFactory.createStructType(
> Arrays.asList(
> typeFactory.createJavaType(java.sql.Date.class),
> typeFactory.createJavaType(java.sql.Time.class),
> typeFactory.createJavaType(java.sql.Timestamp.class)),
> Arrays.asList("dt", "tm", "ts"));
> // one row: Date.valueOf("2018-12-14"), Time.valueOf("18:29:34"),
> // Timestamp.valueOf("2018-12-14 18:29:34.123")
> {code}
> Expected: the cast and the comparisons agree. Either both keep the
> milliseconds ({{cast("ts" as varchar(30))}} returns {{2018-12-14
> 18:29:34.123}}), or both drop them ({{"ts" = TIMESTAMP '2018-12-14
> 18:29:34.123'}} returns no row).
> Guess, not needed for the report above: {{JavaType}} does not override
> {{getPrecision()}}, so the column becomes {{TIMESTAMP}} with the default
> precision 0 from {{RelDataTypeSystemImpl.getDefaultPrecision}}, while the
> comparison code reads the {{Timestamp}} value with its milliseconds. PR 971
> (CALCITE-1703) proposed overriding {{JavaType.getPrecision()}} to return 9
> for {{java.sql.Timestamp}}.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
