in modules/sql-engine/src/main/java/org/apache/ignite/internal/sql/engine/rule/logical/IgniteSubQueryRemoveRule.java [578:831]
private static RexNode rewriteIn(RexSubQuery e, Set<CorrelationId> variablesSet,
RelOptUtil.Logic logic, RelBuilder builder, int offset, 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 in (select deptno from emp)
// from emp as e
//
// becomes
//
// select e.deptno,
// case
// when ct.c = 0 then false
// when e.deptno is null then null
// when dt.i is not null then true
// when ct.ck < ct.c then null
// else false
// end
// from emp as e
// left join (
// (select count(*) as c, count(deptno) as ck from emp) as ct
// cross join (select distinct deptno, true as i from emp)) as dt
// on e.deptno = dt.deptno
//
// If keys are not null we can remove "ct" and simplify to
//
// select e.deptno,
// case
// when dt.i is not null then true
// else false
// end
// from emp as e
// left join (select distinct deptno, true as i from emp) as dt
// on e.deptno = dt.deptno
//
// We could further simplify to
//
// select e.deptno,
// dt.i is not null
// from emp as e
// left join (select distinct deptno, true as i from emp) as dt
// on e.deptno = dt.deptno
//
// but have not yet.
//
// If the logic is TRUE we can just kill the record if the condition
// evaluates to FALSE or UNKNOWN. Thus the query simplifies to an inner
// join:
//
// select e.deptno,
// true
// from emp as e
// inner join (select distinct deptno from emp) as dt
// on e.deptno = dt.deptno
//
builder.push(e.rel);
final List<RexNode> fields = new ArrayList<>(builder.fields());
// for the case when IN has only literal operands, it may be handled
// in the simpler way:
//
// select e.deptno, 123456 in (select deptno from emp)
// from emp as e
//
// becomes
//
// select e.deptno,
// case
// when dt.c IS NULL THEN FALSE
// when e.deptno IS NULL THEN NULL
// when dt.cs IS FALSE THEN NULL
// when dt.cs IS NOT NULL THEN TRUE
// else false
// end
// from emp AS e
// cross join (
// select distinct deptno is not null as cs, count(*) as c
// from emp
// where deptno = 123456 or deptno is null or e.deptno is null
// order by cs desc limit 1) as dt
//
String ctAlias = "ct";
if (subQueryIndex != 0) {
ctAlias = "ct" + subQueryIndex;
}
boolean allLiterals = RexUtil.allLiterals(e.getOperands());
final List<RexNode> expressionOperands = new ArrayList<>(e.getOperands());
final List<RexNode> keyIsNulls = e.getOperands().stream()
.filter(operand -> operand.getType().isNullable())
.map(builder::isNull)
.collect(Collectors.toList());
final RexLiteral trueLiteral = builder.literal(true);
final RexLiteral falseLiteral = builder.literal(false);
final RexLiteral unknownLiteral =
builder.getRexBuilder().makeNullLiteral(trueLiteral.getType());
if (allLiterals) {
final List<RexNode> conditions =
Pair.zip(expressionOperands, fields).stream()
.map(pair -> builder.equals(pair.left, pair.right))
.collect(Collectors.toList());
switch (logic) {
case TRUE:
case TRUE_FALSE:
builder.filter(conditions);
builder.project(builder.alias(trueLiteral, "cs"));
builder.distinct();
break;
default:
List<RexNode> isNullOperands = fields.stream()
.map(builder::isNull)
.collect(Collectors.toList());
// uses keyIsNulls conditions in the filter to avoid empty results
isNullOperands.addAll(keyIsNulls);
builder.filter(
builder.or(
builder.and(conditions),
builder.or(isNullOperands)));
RexNode project =
builder.and(
fields.stream()
.map(builder::isNotNull)
.collect(Collectors.toList()));
builder.project(builder.alias(project, "cs"));
if (variablesSet.isEmpty()) {
builder.aggregate(builder.groupKey(builder.field("cs")),
builder.count(false, "c"));
} else {
builder.distinct();
}
// sorts input with desc order since we are interested
// only in the case when one of the values is true.
// When true value is absent then we are interested
// only in false value.
builder.sortLimit(0, 1,
ImmutableList.of(builder.desc(builder.field("cs"))));
}
// clears expressionOperands and fields lists since
// all expressions were used in the filter
expressionOperands.clear();
fields.clear();
} else {
switch (logic) {
case TRUE:
builder.aggregate(builder.groupKey(fields));
break;
case TRUE_FALSE_UNKNOWN:
case UNKNOWN_AS_TRUE:
// Builds the cross join
builder.aggregate(builder.groupKey(),
builder.count(false, "c"),
builder.count(builder.fields()).as("ck"));
builder.as(ctAlias);
if (!variablesSet.isEmpty()) {
builder.join(JoinRelType.LEFT, trueLiteral, variablesSet);
} else {
builder.join(JoinRelType.INNER, trueLiteral, variablesSet);
}
offset += 2;
builder.push(e.rel);
// fall through
default:
builder.aggregate(builder.groupKey(fields),
builder.literalAgg(true).as("i"));
}
}
String dtAlias = "dt";
if (subQueryIndex != 0) {
dtAlias = "dt" + subQueryIndex;
}
builder.as(dtAlias);
int refOffset = offset;
final List<RexNode> conditions =
Pair.zip(expressionOperands, builder.fields()).stream()
.map(pair -> builder.equals(pair.left, RexUtil.shift(pair.right, refOffset)))
.collect(Collectors.toList());
switch (logic) {
case TRUE:
builder.join(JoinRelType.INNER, builder.and(conditions), variablesSet);
return trueLiteral;
default:
break;
}
// Now the left join
builder.join(JoinRelType.LEFT, builder.and(conditions), variablesSet);
final ImmutableList.Builder<RexNode> operands = ImmutableList.builder();
RexLiteral b = trueLiteral;
switch (logic) {
case TRUE_FALSE_UNKNOWN:
b = unknownLiteral;
// fall through
case UNKNOWN_AS_TRUE:
if (allLiterals) {
// Considers case when right side of IN is empty
// for the case of non-correlated sub-queries
if (variablesSet.isEmpty()) {
operands.add(
builder.isNull(builder.field(dtAlias, "c")),
falseLiteral);
}
operands.add(
builder.equals(builder.field(dtAlias, "cs"), falseLiteral),
b);
} else {
operands.add(
builder.equals(builder.field(ctAlias, "c"), builder.literal(0)),
falseLiteral);
}
break;
default:
break;
}
if (!keyIsNulls.isEmpty()) {
operands.add(builder.or(keyIsNulls), unknownLiteral);
}
if (allLiterals) {
operands.add(builder.isNotNull(builder.field(dtAlias, "cs")),
trueLiteral);
} else {
operands.add(builder.isNotNull(last(builder.fields())),
trueLiteral);
}
if (!allLiterals) {
switch (logic) {
case TRUE_FALSE_UNKNOWN:
case UNKNOWN_AS_TRUE:
operands.add(
builder.lessThan(builder.field(ctAlias, "ck"),
builder.field(ctAlias, "c")),
b);
break;
default:
break;
}
}
operands.add(falseLiteral);
return builder.call(SqlStdOperatorTable.CASE, operands.build());
}