[jira] [Commented] (CALCITE-1690) Calcite timestamp literals cannot express precision above millisecond, TIMESTAMP(3)

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

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

Julian Hyde commented on CALCITE-1690:
--------------------------------------

Here's how to create a high-precision timestamp literal:

{code}
final RexBuilder b;
// Your type system must support enough digits;
// otherwise the literal will be silently rounded on creation
assert b.getTypeFactory().getTypeSystem()
    .getMaxPrecision(SqlTypeName.TIMESTAMP) >= 9;
// July 21st 1969 02:56:40 and 12,345,678 nanoseconds.
// The leading "0" is important; without it you'd have 123,456,780 nanoseconds.
final TimestampString ts = new TimestampString(1969, 7, 21, 2, 56, 40)
  .withFraction("012345678");
RexLiteral literal = b.makeTimestampLiteral(ts, 9);

// Or, if you don't need to go further than nanoseconds,
final TimestampString ts2 = new TimestampString(1969, 7, 21, 2, 56, 40)
  .withNanos(12345678);

// We recommend using RexLiteral.getValueAs(Class) to retrieve the value.
// The behavior of getValue, getValue2, getValue3 is unchanged.
Calendar cal = literal.getValueAs(Calendar.class); // only contains milliseconds
Comparable c = literal.getValue();
assert c.equals(cal);
Long l = literal.getValueAs(Long.class);  // only contains milliseconds
TimestampString ts3 = literal.getValueAs(TimestampString.class); // contains all digits
assert ts3.equals(ts);
assert ts3.equals(ts2);
{code}

There is a patch for review at https://github.com/julianhyde/calcite/tree/1690-date-time-literal. It depends on changes to avatica, which can be found at https://github.com/julianhyde/calcite-avatica/tree/1690-date-time-literal. You will need to "mvn install" avatica, and this fix will require an avatica-1.10 release.

[~ashutoshc], [~rusanu], Can you please review?

> Calcite timestamp literals cannot express precision above millisecond, TIMESTAMP(3)
> -----------------------------------------------------------------------------------
>
>                 Key: CALCITE-1690
>                 URL: https://issues.apache.org/jira/browse/CALCITE-1690
>             Project: Calcite
>          Issue Type: Bug
>            Reporter: Remus Rusanu
>            Assignee: Remus Rusanu
>             Fix For: 1.13.0
>
>
> {{RexBuilder.makeTimestampLiteral}} accepts the TS as a Java Calendar instance. Calendar type has only ms precision, thus types like {{TIMESTAMP(9)}} cannot be represented.
> This results in incorrect results in Hive due to constant reduction:
> {noformat}
> hive> explain select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22 09:00:00.123456789';
> OK
> Plan optimized by CBO.
> Stage-0
>   Fetch Operator
>     limit:-1
>     Select Operator [SEL_2]
>       Output:["_col0"]
>       Filter Operator [FIL_4]
>         predicate:(c17 = 2012-04-22 09:00:00.123)
>         TableScan [TS_0]
>           Output:["c17"]
> hive> select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22 09:00:00.123456789';
> OK
> Time taken: 0.687 seconds
> hive> set hive.cbo.enable=false;
> hive> explain select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22 09:00:00.123456789';
> OK
> Stage-0
>   Fetch Operator
>     limit:-1
>     Select Operator [SEL_2]
>       Output:["_col0"]
>       Filter Operator [FIL_4]
>         predicate:(c17 = '2012-04-22 09:00:00.123456789')
>         TableScan [TS_0]
>           Output:["c17"]
> hive> select c17 from testjdbcdriverdatatypetbl where c17='2012-04-22 09:00:00.123456789';
> OK
> 2012-04-22 09:00:00.123
> {noformat}
> Note how with CBO enabled the qualified row is missed. The plan shows that the constant was truncated to ms.



--
This message was sent by Atlassian JIRA
(v6.3.15#6346)