Rather than > relBuilder.getRexBuilder().makeDateLiteral(new DateString(2019, 1, 1))
I think you can use > relBuilder.literal(new DateString(2019, 1, 1)) (Or should be able to.) Same effect, a bit less code. > On Feb 22, 2019, at 8:21 AM, Anton Haidai <anton.hai...@gmail.com> wrote: > > Stamatis, Hongze - thank you for your help, it works now. > > On Fri, Feb 22, 2019 at 6:05 PM Hongze Zhang <notify...@126.com> wrote: >> >>> EnumerableCalc(.... expr#6=[TIMESTAMPADD($t3, $t4, $t5)] ...) >>> >>> But Calcite (both 1.18 and 1.19-SNAPSHOT) can't execute this plan: >>> >>> Caused by: java.lang.RuntimeException: cannot translate call >>> TIMESTAMPADD($t3, $t4, $t5)As @Stamatis has said, this is normal. Calcite >>> doesn't provide an implementation for TIMESTAMPADD by default.Below is an >>> usage example of operator DATETIME_PLUS if you are using RexBuilder:final >>> RexNode shiftedDateField = >> relBuilder.call( >> SqlStdOperatorTable.DATETIME_PLUS, >> rexBuilder.makeTimestampLiteral(new TimestampString(2019, 1, 1, 0, 0, 0), 0), >> rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, >> rexBuilder.makeIntervalLiteral( >> TimeUnit.MONTH.multiplier, >> new SqlIntervalQualifier(TimeUnit.MONTH, null, >> SqlParserPos.ZERO) >> ), >> relBuilder.literal(1) >> ) >> ); >> >> >> >> >> >> >> >> >> >> >> >> >> >> 在 2019-02-22 23:49:35，"Stamatis Zampetakis" <zabe...@gmail.com> 写道： >>> Hi Anton, >>> >>> The TIMESTAMPADD operator never ends up in the logical plan so essentially >>> there is no code for executing it. It is just used as a syntactic sugar in >>> the parser. >>> >>> Have a look below to see how you can obtain the equivalent in relational >>> algebra: >>> https://github.com/apache/calcite/blob/8eb852039db04c132ae7a99943495f87cf39dfd2/core/src/main/java/org/apache/calcite/sql2rel/StandardConvertletTable.java#L1437 >>> >>> Best, >>> Stamatis >>> >>> Στις Παρ, 22 Φεβ 2019 στις 3:51 μ.μ., ο/η Anton Haidai < >>> anton.hai...@gmail.com> έγραψε: >>> >>>> Hongze, thank you for your answer. Your code successfully produces a >>>> plan with the following node: >>>> >>>> EnumerableCalc(.... expr#6=[TIMESTAMPADD($t3, $t4, $t5)] ...) >>>> >>>> But Calcite (both 1.18 and 1.19-SNAPSHOT) can't execute this plan: >>>> >>>> Caused by: java.lang.RuntimeException: cannot translate call >>>> TIMESTAMPADD($t3, $t4, $t5) >>>> at >>>> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translateCall(RexToLixTranslator.java:719) >>>> at >>>> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate0(RexToLixTranslator.java:693) >>>> at >>>> org.apache.calcite.adapter.enumerable.RexToLixTranslator.translate(RexToLixTranslator.java:197) >>>> >>>> While the SQL from my first message produces the following node: >>>> >>>> EnumerableCalc(expr#0..26=[{inputs}], expr#27=[1], expr#28=[1], >>>> expr#29=[*($t27, $t28)], expr#30=[+($t0, $t29)], EXPR$0=[$t30]) >>>> >>>> And Calcite is able to execute it. So now I'm trying to replicate it >>>> using a RelBuilder (using INTERVAL instead of TIMESTAMP_ADD), without >>>> a success so far. >>>> >>>> On Fri, Feb 22, 2019 at 4:11 PM Hongze Zhang <notify...@126.com> wrote: >>>>> >>>>> Correct the flag: >>>>> TimeUnitRange.MONTH -> TimeUnit.MONTH >>>>> >>>>> >>>>> Best, >>>>> Hongze >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> At 2019-02-22 22:07:11, "Hongze Zhang" <notify...@126.com> wrote: >>>>> >>>>> Hi Anton, >>>>> >>>>> >>>>> Following code works to me: >>>>> >>>>> >>>>> >>>>> final RexNode shiftedDateField = >>>>> >>>>> relBuilder.call( >>>>> >>>>> SqlStdOperatorTable.TIMESTAMP_ADD, >>>>> >>>>> relBuilder.getRexBuilder().makeFlag(TimeUnitRange.MONTH), >>>>> >>>>> relBuilder.literal(1), >>>>> >>>>> relBuilder.getRexBuilder().makeDateLiteral(new DateString(2019, >>>> 1, 1)) >>>>> >>>>> ); >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> Best, >>>>> Hongze >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> At 2019-02-22 19:16:09, "Anton Haidai" <anton.hai...@gmail.com> wrote: >>>>>> Hello. In SQL, I can execute a query like "SELECT TIMESTAMPADD(month, >>>>>> 1, "date" ) FROM ..." and it works. But my attempts to do the same >>>>>> thing using RelBuilder are not successful, for example, this code does >>>>>> not work: >>>>>> >>>>>> RexNode shiftedDateField = relBuilder.call( >>>>>> SqlStdOperatorTable.TIMESTAMP_ADD, >>>>>> relBuilder.literal("month"), >>>>>> relBuilder.literal(1), >>>>>> dateFieldRef >>>>>> ); >>>>>> >>>>>> Could you please tell me, how to make a TIMESTAMP_ADD call using >>>> RelBuilder? >>>>>> -- >>>>>> Best regards, >>>>>> Anton. >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>> >>>> >>>> >>>> -- >>>> Best regards, >>>> Anton. >>>> > > > > -- > Best regards, > Anton.
