[jira] [Commented] (CALCITE-3244) RelDecorrelator unable to decorrelate expression with filter and aggregate on top

["Julian Hyde (JIRA)" <ji...@apache.org>]

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

Julian Hyde commented on CALCITE-3244:
--------------------------------------

Thanks for clarifying, [~vvysotskyi].

It's worth checking whether that query works if you change the second {{values(1), (2)}} to {{values (2),(3)}}. Just in case the fishy behavior is due to a DAG.

I agree that RelDecorrelator ought be able to handle this query.

You will be converting "GROUP BY ()" to "GROUP BY var", so be careful with the semantics of empty tables. Note that when "t" is empty, {code}SELECT count(*) FROM t GROUP BY (){code} will return 1 row, and {code}SELECT count(*) FROM t GROUP BY 'a constant'{code} will return 0 rows.

> RelDecorrelator unable to decorrelate expression with filter and aggregate on top
> ---------------------------------------------------------------------------------
>
>                 Key: CALCITE-3244
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3244
>             Project: Calcite
>          Issue Type: Improvement
>    Affects Versions: 1.20.0
>            Reporter: benj
>            Assignee: Danny Chan
>            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)