Examples with SqlQuantifyOperator org.apache.calcite.sql.fun.SqlQuantifyOperator used on opensource projects

Example 1 with SqlQuantifyOperator

use of org.apache.calcite.sql.fun.SqlQuantifyOperator in project calcite by apache.

the class SqlToRelConverter method convertInToOr.

/**
 * Converts "x IN (1, 2, ...)" to "x=1 OR x=2 OR ...".
 *
 * @param leftKeys   LHS
 * @param valuesList RHS
 * @param op         The operator (IN, NOT IN, > SOME, ...)
 * @return converted expression
 */
private RexNode convertInToOr(final Blackboard bb, final List<RexNode> leftKeys, SqlNodeList valuesList, SqlInOperator op) {
    final List<RexNode> comparisons = new ArrayList<>();
    for (SqlNode rightVals : valuesList) {
        RexNode rexComparison;
        final SqlOperator comparisonOp;
        if (op instanceof SqlQuantifyOperator) {
            comparisonOp = RelOptUtil.op(((SqlQuantifyOperator) op).comparisonKind, SqlStdOperatorTable.EQUALS);
        } else {
            comparisonOp = SqlStdOperatorTable.EQUALS;
        }
        if (leftKeys.size() == 1) {
            rexComparison = rexBuilder.makeCall(comparisonOp, leftKeys.get(0), ensureSqlType(leftKeys.get(0).getType(), bb.convertExpression(rightVals)));
        } else {
            assert rightVals instanceof SqlCall;
            final SqlBasicCall call = (SqlBasicCall) rightVals;
            assert (call.getOperator() instanceof SqlRowOperator) && call.operandCount() == leftKeys.size();
            rexComparison = RexUtil.composeConjunction(rexBuilder, Iterables.transform(Pair.zip(leftKeys, call.getOperandList()), new Function<Pair<RexNode, SqlNode>, RexNode>() {

                public RexNode apply(Pair<RexNode, SqlNode> pair) {
                    return rexBuilder.makeCall(comparisonOp, pair.left, ensureSqlType(pair.left.getType(), bb.convertExpression(pair.right)));
                }
            }), false);
        }
        comparisons.add(rexComparison);
    }
    switch(op.kind) {
        case ALL:
            return RexUtil.composeConjunction(rexBuilder, comparisons, true);
        case NOT_IN:
            return rexBuilder.makeCall(SqlStdOperatorTable.NOT, RexUtil.composeDisjunction(rexBuilder, comparisons, true));
        case IN:
        case SOME:
            return RexUtil.composeDisjunction(rexBuilder, comparisons, true);
        default:
            throw new AssertionError();
    }
}

Example 2 with SqlQuantifyOperator

use of org.apache.calcite.sql.fun.SqlQuantifyOperator in project hive by apache.

the class RexNodeExprFactory method convertSubquerySomeAll.

public static RexNode convertSubquerySomeAll(final RelOptCluster cluster, final ASTNode comparisonOp, final SubqueryType subqueryType, final RelNode subqueryRel, final RexNode rexNodeLhs) throws SemanticException {
    SqlQuantifyOperator quantifyOperator = null;
    switch(comparisonOp.getType()) {
        case HiveParser.EQUAL:
            if (subqueryType == SubqueryType.ALL) {
                throwInvalidSubqueryError(comparisonOp);
            }
            quantifyOperator = SqlStdOperatorTable.SOME_EQ;
            break;
        case HiveParser.LESSTHAN:
            quantifyOperator = SqlStdOperatorTable.SOME_LT;
            break;
        case HiveParser.LESSTHANOREQUALTO:
            quantifyOperator = SqlStdOperatorTable.SOME_LE;
            break;
        case HiveParser.GREATERTHAN:
            quantifyOperator = SqlStdOperatorTable.SOME_GT;
            break;
        case HiveParser.GREATERTHANOREQUALTO:
            quantifyOperator = SqlStdOperatorTable.SOME_GE;
            break;
        case HiveParser.NOTEQUAL:
            if (subqueryType == SubqueryType.SOME) {
                throwInvalidSubqueryError(comparisonOp);
            }
            quantifyOperator = SqlStdOperatorTable.SOME_NE;
            break;
        default:
            throw new CalciteSubquerySemanticException(ErrorMsg.INVALID_SUBQUERY_EXPRESSION.getMsg("Invalid operator:" + comparisonOp.toString()));
    }
    if (subqueryType == SubqueryType.ALL) {
        quantifyOperator = SqlStdOperatorTable.some(quantifyOperator.comparisonKind.negateNullSafe());
    }
    RexNode someQuery = getSomeSubquery(cluster, subqueryRel, rexNodeLhs, quantifyOperator);
    if (subqueryType == SubqueryType.ALL) {
        return cluster.getRexBuilder().makeCall(SqlStdOperatorTable.NOT, someQuery);
    }
    return someQuery;
}

