[jira] [Comment Edited] (CALCITE-3244) Decorrelating sub-queries - Adding Implicit RowID support

["Volodymyr Vysotskyi (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-3244?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16906038#comment-16906038 ] 

Volodymyr Vysotskyi edited comment on CALCITE-3244 at 8/13/19 10:10 AM:
------------------------------------------------------------------------

Thanks, [~benj641] for reporting this issue and [~julianhyde] for taking an initial look.

Looks like the root cause why it fails in Drill is connected with the way how Calcite decorrelates sub-queries of such type.

For the following test:
{code:java}
  @Test public void testDecorrelateScalarSubQuery() throws Exception {
    final String sql = "select \n"
        + "    (select count(t2.id) \n"
        + "    from (values(1), (2)) t2(id) where t2.id = t1.id)\n"
        + "    from (values(1), (2)) t1(id)";
    checkSubQuery(sql).withLateDecorrelation(true).check();
  }
{code}
Calcite will generate the following plan with {{LogicalCorrelate}} operator:
{noformat}
LogicalProject(EXPR$0=[$1])
  LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
    LogicalValues(tuples=[[{ 1 }, { 2 }]])
    LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
      LogicalFilter(condition=[=($0, $cor0.ID)])
        LogicalValues(tuples=[[{ 1 }, { 2 }]])
{noformat}
But Drill does not support Correlate operator except the case when it corresponds to lateral unnest so it was expected that RelDecorrelator will rewrite the plan to remove correlate operator.


was (Author: vvysotskyi):
Thanks, [~benj641] for reporting this issue and [~julianhyde] for taking an initial look.

Looks like the root cause why it fails in Drill is connected with the way how Calcite decorrelates sub-queries of such type.

For the following test:
{code:java}
  @Test public void testDecorrelateScalarSubQuery() throws Exception {
    final String sql =
        "select \n"
        + "    (select count(t2.id) \n"
        + "    from (values(1), (2)) t2(id) where t2.id = t1.id)\n"
        + "    from (values(1), (2)) t1(id)";
    checkSubQuery(sql).withLateDecorrelation(true).check();
  }
{code}
Calcite will generate the following plan with {{LogicalCorrelate}} operator:
{noformat}
LogicalProject(EXPR$0=[$1])
  LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
    LogicalValues(tuples=[[{ 1 }, { 2 }]])
    LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
      LogicalFilter(condition=[=($0, $cor0.ID)])
        LogicalValues(tuples=[[{ 1 }, { 2 }]])
{noformat}

But Drill does not support Correlate operator except the case when it corresponds to lateral unnest so it was expected that RelDecorrelator will rewrite the plan to remove correlate operator.

> Decorrelating sub-queries - Adding Implicit RowID support
> ---------------------------------------------------------
>
>                 Key: CALCITE-3244
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3244
>             Project: Calcite
>          Issue Type: Improvement
>    Affects Versions: 1.20.0
>            Reporter: benj
>            Priority: Major
>
> Some very useful type of requests currently failed with:
> {code:java}
> SYSTEM ERROR: UnsupportedOperationException: Adding Implicit RowID column is not supported for ValuesPrel operator 
> {code}
> Examples from DRILL-7050:
> {code:sql}
> select t1.id,
>      (select count(t2.id) 
>      from (
>          select 1 as id 
>          union all 
>          select 2 as id
>      ) t2 where t2.id = t1.id)
>  from (
>      select 1 as id 
>      union all 
>      select 2 as id
>  ) t1
> {code}
> {code:sql}
> SELECT t,
> (SELECT count(*) FROM
>  (SELECT split(r,' ') AS r FROM
>   (SELECT sub.t AS r)) AS x
>  ,LATERAL(SELECT $unnest AS u FROM unnest(x.r))
>  /* WHERE ... */) t2
> FROM
> (SELECT 'unnest is useful' AS t) sub
> {code}
>  
> _Please note that in 1.18 the error for these requests was:_
> {code:java}
> Error: PLAN ERROR: Cannot convert RexNode to equivalent Drill expression. RexNode Class: org.apache.calcite.rex.RexCorrelVariable, RexNode Digest: $cor0
> {code}



--
This message was sent by Atlassian JIRA
(v7.6.14#76016)