Vladimir Sitnikov created CALCITE-7878:
------------------------------------------
Summary: 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
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)
