[
https://issues.apache.org/jira/browse/CALCITE-3427?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16955739#comment-16955739
]
Chunwei Lei commented on CALCITE-3427:
--------------------------------------
It would be great if you can provide a test to reproduce it.
> Some subquery correlated cases are not fully implemented
> --------------------------------------------------------
>
> Key: CALCITE-3427
> URL: https://issues.apache.org/jira/browse/CALCITE-3427
> Project: Calcite
> Issue Type: Bug
> Components: core
> Affects Versions: 1.21.0
> Reporter: liuzonghao
> Priority: Major
> Fix For: next
>
>
> {code:java}
> //代码占位符
> // for correlated case queries such as
> //
> // select e.deptno, e.deptno < some (
> // select deptno from emp where emp.name = e.name) as v
> // from emp as e
> //
> // becomes
> //
> // select e.deptno,
> // case
> // when indicator is null then false // sub-query is empty for
> corresponding corr value
> // when q.c = 0 then false // sub-query is empty
> // when (e.deptno < q.m) is true then true
> // when q.c > q.d then unknown // sub-query has at least one null
> // else e.deptno < q.m
> // end as v
> // from emp as e
> // left outer join (
> // select name, max(deptno) as m, count(*) as c, count(deptno) as d,
> // "alwaysTrue" as indicator
> // from emp group by name) as q on e.name = q.name
> Set<CorrelationId> varsUsed = RelOptUtil.getVariablesUsed(e.rel);
> builder.push(e.rel)
> .aggregate(builder.groupKey(),
> builder.aggregateCall(minMax, builder.field(0)).as("m"),
> builder.count(false, "c"),
> builder.count(false, "d", builder.field(0)));
> final List<RexNode> parentQueryFields = new ArrayList<>();
> parentQueryFields.addAll(builder.fields());
> String indicator = "trueLiteral";
> parentQueryFields.add(builder.alias(literalTrue, indicator));
> builder.project(parentQueryFields).as("q");
> builder.join(JoinRelType.LEFT, literalTrue, variablesSet);
> caseRexNode = builder.call(SqlStdOperatorTable.CASE,
> builder.call(SqlStdOperatorTable.IS_NULL,
> builder.field("q", indicator)),
> literalFalse,
> builder.call(SqlStdOperatorTable.EQUALS, builder.field("q", "c"),
> builder.literal(0)),
> literalFalse,
> builder.call(SqlStdOperatorTable.IS_TRUE,
> builder.call(RelOptUtil.op(op.comparisonKind, null),
> e.operands.get(0), builder.field("q", "m"))),
> literalTrue,
> builder.call(SqlStdOperatorTable.GREATER_THAN,
> builder.field("q", "c"), builder.field("q", "d")),
> literalUnknown,
> builder.call(RelOptUtil.op(op.comparisonKind, null),
> e.operands.get(0), builder.field("q", "m")));
> {code}
> implementation code is in SubQueryRemoveRule.rewriteSome method. the groupKey
> and join condition is lacked.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
