You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Jess Balint <jb...@gmail.com> on 2017/01/23 16:36:04 UTC
RexNode representation of IN w/expr list
Hi,
I'm trying to use RelBuilder.call() with SqlStdOperatorTable.IN. I want to
use IN with the scalar expr list form, not a subquery. How should this be
represented by a RexNode? I tried using SqlToRelConverter and it parses as
"x = 1 OR x = 2 ....". When I used the expr list form through RelBuilder
(flat list of arguments with LHS first) and passed it to RelToSqlConverter,
I get "x IN 1 IN 2" due to this code in SqlImplementor:
if (op instanceof SqlBinaryOperator && nodeList.size() > 2) {
// In RexNode trees, OR and AND have any number of children;
// SqlCall requires exactly 2. So, convert to a left-deep binary
tree.
return createLeftCall(op, nodeList);
}
I am wondering if one of the following is true:
* IN w/expr list is not intended to be represented in RexNode form, hence
the conversion by SqlToRelConverter creates an OR tree
* I am representing the arguments incorrectly w/something like call(IN,
field("x"), literal(1), literal(2)) and need to use a specialized RexNode
structure
* RelToSqlConverter is lacking the proper handling of the IN operator
Any hints/thoughts appreciated.
Thanks.
Jess
Re: RexNode representation of IN w/expr list
Posted by Julian Hyde <jh...@apache.org>.
The Rel tree looks good. I don't think we ever invested the effort to
make RexSubQuery => SqlNode => unparse work properly. RexSubQuery is a
strange beast: it has only one RexNode argument but also has a RelNode
argument which is hidden because it's not a RexNode.
On Wed, Jan 25, 2017 at 2:43 PM, Jess Balint <jb...@gmail.com> wrote:
> Thanks for the hint. I've got this rel tree now:
>
> LogicalProject(F_8=[$2], F_7=[$0], F_11=[$8])
> LogicalFilter(condition=[IN($8, {
> LogicalValues(tuples=[[{ 100 }, { 200 }, { 300 }]])
> })])
> LogicalJoin(condition=[=($0, $7)], joinType=[inner])
> JdbcTableScan(table=[[PUBLIC, EMPLOYEES]])
> JdbcTableScan(table=[[PUBLIC, SALARIES]])
>
> Does this look right? I used RexSubQuery.in() and passed this straight to
> relBuilder.filter(). I converted this to SqlNode but it won't unparse as
> the SqlBasicCall(IN) only has one operand but is unparsed due to IN being a
> binary operator. The operand is the "$8" input ref (this is due to
> RexSubQuery returning only one operand and not the rel). Any ideas on where
> the issue is here?
>
> Thanks a lot.
>
> Jess
>
> On Wed, Jan 25, 2017 at 4:32 PM, Jess Balint <jb...@gmail.com> wrote:
>
>> Thanks for the hint. I've got this rel tree now:
>>
>>
>> On Mon, Jan 23, 2017 at 1:18 PM, Julian Hyde <jh...@apache.org> wrote:
>>
>>> “IN” with a list of scalar values is just syntactic sugar. If you use
>>> RelBuilder there is no equivalent to ‘x in (1, 2)’; you need to write ‘x =
>>> 1 or x = 2’ long-hand.
>>>
>>> If you want to, you can instead use RelBuilder to build the equivalent of
>>> ‘x in (values (1), (2))’ — that is, a sub-query. You use a RexSubQuery node
>>> for that. That formulation is semantically equivalent to the OR but
>>> structurally quite different, and will tend to be optimized differently.
>>>
>>> Julian
>>>
>>>
>>> > On Jan 23, 2017, at 8:36 AM, Jess Balint <jb...@gmail.com> wrote:
>>> >
>>> > Hi,
>>> >
>>> > I'm trying to use RelBuilder.call() with SqlStdOperatorTable.IN. I want
>>> to
>>> > use IN with the scalar expr list form, not a subquery. How should this
>>> be
>>> > represented by a RexNode? I tried using SqlToRelConverter and it parses
>>> as
>>> > "x = 1 OR x = 2 ....". When I used the expr list form through RelBuilder
>>> > (flat list of arguments with LHS first) and passed it to
>>> RelToSqlConverter,
>>> > I get "x IN 1 IN 2" due to this code in SqlImplementor:
>>> >
>>> > if (op instanceof SqlBinaryOperator && nodeList.size() > 2) {
>>> > // In RexNode trees, OR and AND have any number of children;
>>> > // SqlCall requires exactly 2. So, convert to a left-deep
>>> binary
>>> > tree.
>>> > return createLeftCall(op, nodeList);
>>> > }
>>> >
>>> > I am wondering if one of the following is true:
>>> > * IN w/expr list is not intended to be represented in RexNode form,
>>> hence
>>> > the conversion by SqlToRelConverter creates an OR tree
>>> > * I am representing the arguments incorrectly w/something like call(IN,
>>> > field("x"), literal(1), literal(2)) and need to use a specialized
>>> RexNode
>>> > structure
>>> > * RelToSqlConverter is lacking the proper handling of the IN operator
>>> >
>>> > Any hints/thoughts appreciated.
>>> >
>>> > Thanks.
>>> > Jess
>>>
>>>
>>
Re: RexNode representation of IN w/expr list
Posted by Jess Balint <jb...@gmail.com>.
Thanks for the hint. I've got this rel tree now:
LogicalProject(F_8=[$2], F_7=[$0], F_11=[$8])
LogicalFilter(condition=[IN($8, {
LogicalValues(tuples=[[{ 100 }, { 200 }, { 300 }]])
})])
LogicalJoin(condition=[=($0, $7)], joinType=[inner])
JdbcTableScan(table=[[PUBLIC, EMPLOYEES]])
JdbcTableScan(table=[[PUBLIC, SALARIES]])
Does this look right? I used RexSubQuery.in() and passed this straight to
relBuilder.filter(). I converted this to SqlNode but it won't unparse as
the SqlBasicCall(IN) only has one operand but is unparsed due to IN being a
binary operator. The operand is the "$8" input ref (this is due to
RexSubQuery returning only one operand and not the rel). Any ideas on where
the issue is here?
Thanks a lot.
Jess
On Wed, Jan 25, 2017 at 4:32 PM, Jess Balint <jb...@gmail.com> wrote:
> Thanks for the hint. I've got this rel tree now:
>
>
> On Mon, Jan 23, 2017 at 1:18 PM, Julian Hyde <jh...@apache.org> wrote:
>
>> “IN” with a list of scalar values is just syntactic sugar. If you use
>> RelBuilder there is no equivalent to ‘x in (1, 2)’; you need to write ‘x =
>> 1 or x = 2’ long-hand.
>>
>> If you want to, you can instead use RelBuilder to build the equivalent of
>> ‘x in (values (1), (2))’ — that is, a sub-query. You use a RexSubQuery node
>> for that. That formulation is semantically equivalent to the OR but
>> structurally quite different, and will tend to be optimized differently.
>>
>> Julian
>>
>>
>> > On Jan 23, 2017, at 8:36 AM, Jess Balint <jb...@gmail.com> wrote:
>> >
>> > Hi,
>> >
>> > I'm trying to use RelBuilder.call() with SqlStdOperatorTable.IN. I want
>> to
>> > use IN with the scalar expr list form, not a subquery. How should this
>> be
>> > represented by a RexNode? I tried using SqlToRelConverter and it parses
>> as
>> > "x = 1 OR x = 2 ....". When I used the expr list form through RelBuilder
>> > (flat list of arguments with LHS first) and passed it to
>> RelToSqlConverter,
>> > I get "x IN 1 IN 2" due to this code in SqlImplementor:
>> >
>> > if (op instanceof SqlBinaryOperator && nodeList.size() > 2) {
>> > // In RexNode trees, OR and AND have any number of children;
>> > // SqlCall requires exactly 2. So, convert to a left-deep
>> binary
>> > tree.
>> > return createLeftCall(op, nodeList);
>> > }
>> >
>> > I am wondering if one of the following is true:
>> > * IN w/expr list is not intended to be represented in RexNode form,
>> hence
>> > the conversion by SqlToRelConverter creates an OR tree
>> > * I am representing the arguments incorrectly w/something like call(IN,
>> > field("x"), literal(1), literal(2)) and need to use a specialized
>> RexNode
>> > structure
>> > * RelToSqlConverter is lacking the proper handling of the IN operator
>> >
>> > Any hints/thoughts appreciated.
>> >
>> > Thanks.
>> > Jess
>>
>>
>
Re: RexNode representation of IN w/expr list
Posted by Jess Balint <jb...@gmail.com>.
Thanks for the hint. I've got this rel tree now:
On Mon, Jan 23, 2017 at 1:18 PM, Julian Hyde <jh...@apache.org> wrote:
> “IN” with a list of scalar values is just syntactic sugar. If you use
> RelBuilder there is no equivalent to ‘x in (1, 2)’; you need to write ‘x =
> 1 or x = 2’ long-hand.
>
> If you want to, you can instead use RelBuilder to build the equivalent of
> ‘x in (values (1), (2))’ — that is, a sub-query. You use a RexSubQuery node
> for that. That formulation is semantically equivalent to the OR but
> structurally quite different, and will tend to be optimized differently.
>
> Julian
>
>
> > On Jan 23, 2017, at 8:36 AM, Jess Balint <jb...@gmail.com> wrote:
> >
> > Hi,
> >
> > I'm trying to use RelBuilder.call() with SqlStdOperatorTable.IN. I want
> to
> > use IN with the scalar expr list form, not a subquery. How should this be
> > represented by a RexNode? I tried using SqlToRelConverter and it parses
> as
> > "x = 1 OR x = 2 ....". When I used the expr list form through RelBuilder
> > (flat list of arguments with LHS first) and passed it to
> RelToSqlConverter,
> > I get "x IN 1 IN 2" due to this code in SqlImplementor:
> >
> > if (op instanceof SqlBinaryOperator && nodeList.size() > 2) {
> > // In RexNode trees, OR and AND have any number of children;
> > // SqlCall requires exactly 2. So, convert to a left-deep binary
> > tree.
> > return createLeftCall(op, nodeList);
> > }
> >
> > I am wondering if one of the following is true:
> > * IN w/expr list is not intended to be represented in RexNode form, hence
> > the conversion by SqlToRelConverter creates an OR tree
> > * I am representing the arguments incorrectly w/something like call(IN,
> > field("x"), literal(1), literal(2)) and need to use a specialized RexNode
> > structure
> > * RelToSqlConverter is lacking the proper handling of the IN operator
> >
> > Any hints/thoughts appreciated.
> >
> > Thanks.
> > Jess
>
>
Re: RexNode representation of IN w/expr list
Posted by Julian Hyde <jh...@apache.org>.
“IN” with a list of scalar values is just syntactic sugar. If you use RelBuilder there is no equivalent to ‘x in (1, 2)’; you need to write ‘x = 1 or x = 2’ long-hand.
If you want to, you can instead use RelBuilder to build the equivalent of ‘x in (values (1), (2))’ — that is, a sub-query. You use a RexSubQuery node for that. That formulation is semantically equivalent to the OR but structurally quite different, and will tend to be optimized differently.
Julian
> On Jan 23, 2017, at 8:36 AM, Jess Balint <jb...@gmail.com> wrote:
>
> Hi,
>
> I'm trying to use RelBuilder.call() with SqlStdOperatorTable.IN. I want to
> use IN with the scalar expr list form, not a subquery. How should this be
> represented by a RexNode? I tried using SqlToRelConverter and it parses as
> "x = 1 OR x = 2 ....". When I used the expr list form through RelBuilder
> (flat list of arguments with LHS first) and passed it to RelToSqlConverter,
> I get "x IN 1 IN 2" due to this code in SqlImplementor:
>
> if (op instanceof SqlBinaryOperator && nodeList.size() > 2) {
> // In RexNode trees, OR and AND have any number of children;
> // SqlCall requires exactly 2. So, convert to a left-deep binary
> tree.
> return createLeftCall(op, nodeList);
> }
>
> I am wondering if one of the following is true:
> * IN w/expr list is not intended to be represented in RexNode form, hence
> the conversion by SqlToRelConverter creates an OR tree
> * I am representing the arguments incorrectly w/something like call(IN,
> field("x"), literal(1), literal(2)) and need to use a specialized RexNode
> structure
> * RelToSqlConverter is lacking the proper handling of the IN operator
>
> Any hints/thoughts appreciated.
>
> Thanks.
> Jess