Thanks a lot for your input Andrei. This workaround is working for us now. I am looking into the fix by Danny Chan (https://issues.apache.org/jira/browse/CALCITE-3747)
Regards, Hrudaya -----Original Message----- From: Andrei Sereda <and...@sereda.cc> Sent: Saturday, January 18, 2020 9:37 AM To: dev@calcite.apache.org Subject: [EXTERNAL] Re: Usage of SqlStdOperatorTable.BETWEEN from RelBuilder Hi Hrudaya, It seems that SqlBetweenOperator doesn’t correctly infer return type when created by RexBuilder. As a workaround, can you replace BETWEEN by composition of two ANDs ? (user_id >= 1 AND user_id <= 5) // RelBuilder builder = ... builder.scan("users"); RexNode condition1 = builder.call(SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, builder.field("user_id"), builder.literal(20)); RexNode condition2 = builder.call(SqlStdOperatorTable.LESS_THAN_OR_EQUAL, builder.field("user_id"), builder.literal(30)); RexNode between = builder.and(condition1, condition2); RelNode root = builder .filter(between) .build(); Devs, Do you agree to set returnTypeInference to ReturnTypes.BOOLEAN_NULLABLE (in constructor) for SqlBetweenOperator ? And handle the case with RexCallBinding ? Thanks, Andrei. On Fri, Jan 17, 2020 at 8:56 PM Hrudaya Reddy <hre...@csod.com> wrote: > Hi all, > > We are trying to generate the following sql query > > SELECT [user_id], [create_date] > FROM [users] > WHERE [user_id] BETWEEN 1 AND 5 > > I am trying the following but I get ClassCastException error > > RexNode betweenCondition = > relBuilder.call(SqlStdOperatorTable.BETWEEN, > relBuilder.field("user_id"), > relBuilder.literal(1), relBuilder.literal(5)); > > Exception in thread "main" java.lang.ClassCastException: > org.apache.calcite.rex.RexCallBinding cannot be cast to > org.apache.calcite.sql.SqlCallBinding > at > org.apache.calcite.sql.fun.SqlBetweenOperator.inferReturnType(SqlBetweenOperator.java:139) > at > org.apache.calcite.rex.RexBuilder.deriveReturnType(RexBuilder.java:276) > at org.apache.calcite.tools.RelBuilder.call(RelBuilder.java:602) > at org.apache.calcite.tools.RelBuilder.call(RelBuilder.java:596) > > I would really appreciate it if you could guide me with the correct > usage of the BETWEEN operator. > > Thanks in advance. > > Regards, > Hrudaya > > This message, together with any attachments, is intended only for the > use of the individual or entity to which it is addressed and may > contain confidential and/or privileged information. If you are not the > intended recipient(s), or the employee or agent responsible for > delivery of this message to the intended recipient(s), you are hereby > notified that any dissemination, distribution or copying of this > message, or any attachment, is strictly prohibited. If you have > received this message in error, please immediately notify the sender > and delete the message, together with any attachments, from your computer. > Thank you for your cooperation. > ---------------------------------------------------------------------- *EXTERNAL MESSAGE WARNING: This email originated from outside of Cornerstone. Do not click links or open attachments unless you recognize the sender and know the content is safe. Please see this wiki for more information on email safety: https://wiki.cornerstoneondemand.com/display/ISS/Security+Awareness This message, together with any attachments, is intended only for the use of the individual or entity to which it is addressed and may contain confidential and/or privileged information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this message, or any attachment, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete the message, together with any attachments, from your computer. Thank you for your cooperation.