Example 3 with SqlQuantifyOperator

use of org.apache.calcite.sql.fun.SqlQuantifyOperator in project hive by apache.

the class HiveSubQueryRemoveRule method rewriteSomeAll.

private RexNode rewriteSomeAll(RexSubQuery e, Set<CorrelationId> variablesSet, RelBuilder builder) {
    final SqlQuantifyOperator op = (SqlQuantifyOperator) e.op;
    // SOME_EQ & SOME_NE should have been rewritten into IN/ NOT IN
    assert (op == SqlStdOperatorTable.SOME_GE || op == SqlStdOperatorTable.SOME_LE || op == SqlStdOperatorTable.SOME_LT || op == SqlStdOperatorTable.SOME_GT);
    if (variablesSet.isEmpty()) {
        // 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(), op.comparisonKind == SqlKind.GREATER_THAN || op.comparisonKind == SqlKind.GREATER_THAN_OR_EQUAL ? builder.min("m", builder.field(0)) : builder.max("m", builder.field(0)), builder.count(false, "c"), builder.count(false, "d", builder.field(0))).as("q").join(JoinRelType.INNER);
        return builder.call(SqlStdOperatorTable.CASE, builder.call(SqlStdOperatorTable.EQUALS, builder.field("q", "c"), builder.literal(0)), builder.literal(false), builder.call(SqlStdOperatorTable.IS_TRUE, builder.call(RelOptUtil.op(op.comparisonKind, null), e.operands.get(0), builder.field("q", "m"))), builder.literal(true), builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("q", "c"), builder.field("q", "d")), e.rel.getCluster().getRexBuilder().makeNullLiteral(SqlTypeName.BOOLEAN), builder.call(RelOptUtil.op(op.comparisonKind, null), e.operands.get(0), builder.field("q", "m")));
    } else {
        // 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 max(deptno) as m, count(*) as c, count(deptno) as d, "trueLiteral" as indicator
        // group by name from emp) as q on e.name = q.name
        subqueryRestriction(e.rel);
        builder.push(e.rel);
        builder.aggregate(builder.groupKey(), op.comparisonKind == SqlKind.GREATER_THAN || op.comparisonKind == SqlKind.GREATER_THAN_OR_EQUAL ? builder.min("m", builder.field(0)) : builder.max("m", builder.field(0)), 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(builder.literal(true), indicator));
        builder.project(parentQueryFields).as("q");
        builder.join(JoinRelType.LEFT, builder.literal(true), variablesSet);
        return builder.call(SqlStdOperatorTable.CASE, builder.call(SqlStdOperatorTable.IS_NULL, builder.field(indicator)), builder.literal(false), builder.call(SqlStdOperatorTable.EQUALS, builder.field("q", "c"), builder.literal(0)), builder.literal(false), builder.call(SqlStdOperatorTable.IS_TRUE, builder.call(RelOptUtil.op(op.comparisonKind, null), e.operands.get(0), builder.field("q", "m"))), builder.literal(true), builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("q", "c"), builder.field("q", "d")), e.rel.getCluster().getRexBuilder().makeNullLiteral(SqlTypeName.BOOLEAN), builder.call(RelOptUtil.op(op.comparisonKind, null), e.operands.get(0), builder.field("q", "m")));
    }
}

Example 4 with SqlQuantifyOperator

use of org.apache.calcite.sql.fun.SqlQuantifyOperator in project calcite by apache.

the class SubQueryRemoveRule method apply.

