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

Example 1 with SqlInOperator

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

the class SqlToRelConverter method substituteSubQuery.

private void substituteSubQuery(Blackboard bb, SubQuery subQuery) {
    final RexNode expr = subQuery.expr;
    if (expr != null) {
        // Already done.
        return;
    }
    final SqlBasicCall call;
    final RelNode rel;
    final SqlNode query;
    final RelOptUtil.Exists converted;
    switch(subQuery.node.getKind()) {
        case CURSOR:
            convertCursor(bb, subQuery);
            return;
        case MULTISET_QUERY_CONSTRUCTOR:
        case MULTISET_VALUE_CONSTRUCTOR:
        case ARRAY_QUERY_CONSTRUCTOR:
            rel = convertMultisets(ImmutableList.of(subQuery.node), bb);
            subQuery.expr = bb.register(rel, JoinRelType.INNER);
            return;
        case IN:
        case NOT_IN:
        case SOME:
        case ALL:
            call = (SqlBasicCall) subQuery.node;
            query = call.operand(1);
            if (!config.isExpand() && !(query instanceof SqlNodeList)) {
                return;
            }
            final SqlNode leftKeyNode = call.operand(0);
            final List<RexNode> leftKeys;
            switch(leftKeyNode.getKind()) {
                case ROW:
                    leftKeys = Lists.newArrayList();
                    for (SqlNode sqlExpr : ((SqlBasicCall) leftKeyNode).getOperandList()) {
                        leftKeys.add(bb.convertExpression(sqlExpr));
                    }
                    break;
                default:
                    leftKeys = ImmutableList.of(bb.convertExpression(leftKeyNode));
            }
            if (query instanceof SqlNodeList) {
                SqlNodeList valueList = (SqlNodeList) query;
                if (!containsNullLiteral(valueList) && valueList.size() < config.getInSubQueryThreshold()) {
                    // We're under the threshold, so convert to OR.
                    subQuery.expr = convertInToOr(bb, leftKeys, valueList, (SqlInOperator) call.getOperator());
                    return;
                }
            // Otherwise, let convertExists translate
            // values list into an inline table for the
            // reference to Q below.
            }
            // Project out the search columns from the left side
            // Q1:
            // "select from emp where emp.deptno in (select col1 from T)"
            // 
            // is converted to
            // 
            // "select from
            // emp inner join (select distinct col1 from T)) q
            // on emp.deptno = q.col1
            // 
            // Q2:
            // "select from emp where emp.deptno not in (Q)"
            // 
            // is converted to
            // 
            // "select from
            // emp left outer join (select distinct col1, TRUE from T) q
            // on emp.deptno = q.col1
            // where emp.deptno <> null
            // and q.indicator <> TRUE"
            // 
            final RelDataType targetRowType = SqlTypeUtil.promoteToRowType(typeFactory, validator.getValidatedNodeType(leftKeyNode), null);
            final boolean notIn = call.getOperator().kind == SqlKind.NOT_IN;
            converted = convertExists(query, RelOptUtil.SubQueryType.IN, subQuery.logic, notIn, targetRowType);
            if (converted.indicator) {
                // Generate
                // emp CROSS JOIN (SELECT COUNT(*) AS c,
                // COUNT(deptno) AS ck FROM dept)
                final RelDataType longType = typeFactory.createSqlType(SqlTypeName.BIGINT);
                // fragile
                final RelNode seek = converted.r.getInput(0);
                final int keyCount = leftKeys.size();
                final List<Integer> args = ImmutableIntList.range(0, keyCount);
                LogicalAggregate aggregate = LogicalAggregate.create(seek, ImmutableBitSet.of(), null, ImmutableList.of(AggregateCall.create(SqlStdOperatorTable.COUNT, false, false, ImmutableList.<Integer>of(), -1, longType, null), AggregateCall.create(SqlStdOperatorTable.COUNT, false, false, args, -1, longType, null)));
                LogicalJoin join = LogicalJoin.create(bb.root, aggregate, rexBuilder.makeLiteral(true), ImmutableSet.<CorrelationId>of(), JoinRelType.INNER);
                bb.setRoot(join, false);
            }
            final RexNode rex = bb.register(converted.r, converted.outerJoin ? JoinRelType.LEFT : JoinRelType.INNER, leftKeys);
            RelOptUtil.Logic logic = subQuery.logic;
            switch(logic) {
                case TRUE_FALSE_UNKNOWN:
                case UNKNOWN_AS_TRUE:
                    if (!converted.indicator) {
                        logic = RelOptUtil.Logic.TRUE_FALSE;
                    }
            }
            subQuery.expr = translateIn(logic, bb.root, rex);
            if (notIn) {
                subQuery.expr = rexBuilder.makeCall(SqlStdOperatorTable.NOT, subQuery.expr);
            }
            return;
        case EXISTS:
            // "select from emp where exists (select a from T)"
            // 
            // is converted to the following if the sub-query is correlated:
            // 
            // "select from emp left outer join (select AGG_TRUE() as indicator
            // from T group by corr_var) q where q.indicator is true"
            // 
            // If there is no correlation, the expression is replaced with a
            // boolean indicating whether the sub-query returned 0 or >= 1 row.
            call = (SqlBasicCall) subQuery.node;
            query = call.operand(0);
            if (!config.isExpand()) {
                return;
            }
            converted = convertExists(query, RelOptUtil.SubQueryType.EXISTS, subQuery.logic, true, null);
            assert !converted.indicator;
            if (convertNonCorrelatedSubQuery(subQuery, bb, converted.r, true)) {
                return;
            }
            subQuery.expr = bb.register(converted.r, JoinRelType.LEFT);
            return;
        case SCALAR_QUERY:
            // to a constant expression.
            if (!config.isExpand()) {
                return;
            }
            call = (SqlBasicCall) subQuery.node;
            query = call.operand(0);
            converted = convertExists(query, RelOptUtil.SubQueryType.SCALAR, subQuery.logic, true, null);
            assert !converted.indicator;
            if (convertNonCorrelatedSubQuery(subQuery, bb, converted.r, false)) {
                return;
            }
            rel = convertToSingleValueSubq(query, converted.r);
            subQuery.expr = bb.register(rel, JoinRelType.LEFT);
            return;
        case SELECT:
            // This is used when converting multiset queries:
            // 
            // select * from unnest(select multiset[deptno] from emps);
            // 
            converted = convertExists(subQuery.node, RelOptUtil.SubQueryType.SCALAR, subQuery.logic, true, null);
            assert !converted.indicator;
            subQuery.expr = bb.register(converted.r, JoinRelType.LEFT);
            return;
        default:
            throw new AssertionError("unexpected kind of sub-query: " + subQuery.node);
    }
}

