You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Sergey Nuyanzin (Jira)" <ji...@apache.org> on 2023/03/26 12:36:00 UTC
[jira] [Comment Edited] (CALCITE-4698) Respect input types and precision for datetime_plus and timestampadd
[ https://issues.apache.org/jira/browse/CALCITE-4698?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17705054#comment-17705054 ]
Sergey Nuyanzin edited comment on CALCITE-4698 at 3/26/23 12:35 PM:
--------------------------------------------------------------------
Thanks for having a look [~julianhyde].
{quote}
TIMESTAMP '1969-07-21 00:00:00.000' + INTERVAL '3' MILLISECOND.
{quote}
as far as I understand this is not supported (yet). Instead it's possible to specify precision like {{SECOND(1, 3)}} which I used in tests.
I added more tests including {{TIMESTAMP '...' + INTERVAL '...'}} and {{TIMESTAMP '...' - INTERVAL '...'}}.
Also added support of precision respect for these cases.
{quote}
The logic in deduceType is still convoluted. I have never understood why MILLISECOND and MICROSECOND have to be treated separately.
{quote}
This one is now treated together
{quote}
Can you add tests for SqlStdOperatorTable.DATETIME_PLUS, e.g. TIMESTAMP '...' + INTERVAL '...'. (I could not find any in SqlOperatorTest.)
{quote}
For this one I have a question first. It seems that {{PLUS}} for timestamp and intervals is converted into {{SqlStdOperatorTable.DATETIME_PLUS}} at {{org.apache.calcite.sql2rel.StandardConvertletTable#convertPlus}} and it seems that tests from {{org.apache.calcite.test.SqlOperatorTest#testPlusIntervalOperator}} cover tests for {{SqlStdOperatorTable.DATETIME_PLUS}}. Should we add more tests specifically for {{SqlStdOperatorTable.DATETIME_PLUS}} or may be just extract datetime related args into a separate method/test for {{SqlStdOperatorTable.DATETIME_PLUS}}?
was (Author: sergey nuyanzin):
Thanks for having a look [~julianhyde].
{quote}
TIMESTAMP '1969-07-21 00:00:00.000' + INTERVAL '3' MILLISECOND.
{quote}
as far as I understand this is not supported (yet). Instead it's possible to specify precision like {{SECOND(1, 3)}} which I used in tests.
I added more tests including {{TIMESTAMP '...' + INTERVAL '...'}} and {{TIMESTAMP '...' - INTERVAL '...'}}.
Also added support of precision respect for these cases.
{quote}
The logic in deduceType is still convoluted. I have never understood why MILLISECOND and MICROSECOND have to be treated separately.
{quote}
This one is now treated together
{quote}
Can you add tests for SqlStdOperatorTable.DATETIME_PLUS, e.g. TIMESTAMP '...' + INTERVAL '...'. (I could not find any in SqlOperatorTest.)
{quote}
For this one I have a question first. It seems that {{PLUS}} for timestamp and intervals is converted into {{SqlStdOperatorTable.DATETIME_PLUS}} and it seems that tests from {{org.apache.calcite.test.SqlOperatorTest#testPlusIntervalOperator}} cover tests for {{SqlStdOperatorTable.DATETIME_PLUS}}. Should we add more tests specifically for {{SqlStdOperatorTable.DATETIME_PLUS}}?
> Respect input types and precision for datetime_plus and timestampadd
> --------------------------------------------------------------------
>
> Key: CALCITE-4698
> URL: https://issues.apache.org/jira/browse/CALCITE-4698
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Caizhi Weng
> Priority: Major
> Labels: pull-request-available
> Time Spent: 1h 10m
> Remaining Estimate: 0h
>
> Currently {{SqlTimestampAddFunction#deduceType}} does not deal with timestamp_ltz type correctly. Return type of {{timestampadd(..., ..., timestamp_ltz)}} will be timestamp but it should be timestamp_ltz.
> Same issue is for precision datetime_plus.
> Also precision is lost.
> A number of queries highlighting the issue
> {code:sql}
> SELECT timestamp '2003-08-02 12:54:01' - INTERVAL '1.123' SECOND(1, 3);
> SELECT time '12:12:12' + INTERVAL '3.456' SECOND(1, 3);
> SELECT timestampadd(SQL_TSI_FRAC_SECOND, 2, timestamp with local time zone '2016-02-24 12:42:25.000');
> {code}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)