You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by ha...@tutanota.com on 2019/06/23 22:29:38 UTC

Subquery

Although I can build a query which looks like

    val query = builder.scan("table1").filter(builder.equals(builder.field("filed_name1"), builder.literal("matched_value1"))).project(builder.field("field1_1"))

How can I place this query as subquery which might looks like sql statement 

select * from table2 where field2_1 in (select field1_1 from table1 where field_name1 = 'matched_value1')

Below code snippet doesn't get compiled 

buidler.scan("table2").filter(builder.call(IN, builder.field("field2_1"), /* query.getRexBuidler*/))

it's because I don't know how to convert query to RexNode. What is the correct way of building in clause within filter? Any suggestions? 

Current used calcite core version is 1.19.0

Thanks

回复：Subquery

Posted by Haisheng Yuan <h....@alibaba-inc.com>.
Try semiJoin in RelBuilder. Maybe that is what you want.





Thanks~
Haisheng Yuan------------------------------------------------------------------
发件人：<ha...@tutanota.com>
日　期：2019年06月24日 06:29:38
收件人：Dev<de...@calcite.apache.org>
主　题：Subquery


Although I can build a query which looks like

    val query = builder.scan("table1").filter(builder.equals(builder.field("filed_name1"), builder.literal("matched_value1"))).project(builder.field("field1_1"))

How can I place this query as subquery which might looks like sql statement 

select * from table2 where field2_1 in (select field1_1 from table1 where field_name1 = 'matched_value1')

Below code snippet doesn't get compiled 

buidler.scan("table2").filter(builder.call(IN, builder.field("field2_1"), /* query.getRexBuidler*/))

it's because I don't know how to convert query to RexNode. What is the correct way of building in clause within filter? Any suggestions? 

Current used calcite core version is 1.19.0

Thanks

Re: Subquery

Posted by Danny Chan <yu...@gmail.com>.
Hamishberridge, you can use RelBuilder#semiJoin [1], the pseudocode seems:

RelNode left = …
RelNode right = ...
relBuilder
  .push(left)
  .push(right)
  .semiJoin(conditions)

BTW, maybe you should use Calcite release 1.20.0 which would be released this week, the release has improve the join like expressions including SemiJoin.

[1] https://github.com/apache/calcite/blob/4e89fddab415a1e04b82c7d69960e399f608949f/core/src/main/java/org/apache/calcite/tools/RelBuilder.java#L1957

Best,
Danny Chan
在 2019年6月24日 +0800 AM6:29，hamishberridge@tutanota.com，写道：
>
> Although I can build a query which looks like
>
>     val query = builder.scan("table1").filter(builder.equals(builder.field("filed_name1"), builder.literal("matched_value1"))).project(builder.field("field1_1"))
>
> How can I place this query as subquery which might looks like sql statement
>
> select * from table2 where field2_1 in (select field1_1 from table1 where field_name1 = 'matched_value1')
>
> Below code snippet doesn't get compiled
>
> buidler.scan("table2").filter(builder.call(IN, builder.field("field2_1"), /* query.getRexBuidler*/))
>
> it's because I don't know how to convert query to RexNode. What is the correct way of building in clause within filter? Any suggestions?
>
> Current used calcite core version is 1.19.0
>
> Thanks