Example 2 with SqlInOperator

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

the class SqlToRelConverter method pushDownNotForIn.

/**
 * Push down all the NOT logical operators into any IN/NOT IN operators.
 *
 * @param scope Scope where {@code sqlNode} occurs
 * @param sqlNode the root node from which to look for NOT operators
 * @return the transformed SqlNode representation with NOT pushed down.
 */
private static SqlNode pushDownNotForIn(SqlValidatorScope scope, SqlNode sqlNode) {
    if ((sqlNode instanceof SqlCall) && containsInOperator(sqlNode)) {
        SqlCall sqlCall = (SqlCall) sqlNode;
        if ((sqlCall.getOperator() == SqlStdOperatorTable.AND) || (sqlCall.getOperator() == SqlStdOperatorTable.OR)) {
            SqlNode[] sqlOperands = ((SqlBasicCall) sqlCall).operands;
            for (int i = 0; i < sqlOperands.length; i++) {
                sqlOperands[i] = pushDownNotForIn(scope, sqlOperands[i]);
            }
            return reg(scope, sqlNode);
        } else if (sqlCall.getOperator() == SqlStdOperatorTable.NOT) {
            SqlNode childNode = sqlCall.operand(0);
            assert childNode instanceof SqlCall;
            SqlBasicCall childSqlCall = (SqlBasicCall) childNode;
            if (childSqlCall.getOperator() == SqlStdOperatorTable.AND) {
                SqlNode[] andOperands = childSqlCall.getOperands();
                SqlNode[] orOperands = new SqlNode[andOperands.length];
                for (int i = 0; i < orOperands.length; i++) {
                    orOperands[i] = reg(scope, SqlStdOperatorTable.NOT.createCall(SqlParserPos.ZERO, andOperands[i]));
                }
                for (int i = 0; i < orOperands.length; i++) {
                    orOperands[i] = pushDownNotForIn(scope, orOperands[i]);
                }
                return reg(scope, SqlStdOperatorTable.OR.createCall(SqlParserPos.ZERO, orOperands[0], orOperands[1]));
            } else if (childSqlCall.getOperator() == SqlStdOperatorTable.OR) {
                SqlNode[] orOperands = childSqlCall.getOperands();
                SqlNode[] andOperands = new SqlNode[orOperands.length];
                for (int i = 0; i < andOperands.length; i++) {
                    andOperands[i] = reg(scope, SqlStdOperatorTable.NOT.createCall(SqlParserPos.ZERO, orOperands[i]));
                }
                for (int i = 0; i < andOperands.length; i++) {
                    andOperands[i] = pushDownNotForIn(scope, andOperands[i]);
                }
                return reg(scope, SqlStdOperatorTable.AND.createCall(SqlParserPos.ZERO, andOperands[0], andOperands[1]));
            } else if (childSqlCall.getOperator() == SqlStdOperatorTable.NOT) {
                SqlNode[] notOperands = childSqlCall.getOperands();
                assert notOperands.length == 1;
                return pushDownNotForIn(scope, notOperands[0]);
            } else if (childSqlCall.getOperator() instanceof SqlInOperator) {
                SqlNode[] inOperands = childSqlCall.getOperands();
                SqlInOperator inOp = (SqlInOperator) childSqlCall.getOperator();
                if (inOp.kind == SqlKind.NOT_IN) {
                    return reg(scope, SqlStdOperatorTable.IN.createCall(SqlParserPos.ZERO, inOperands[0], inOperands[1]));
                } else {
                    return reg(scope, SqlStdOperatorTable.NOT_IN.createCall(SqlParserPos.ZERO, inOperands[0], inOperands[1]));
                }
            } else {
                // (only considering AND, OR, NOT)
                return sqlNode;
            }
        } else {
            // (only considering AND, OR, NOT)
            return sqlNode;
        }
    } else {
        // tree rooted at sqlNode does not contain inOperator
        return sqlNode;
    }
}