[
https://issues.apache.org/jira/browse/CALCITE-3427?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
liuzonghao updated CALCITE-3427:
--------------------------------
Description:
{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.
was:
{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. furthermore we can only support correlated
variables is "xxx = $cor1 AND yyy = $cor2" case
> 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
> 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)
