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 <[email protected]> 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.
>
