[ 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)