liuzonghao created CALCITE-3427: ----------------------------------- Summary: some subquery correlated case isn't 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 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)