in modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rule/logical/IgniteSubQueryRemoveRule.java [180:467]
private static RexNode rewriteSome(RexSubQuery e, Set<CorrelationId> variablesSet,
RelBuilder builder, int subQueryIndex) {
// If the sub-query is guaranteed empty, just return
// FALSE.
final RelMetadataQuery mq = e.rel.getCluster().getMetadataQuery();
if (RelMdUtil.isRelDefinitelyEmpty(mq, e.rel)) {
return builder.getRexBuilder().makeLiteral(Boolean.FALSE, e.getType(), true);
}
// Most general case, where the left and right keys might have nulls, and
// caller requires 3-valued logic return.
//
// select e.deptno, e.deptno < some (select deptno from emp) as v
// from emp as e
//
// becomes
//
// select e.deptno,
// case
// 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
// cross join (
// select max(deptno) as m, count(*) as c, count(deptno) as d
// from emp) as q
//
final SqlQuantifyOperator op = (SqlQuantifyOperator) e.op;
switch (op.comparisonKind) {
case GREATER_THAN_OR_EQUAL:
case LESS_THAN_OR_EQUAL:
case LESS_THAN:
case GREATER_THAN:
case NOT_EQUALS:
break;
default:
// "SOME =" should have been rewritten into IN.
throw new AssertionError("unexpected " + op);
}
final RexNode caseRexNode;
final RexNode literalFalse = builder.literal(false);
final RexNode literalTrue = builder.literal(true);
final RexLiteral literalUnknown =
builder.getRexBuilder().makeNullLiteral(literalFalse.getType());
final SqlAggFunction minMax = op.comparisonKind == SqlKind.GREATER_THAN
|| op.comparisonKind == SqlKind.GREATER_THAN_OR_EQUAL
? SqlStdOperatorTable.MIN
: SqlStdOperatorTable.MAX;
String qAlias = "q";
if (subQueryIndex != 0) {
qAlias = "q" + subQueryIndex;
}
if (variablesSet.isEmpty()) {
switch (op.comparisonKind) {
case GREATER_THAN_OR_EQUAL:
case LESS_THAN_OR_EQUAL:
case LESS_THAN:
case GREATER_THAN:
// for non-correlated case queries such as
// select e.deptno, e.deptno < some (select deptno from emp) as v
// from emp as e
//
// becomes
//
// select e.deptno,
// case
// 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
// cross join (
// select max(deptno) as m, count(*) as c, count(deptno) as d
// from emp) as q
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)))
.as(qAlias)
.join(JoinRelType.INNER);
caseRexNode =
builder.call(SqlStdOperatorTable.CASE,
builder.equals(builder.field(qAlias, "c"), builder.literal(0)),
literalFalse,
builder.call(SqlStdOperatorTable.IS_TRUE,
builder.call(RexUtil.op(op.comparisonKind),
e.operands.get(0), builder.field(qAlias, "m"))),
literalTrue,
builder.greaterThan(builder.field(qAlias, "c"),
builder.field(qAlias, "d")),
literalUnknown,
builder.call(RexUtil.op(op.comparisonKind),
e.operands.get(0), builder.field(qAlias, "m")));
break;
case NOT_EQUALS:
// for non-correlated case queries such as
// select e.deptno, e.deptno <> some (select deptno from emp) as v
// from emp as e
//
// becomes
//
// select e.deptno,
// case
// when q.c = 0 then false // sub-query is empty
// when e.deptno is null then unknown
// when q.c <> q.d && q.d <= 1 then e.deptno != m || unknown
// when q.d = 1
// then e.deptno != m // sub-query has the distinct result
// else true
// end as v
// from emp as e
// cross join (
// select count(*) as c, count(deptno) as d, max(deptno) as m
// from (select distinct deptno from emp)) as q
builder.push(e.rel);
builder.distinct()
.aggregate(builder.groupKey(),
builder.count(false, "c"),
builder.count(false, "d", builder.field(0)),
builder.max(builder.field(0)).as("m"))
.as(qAlias)
.join(JoinRelType.INNER);
caseRexNode =
builder.call(SqlStdOperatorTable.CASE,
builder.equals(builder.field("c"), builder.literal(0)),
literalFalse,
builder.isNull(e.getOperands().get(0)),
literalUnknown,
builder.and(
builder.notEquals(builder.field("d"), builder.field("c")),
builder.lessThanOrEqual(builder.field("d"),
builder.literal(1))),
builder.or(
builder.notEquals(e.operands.get(0), builder.field(qAlias, "m")),
literalUnknown),
builder.equals(builder.field("d"), builder.literal(1)),
builder.notEquals(e.operands.get(0), builder.field(qAlias, "m")),
literalTrue);
break;
default:
throw new AssertionError("not possible - per above check");
}
} else {
final String indicator = "trueLiteral";
final List<RexNode> parentQueryFields = new ArrayList<>();
switch (op.comparisonKind) {
case GREATER_THAN_OR_EQUAL:
case LESS_THAN_OR_EQUAL:
case LESS_THAN:
case GREATER_THAN:
// 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
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)));
parentQueryFields.addAll(builder.fields());
parentQueryFields.add(builder.alias(literalTrue, indicator));
builder.project(parentQueryFields).as(qAlias);
builder.join(JoinRelType.LEFT, literalTrue, variablesSet);
caseRexNode =
builder.call(SqlStdOperatorTable.CASE,
builder.isNull(builder.field(qAlias, indicator)),
literalFalse,
builder.equals(builder.field(qAlias, "c"), builder.literal(0)),
literalFalse,
builder.call(SqlStdOperatorTable.IS_TRUE,
builder.call(RexUtil.op(op.comparisonKind),
e.operands.get(0), builder.field(qAlias, "m"))),
literalTrue,
builder.greaterThan(builder.field(qAlias, "c"),
builder.field(qAlias, "d")),
literalUnknown,
builder.call(RexUtil.op(op.comparisonKind),
e.operands.get(0), builder.field(qAlias, "m")));
break;
case NOT_EQUALS:
// 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 is null then unknown
// when q.c <> q.d && q.dd <= 1
// then e.deptno != m || unknown
// when q.dd = 1
// then e.deptno != m // sub-query has the distinct result
// else true
// end as v
// from emp as e
// left outer join (
// select name, count(*) as c, count(deptno) as d, count(distinct deptno) as dd,
// max(deptno) as m, "alwaysTrue" as indicator
// from emp group by name) as q on e.name = q.name
// Additional details on the `q.c <> q.d && q.dd <= 1` clause:
// the q.c <> q.d comparison identifies if there are any null values,
// since count(*) counts null values and count(deptno) does not.
// if there's no null value, c should be equal to d.
// the q.dd <= 1 part means: true if there is at most one non-null value
// so this clause means:
// "if there are any null values and there is at most one non-null value".
builder.push(e.rel)
.aggregate(builder.groupKey(),
builder.count(false, "c"),
builder.count(false, "d", builder.field(0)),
builder.count(true, "dd", builder.field(0)),
builder.max(builder.field(0)).as("m"));
parentQueryFields.addAll(builder.fields());
parentQueryFields.add(builder.alias(literalTrue, indicator));
builder.project(parentQueryFields).as(qAlias); // TODO use projectPlus
builder.join(JoinRelType.LEFT, literalTrue, variablesSet);
caseRexNode =
builder.call(SqlStdOperatorTable.CASE,
builder.isNull(builder.field(qAlias, indicator)),
literalFalse,
builder.equals(builder.field("c"), builder.literal(0)),
literalFalse,
builder.isNull(e.getOperands().get(0)),
literalUnknown,
builder.and(
builder.notEquals(builder.field("d"), builder.field("c")),
builder.lessThanOrEqual(builder.field("dd"),
builder.literal(1))),
builder.or(
builder.notEquals(e.operands.get(0), builder.field(qAlias, "m")),
literalUnknown),
builder.equals(builder.field("dd"), builder.literal(1)),
builder.notEquals(e.operands.get(0), builder.field(qAlias, "m")),
literalTrue);
break;
default:
throw new AssertionError("not possible - per above check");
}
}
// CASE statement above is created with nullable boolean type, but it might
// not be correct. If the original sub-query node's type is not nullable it
// is guaranteed for case statement to not produce NULLs. Therefore to avoid
// planner complaining we need to add cast. Note that nullable type is
// created due to the MIN aggregate call, since there is no GROUP BY.
if (!e.getType().isNullable()) {
return builder.cast(caseRexNode, e.getType().getSqlTypeName());
}
return caseRexNode;
}