protected RexNode apply(RexSubQuery e, Set<CorrelationId> variablesSet, RelOptUtil.Logic logic, RelBuilder builder, int inputCount, int offset) {
    switch(e.getKind()) {
        case SCALAR_QUERY:
            builder.push(e.rel);
            final RelMetadataQuery mq = e.rel.getCluster().getMetadataQuery();
            final Boolean unique = mq.areColumnsUnique(builder.peek(), ImmutableBitSet.of());
            if (unique == null || !unique) {
                builder.aggregate(builder.groupKey(), builder.aggregateCall(SqlStdOperatorTable.SINGLE_VALUE, false, false, null, null, builder.field(0)));
            }
            builder.join(JoinRelType.LEFT, builder.literal(true), variablesSet);
            return field(builder, inputCount, offset);
        case SOME:
            // 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;
            builder.push(e.rel).aggregate(builder.groupKey(), op.comparisonKind == SqlKind.GREATER_THAN || op.comparisonKind == SqlKind.GREATER_THAN_OR_EQUAL ? builder.min("m", builder.field(0)) : builder.max("m", builder.field(0)), builder.count(false, "c"), builder.count(false, "d", builder.field(0))).as("q").join(JoinRelType.INNER);
            return builder.call(SqlStdOperatorTable.CASE, builder.call(SqlStdOperatorTable.EQUALS, builder.field("q", "c"), builder.literal(0)), builder.literal(false), builder.call(SqlStdOperatorTable.IS_TRUE, builder.call(RelOptUtil.op(op.comparisonKind, null), e.operands.get(0), builder.field("q", "m"))), builder.literal(true), builder.call(SqlStdOperatorTable.GREATER_THAN, builder.field("q", "c"), builder.field("q", "d")), builder.literal(null), builder.call(RelOptUtil.op(op.comparisonKind, null), e.operands.get(0), builder.field("q", "m")));
        case IN:
        case EXISTS:
            // 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 dt.i is not null then true
            // when e.deptno is null then null
            // 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<>();
            switch(e.getKind()) {
                case IN:
                    fields.addAll(builder.fields());
            }
            // First, the cross join
            switch(logic) {
                case TRUE_FALSE_UNKNOWN:
                case UNKNOWN_AS_TRUE:
                    // null keys we do not need to generate count(*), count(c)
                    if (e.getKind() == SqlKind.EXISTS) {
                        logic = RelOptUtil.Logic.TRUE_FALSE;
                        break;
                    }
                    builder.aggregate(builder.groupKey(), builder.count(false, "c"), builder.aggregateCall(SqlStdOperatorTable.COUNT, false, false, null, "ck", builder.fields()));
                    builder.as("ct");
                    if (!variablesSet.isEmpty()) {
                        builder.join(JoinRelType.LEFT, builder.literal(true), variablesSet);
                    } else {
                        builder.join(JoinRelType.INNER, builder.literal(true), variablesSet);
                    }
                    offset += 2;
                    builder.push(e.rel);
                    break;
            }
            // Now the left join
            switch(logic) {
                case TRUE:
                    if (fields.isEmpty()) {
                        builder.project(builder.alias(builder.literal(true), "i"));
                        builder.aggregate(builder.groupKey(0));
                    } else {
                        builder.aggregate(builder.groupKey(fields));
                    }
                    break;
                default:
                    fields.add(builder.alias(builder.literal(true), "i"));
                    builder.project(fields);
                    builder.distinct();
            }
            builder.as("dt");
            final List<RexNode> conditions = new ArrayList<>();
            for (Pair<RexNode, RexNode> pair : Pair.zip(e.getOperands(), builder.fields())) {
                conditions.add(builder.equals(pair.left, RexUtil.shift(pair.right, offset)));
            }
            switch(logic) {
                case TRUE:
                    builder.join(JoinRelType.INNER, builder.and(conditions), variablesSet);
                    return builder.literal(true);
            }
            builder.join(JoinRelType.LEFT, builder.and(conditions), variablesSet);
            final List<RexNode> keyIsNulls = new ArrayList<>();
            for (RexNode operand : e.getOperands()) {
                if (operand.getType().isNullable()) {
                    keyIsNulls.add(builder.isNull(operand));
                }
            }
            final ImmutableList.Builder<RexNode> operands = ImmutableList.builder();
            switch(logic) {
                case TRUE_FALSE_UNKNOWN:
                case UNKNOWN_AS_TRUE:
                    operands.add(builder.equals(builder.field("ct", "c"), builder.literal(0)), builder.literal(false));
                    break;
            }
            operands.add(builder.isNotNull(Util.last(builder.fields())), builder.literal(true));
            if (!keyIsNulls.isEmpty()) {
                operands.add(builder.or(keyIsNulls), builder.literal(null));
            }
            Boolean b = true;
            switch(logic) {
                case TRUE_FALSE_UNKNOWN:
                    b = null;
                // fall through
                case UNKNOWN_AS_TRUE:
                    operands.add(builder.call(SqlStdOperatorTable.LESS_THAN, builder.field("ct", "ck"), builder.field("ct", "c")), builder.literal(b));
                    break;
            }
            operands.add(builder.literal(false));
            return builder.call(SqlStdOperatorTable.CASE, operands.build());
        default:
            throw new AssertionError(e.getKind());
    }
}