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 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 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)
