You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Hrudaya Reddy <hr...@csod.com> on 2020/01/18 01:55:54 UTC
Usage of SqlStdOperatorTable.BETWEEN from RelBuilder
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.
RE: Re: Usage of SqlStdOperatorTable.BETWEEN from RelBuilder
Posted by Hrudaya Reddy <hr...@csod.com>.
Hi Danny,
I really appreciate the fix. Thank you very much for your help.
Regards,
Hrudaya
-----Original Message-----
From: Danny Chan <yu...@gmail.com>
Sent: Saturday, January 18, 2020 6:40 PM
To: dev@calcite.apache.org
Subject: [EXTERNAL] Re: Usage of SqlStdOperatorTable.BETWEEN from RelBuilder
See https://urldefense.proofpoint.com/v2/url?u=https-3A__issues.apache.org_jira_browse_CALCITE-2D3747&d=DwIFaQ&c=IrjCfGj3zOfzN2DRIASEFg&r=MjX6IuQXq8W7yQ_nB4S_UQ&m=RSRhgtqD7MKnJ5_j6ApJPGaJ2QgW99X_SA5HlXDyH5I&s=G32bsRDyarFEXqJPnO5CxZcbtFNF19mSI3faMqK0ri0&e=
Best,
Danny Chan
在 2020年1月18日 +0800 AM9:56，Hrudaya Reddy <hr...@csod.com>，写道：
> 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(SqlBetwe
> enOperator.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
Re: Usage of SqlStdOperatorTable.BETWEEN from RelBuilder
Posted by Danny Chan <yu...@gmail.com>.
See https://issues.apache.org/jira/browse/CALCITE-3747
Best,
Danny Chan
在 2020年1月18日 +0800 AM9:56，Hrudaya Reddy <hr...@csod.com>，写道：
> 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.
Re: Usage of SqlStdOperatorTable.BETWEEN from RelBuilder
Posted by Danny Chan <yu...@gmail.com>.
Thanks for reporting this Hrudaya ~
I think the type inference of BETWEEN operator should be fixed, just like the CASE operator [1], would fire a fix soon ~
[1] https://github.com/apache/calcite/blob/44be09dff80768be39784d3bff596d5f6727273d/core/src/main/java/org/apache/calcite/sql/fun/SqlCaseOperator.java#L221
Best,
Danny Chan
在 2020年1月18日 +0800 AM9:56，Hrudaya Reddy <hr...@csod.com>，写道：
> 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.
RE: Re: Usage of SqlStdOperatorTable.BETWEEN from RelBuilder
Posted by Hrudaya Reddy <hr...@csod.com>.
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 <an...@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 <hr...@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.
Re: Usage of SqlStdOperatorTable.BETWEEN from RelBuilder
Posted by Andrei Sereda <an...@sereda.cc>.
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 <hr...@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.
>