You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@calcite.apache.org by Chia-Hung Lin <cl...@googlemail.com.INVALID> on 2019/06/25 21:38:30 UTC

How to do SemiJoin?

A quick question. I am trying to do semiJoin(). The code looks like below.

    val subquery = builder.
      scan("foodmart", "customer").
      filter(builder.equals(builder.field("city"), builder.literal("Albany"))).
      project(builder.field("customer_id")).
      build

    val mainquery = builder.
      scan("foodmart", "sales_fact_1998").
      project(builder.field("unit_sales")).
      build

    val relnode = builder.push(mainquery).push(subquery).semiJoin(
      builder.field("customer_id")
    ).build

But when executing, it throws an error message saying
`java.lang.RuntimeException: java.sql.SQLException: Error while
preparing statement [null]`, and following another exception that
mentions

Cause: org.codehaus.commons.compiler.CompileException: Line 18, Column
66: No applicable constructor/method found for actual parameters
"java.math.BigDecimal"; candidates are: "public static boolean
org.apache.calcite.runtime.SqlFunctions.isTrue(java.lang.Boolean)"

Although it looks like code generation issue, I suspect it's more
likely my incorrect usage to construct semiJoin function. What is the
correct way to use semiJoin? Is there any example or doc that may
contain related info? I have this question because I can not find such
info after searching on the internet. Most of semiJoin results found
on the internet are internal implementation which looks like not
related to the usage.

Thanks

Re: How to do SemiJoin?

Posted by Chia-Hung Lin <cl...@googlemail.com.INVALID>.
It looks like the operation is done by

1. Build main query
    * `main query= builder.scan(table name).build`
2. Then push the main table
    * `builder.push(main query)`
3. Build subquery
    * `sub query = builder.scan().filter().build`
4. Push the subquery
    * `builder.push(sub query)`
5. Then do semi-join operation on the condition field
    * `builder.semiJoin(builder.equals(builder.filed(...filed name),
builder.filed(...field name)))`

Thanks for the suggestion, it's very helpful!

On Tue, 25 Jun 2019 at 23:26, Haisheng Yuan <h....@alibaba-inc.com> wrote:
>
> The input parameter of semiJoin method should be join conditions, not just fields.
> Check the example usage in RelMetadataTest.java#checkPredicates L1435.
>
> - Haisheng
>
> ------------------------------------------------------------------
> 发件人：Chia-Hung Lin<cl...@googlemail.com.INVALID>
> 日　期：2019年06月26日 05:38:30
> 收件人：<de...@calcite.apache.org>
> 主　题：How to do SemiJoin?
>
> A quick question. I am trying to do semiJoin(). The code looks like below.
>
>     val subquery = builder.
>       scan("foodmart", "customer").
>       filter(builder.equals(builder.field("city"), builder.literal("Albany"))).
>       project(builder.field("customer_id")).
>       build
>
>     val mainquery = builder.
>       scan("foodmart", "sales_fact_1998").
>       project(builder.field("unit_sales")).
>       build
>
>     val relnode = builder.push(mainquery).push(subquery).semiJoin(
>       builder.field("customer_id")
>     ).build
>
> But when executing, it throws an error message saying
> `java.lang.RuntimeException: java.sql.SQLException: Error while
> preparing statement [null]`, and following another exception that
> mentions
>
> Cause: org.codehaus.commons.compiler.CompileException: Line 18, Column
> 66: No applicable constructor/method found for actual parameters
> "java.math.BigDecimal"; candidates are: "public static boolean
> org.apache.calcite.runtime.SqlFunctions.isTrue(java.lang.Boolean)"
>
> Although it looks like code generation issue, I suspect it's more
> likely my incorrect usage to construct semiJoin function. What is the
> correct way to use semiJoin? Is there any example or doc that may
> contain related info? I have this question because I can not find such
> info after searching on the internet. Most of semiJoin results found
> on the internet are internal implementation which looks like not
> related to the usage.
>
> Thanks

Re: How to do SemiJoin?

Posted by Haisheng Yuan <h....@alibaba-inc.com>.
The input parameter of semiJoin method should be join conditions, not just fields. 
Check the example usage in RelMetadataTest.java#checkPredicates L1435.

- Haisheng

------------------------------------------------------------------
发件人：Chia-Hung Lin<cl...@googlemail.com.INVALID>
日　期：2019年06月26日 05:38:30
收件人：<de...@calcite.apache.org>
主　题：How to do SemiJoin?

A quick question. I am trying to do semiJoin(). The code looks like below.

    val subquery = builder.
      scan("foodmart", "customer").
      filter(builder.equals(builder.field("city"), builder.literal("Albany"))).
      project(builder.field("customer_id")).
      build

    val mainquery = builder.
      scan("foodmart", "sales_fact_1998").
      project(builder.field("unit_sales")).
      build

    val relnode = builder.push(mainquery).push(subquery).semiJoin(
      builder.field("customer_id")
    ).build

But when executing, it throws an error message saying
`java.lang.RuntimeException: java.sql.SQLException: Error while
preparing statement [null]`, and following another exception that
mentions

Cause: org.codehaus.commons.compiler.CompileException: Line 18, Column
66: No applicable constructor/method found for actual parameters
"java.math.BigDecimal"; candidates are: "public static boolean
org.apache.calcite.runtime.SqlFunctions.isTrue(java.lang.Boolean)"

Although it looks like code generation issue, I suspect it's more
likely my incorrect usage to construct semiJoin function. What is the
correct way to use semiJoin? Is there any example or doc that may
contain related info? I have this question because I can not find such
info after searching on the internet. Most of semiJoin results found
on the internet are internal implementation which looks like not
related to the usage.

Thanks