Examples with SqlMatchRecognize org.apache.calcite.sql.SqlMatchRecognize used on opensource projects

Example 1 with SqlMatchRecognize

use of org.apache.calcite.sql.SqlMatchRecognize in project calcite by apache.

the class RelToSqlConverter method visit.

/**
 * @see #dispatch
 */
public Result visit(Match e) {
    final RelNode input = e.getInput();
    final Result x = visitChild(0, input);
    final Context context = matchRecognizeContext(x.qualifiedContext());
    SqlNode tableRef = x.asQueryOrValues();
    final List<SqlNode> partitionSqlList = new ArrayList<>();
    if (e.getPartitionKeys() != null) {
        for (RexNode rex : e.getPartitionKeys()) {
            SqlNode sqlNode = context.toSql(null, rex);
            partitionSqlList.add(sqlNode);
        }
    }
    final SqlNodeList partitionList = new SqlNodeList(partitionSqlList, POS);
    final List<SqlNode> orderBySqlList = new ArrayList<>();
    if (e.getOrderKeys() != null) {
        for (RelFieldCollation fc : e.getOrderKeys().getFieldCollations()) {
            if (fc.nullDirection != RelFieldCollation.NullDirection.UNSPECIFIED) {
                boolean first = fc.nullDirection == RelFieldCollation.NullDirection.FIRST;
                SqlNode nullDirectionNode = dialect.emulateNullDirection(context.field(fc.getFieldIndex()), first, fc.direction.isDescending());
                if (nullDirectionNode != null) {
                    orderBySqlList.add(nullDirectionNode);
                    fc = new RelFieldCollation(fc.getFieldIndex(), fc.getDirection(), RelFieldCollation.NullDirection.UNSPECIFIED);
                }
            }
            orderBySqlList.add(context.toSql(fc));
        }
    }
    final SqlNodeList orderByList = new SqlNodeList(orderBySqlList, SqlParserPos.ZERO);
    final SqlLiteral rowsPerMatch = e.isAllRows() ? SqlMatchRecognize.RowsPerMatchOption.ALL_ROWS.symbol(POS) : SqlMatchRecognize.RowsPerMatchOption.ONE_ROW.symbol(POS);
    final SqlNode after;
    if (e.getAfter() instanceof RexLiteral) {
        SqlMatchRecognize.AfterOption value = (SqlMatchRecognize.AfterOption) ((RexLiteral) e.getAfter()).getValue2();
        after = SqlLiteral.createSymbol(value, POS);
    } else {
        RexCall call = (RexCall) e.getAfter();
        String operand = RexLiteral.stringValue(call.getOperands().get(0));
        after = call.getOperator().createCall(POS, new SqlIdentifier(operand, POS));
    }
    RexNode rexPattern = e.getPattern();
    final SqlNode pattern = context.toSql(null, rexPattern);
    final SqlLiteral strictStart = SqlLiteral.createBoolean(e.isStrictStart(), POS);
    final SqlLiteral strictEnd = SqlLiteral.createBoolean(e.isStrictEnd(), POS);
    RexLiteral rexInterval = (RexLiteral) e.getInterval();
    SqlIntervalLiteral interval = null;
    if (rexInterval != null) {
        interval = (SqlIntervalLiteral) context.toSql(null, rexInterval);
    }
    final SqlNodeList subsetList = new SqlNodeList(POS);
    for (Map.Entry<String, SortedSet<String>> entry : e.getSubsets().entrySet()) {
        SqlNode left = new SqlIdentifier(entry.getKey(), POS);
        List<SqlNode> rhl = Lists.newArrayList();
        for (String right : entry.getValue()) {
            rhl.add(new SqlIdentifier(right, POS));
        }
        subsetList.add(SqlStdOperatorTable.EQUALS.createCall(POS, left, new SqlNodeList(rhl, POS)));
    }
    final SqlNodeList measureList = new SqlNodeList(POS);
    for (Map.Entry<String, RexNode> entry : e.getMeasures().entrySet()) {
        final String alias = entry.getKey();
        final SqlNode sqlNode = context.toSql(null, entry.getValue());
        measureList.add(as(sqlNode, alias));
    }
    final SqlNodeList patternDefList = new SqlNodeList(POS);
    for (Map.Entry<String, RexNode> entry : e.getPatternDefinitions().entrySet()) {
        final String alias = entry.getKey();
        final SqlNode sqlNode = context.toSql(null, entry.getValue());
        patternDefList.add(as(sqlNode, alias));
    }
    final SqlNode matchRecognize = new SqlMatchRecognize(POS, tableRef, pattern, strictStart, strictEnd, patternDefList, measureList, after, subsetList, rowsPerMatch, partitionList, orderByList, interval);
    return result(matchRecognize, Expressions.list(Clause.FROM), e, null);
}

Example 2 with SqlMatchRecognize

use of org.apache.calcite.sql.SqlMatchRecognize in project calcite by apache.

the class SqlToRelConverter method convertMatchRecognize.

protected void convertMatchRecognize(Blackboard bb, SqlCall call) {
    final SqlMatchRecognize matchRecognize = (SqlMatchRecognize) call;
    final SqlValidatorNamespace ns = validator.getNamespace(matchRecognize);
    final SqlValidatorScope scope = validator.getMatchRecognizeScope(matchRecognize);
    final Blackboard matchBb = createBlackboard(scope, null, false);
    final RelDataType rowType = ns.getRowType();
    // convert inner query, could be a table name or a derived table
    SqlNode expr = matchRecognize.getTableRef();
    convertFrom(matchBb, expr);
    final RelNode input = matchBb.root;
    // PARTITION BY
    final SqlNodeList partitionList = matchRecognize.getPartitionList();
    final List<RexNode> partitionKeys = new ArrayList<>();
    for (SqlNode partition : partitionList) {
        RexNode e = matchBb.convertExpression(partition);
        partitionKeys.add(e);
    }
    // ORDER BY
    final SqlNodeList orderList = matchRecognize.getOrderList();
    final List<RelFieldCollation> orderKeys = new ArrayList<>();
    for (SqlNode order : orderList) {
        final RelFieldCollation.Direction direction;
        switch(order.getKind()) {
            case DESCENDING:
                direction = RelFieldCollation.Direction.DESCENDING;
                order = ((SqlCall) order).operand(0);
                break;
            case NULLS_FIRST:
            case NULLS_LAST:
                throw new AssertionError();
            default:
                direction = RelFieldCollation.Direction.ASCENDING;
                break;
        }
        final RelFieldCollation.NullDirection nullDirection = validator.getDefaultNullCollation().last(desc(direction)) ? RelFieldCollation.NullDirection.LAST : RelFieldCollation.NullDirection.FIRST;
        RexNode e = matchBb.convertExpression(order);
        orderKeys.add(new RelFieldCollation(((RexInputRef) e).getIndex(), direction, nullDirection));
    }
    final RelCollation orders = cluster.traitSet().canonize(RelCollations.of(orderKeys));
    // convert pattern
    final Set<String> patternVarsSet = new HashSet<>();
    SqlNode pattern = matchRecognize.getPattern();
    final SqlBasicVisitor<RexNode> patternVarVisitor = new SqlBasicVisitor<RexNode>() {

        @Override
        public RexNode visit(SqlCall call) {
            List<SqlNode> operands = call.getOperandList();
            List<RexNode> newOperands = Lists.newArrayList();
            for (SqlNode node : operands) {
                newOperands.add(node.accept(this));
            }
            return rexBuilder.makeCall(validator.getUnknownType(), call.getOperator(), newOperands);
        }

        @Override
        public RexNode visit(SqlIdentifier id) {
            assert id.isSimple();
            patternVarsSet.add(id.getSimple());
            return rexBuilder.makeLiteral(id.getSimple());
        }

        @Override
        public RexNode visit(SqlLiteral literal) {
            if (literal instanceof SqlNumericLiteral) {
                return rexBuilder.makeExactLiteral(BigDecimal.valueOf(literal.intValue(true)));
            } else {
                return rexBuilder.makeLiteral(literal.booleanValue());
            }
        }
    };
    final RexNode patternNode = pattern.accept(patternVarVisitor);
    SqlLiteral interval = matchRecognize.getInterval();
    RexNode intervalNode = null;
    if (interval != null) {
        intervalNode = matchBb.convertLiteral(interval);
    }
    // convert subset
    final SqlNodeList subsets = matchRecognize.getSubsetList();
    final Map<String, TreeSet<String>> subsetMap = Maps.newHashMap();
    for (SqlNode node : subsets) {
        List<SqlNode> operands = ((SqlCall) node).getOperandList();
        SqlIdentifier left = (SqlIdentifier) operands.get(0);
        patternVarsSet.add(left.getSimple());
        SqlNodeList rights = (SqlNodeList) operands.get(1);
        final TreeSet<String> list = new TreeSet<String>();
        for (SqlNode right : rights) {
            assert right instanceof SqlIdentifier;
            list.add(((SqlIdentifier) right).getSimple());
        }
        subsetMap.put(left.getSimple(), list);
    }
    SqlNode afterMatch = matchRecognize.getAfter();
    if (afterMatch == null) {
        afterMatch = SqlMatchRecognize.AfterOption.SKIP_TO_NEXT_ROW.symbol(SqlParserPos.ZERO);
    }
    final RexNode after;
    if (afterMatch instanceof SqlCall) {
        List<SqlNode> operands = ((SqlCall) afterMatch).getOperandList();
        SqlOperator operator = ((SqlCall) afterMatch).getOperator();
        assert operands.size() == 1;
        SqlIdentifier id = (SqlIdentifier) operands.get(0);
        assert patternVarsSet.contains(id.getSimple()) : id.getSimple() + " not defined in pattern";
        RexNode rex = rexBuilder.makeLiteral(id.getSimple());
        after = rexBuilder.makeCall(validator.getUnknownType(), operator, ImmutableList.of(rex));
    } else {
        after = matchBb.convertExpression(afterMatch);
    }
    matchBb.setPatternVarRef(true);
    // convert measures
    final ImmutableMap.Builder<String, RexNode> measureNodes = ImmutableMap.builder();
    for (SqlNode measure : matchRecognize.getMeasureList()) {
        List<SqlNode> operands = ((SqlCall) measure).getOperandList();
        String alias = ((SqlIdentifier) operands.get(1)).getSimple();
        RexNode rex = matchBb.convertExpression(operands.get(0));
        measureNodes.put(alias, rex);
    }
    // convert definitions
    final ImmutableMap.Builder<String, RexNode> definitionNodes = ImmutableMap.builder();
    for (SqlNode def : matchRecognize.getPatternDefList()) {
        List<SqlNode> operands = ((SqlCall) def).getOperandList();
        String alias = ((SqlIdentifier) operands.get(1)).getSimple();
        RexNode rex = matchBb.convertExpression(operands.get(0));
        definitionNodes.put(alias, rex);
    }
    final SqlLiteral rowsPerMatch = matchRecognize.getRowsPerMatch();
    final boolean allRows = rowsPerMatch != null && rowsPerMatch.getValue() == SqlMatchRecognize.RowsPerMatchOption.ALL_ROWS;
    matchBb.setPatternVarRef(false);
    final RelFactories.MatchFactory factory = RelFactories.DEFAULT_MATCH_FACTORY;
    final RelNode rel = factory.createMatch(input, patternNode, rowType, matchRecognize.getStrictStart().booleanValue(), matchRecognize.getStrictEnd().booleanValue(), definitionNodes.build(), measureNodes.build(), after, subsetMap, allRows, partitionKeys, orders, intervalNode);
    bb.setRoot(rel, false);
}

Example 3 with SqlMatchRecognize

use of org.apache.calcite.sql.SqlMatchRecognize in project flink by apache.

the class SqlValidatorImpl method validateMatchRecognize.

@Override
public void validateMatchRecognize(SqlCall call) {
    final SqlMatchRecognize matchRecognize = (SqlMatchRecognize) call;
    final MatchRecognizeScope scope = (MatchRecognizeScope) getMatchRecognizeScope(matchRecognize);
    final MatchRecognizeNamespace ns = getNamespace(call).unwrap(MatchRecognizeNamespace.class);
    assert ns.rowType == null;
    // rows per match
    final SqlLiteral rowsPerMatch = matchRecognize.getRowsPerMatch();
    final boolean allRows = rowsPerMatch != null && rowsPerMatch.getValue() == SqlMatchRecognize.RowsPerMatchOption.ALL_ROWS;
    final RelDataTypeFactory.Builder typeBuilder = typeFactory.builder();
    // parse PARTITION BY column
    SqlNodeList partitionBy = matchRecognize.getPartitionList();
    if (partitionBy != null) {
        for (SqlNode node : partitionBy) {
            SqlIdentifier identifier = (SqlIdentifier) node;
            identifier.validate(this, scope);
            RelDataType type = deriveType(scope, identifier);
            String name = identifier.names.get(1);
            typeBuilder.add(name, type);
        }
    }
    // parse ORDER BY column
    SqlNodeList orderBy = matchRecognize.getOrderList();
    if (orderBy != null) {
        for (SqlNode node : orderBy) {
            node.validate(this, scope);
            SqlIdentifier identifier;
            if (node instanceof SqlBasicCall) {
                identifier = (SqlIdentifier) ((SqlBasicCall) node).getOperands()[0];
            } else {
                identifier = (SqlIdentifier) node;
            }
            if (allRows) {
                RelDataType type = deriveType(scope, identifier);
                String name = identifier.names.get(1);
                if (!typeBuilder.nameExists(name)) {
                    typeBuilder.add(name, type);
                }
            }
        }
    }
    if (allRows) {
        final SqlValidatorNamespace sqlNs = getNamespace(matchRecognize.getTableRef());
        final RelDataType inputDataType = sqlNs.getRowType();
        for (RelDataTypeField fs : inputDataType.getFieldList()) {
            if (!typeBuilder.nameExists(fs.getName())) {
                typeBuilder.add(fs);
            }
        }
    }
    // retrieve pattern variables used in pattern and subset
    SqlNode pattern = matchRecognize.getPattern();
    PatternVarVisitor visitor = new PatternVarVisitor(scope);
    pattern.accept(visitor);
    SqlLiteral interval = matchRecognize.getInterval();
    if (interval != null) {
        interval.validate(this, scope);
        if (((SqlIntervalLiteral) interval).signum() < 0) {
            throw newValidationError(interval, RESOURCE.intervalMustBeNonNegative(interval.toValue()));
        }
        if (orderBy == null || orderBy.size() == 0) {
            throw newValidationError(interval, RESOURCE.cannotUseWithinWithoutOrderBy());
        }
        SqlNode firstOrderByColumn = orderBy.getList().get(0);
        SqlIdentifier identifier;
        if (firstOrderByColumn instanceof SqlBasicCall) {
            identifier = (SqlIdentifier) ((SqlBasicCall) firstOrderByColumn).getOperands()[0];
        } else {
            identifier = (SqlIdentifier) firstOrderByColumn;
        }
        RelDataType firstOrderByColumnType = deriveType(scope, identifier);
        if (!(firstOrderByColumnType.getSqlTypeName() == SqlTypeName.TIMESTAMP || firstOrderByColumnType.getSqlTypeName() == SqlTypeName.TIMESTAMP_WITH_LOCAL_TIME_ZONE)) {
            throw newValidationError(interval, RESOURCE.firstColumnOfOrderByMustBeTimestamp());
        }
        SqlNode expand = expand(interval, scope);
        RelDataType type = deriveType(scope, expand);
        setValidatedNodeType(interval, type);
    }
    validateDefinitions(matchRecognize, scope);
    SqlNodeList subsets = matchRecognize.getSubsetList();
    if (subsets != null && subsets.size() > 0) {
        for (SqlNode node : subsets) {
            List<SqlNode> operands = ((SqlCall) node).getOperandList();
            String leftString = ((SqlIdentifier) operands.get(0)).getSimple();
            if (scope.getPatternVars().contains(leftString)) {
                throw newValidationError(operands.get(0), RESOURCE.patternVarAlreadyDefined(leftString));
            }
            scope.addPatternVar(leftString);
            for (SqlNode right : (SqlNodeList) operands.get(1)) {
                SqlIdentifier id = (SqlIdentifier) right;
                if (!scope.getPatternVars().contains(id.getSimple())) {
                    throw newValidationError(id, RESOURCE.unknownPattern(id.getSimple()));
                }
                scope.addPatternVar(id.getSimple());
            }
        }
    }
    // validate AFTER ... SKIP TO
    final SqlNode skipTo = matchRecognize.getAfter();
    if (skipTo instanceof SqlCall) {
        final SqlCall skipToCall = (SqlCall) skipTo;
        final SqlIdentifier id = skipToCall.operand(0);
        if (!scope.getPatternVars().contains(id.getSimple())) {
            throw newValidationError(id, RESOURCE.unknownPattern(id.getSimple()));
        }
    }
    List<Map.Entry<String, RelDataType>> measureColumns = validateMeasure(matchRecognize, scope, allRows);
    for (Map.Entry<String, RelDataType> c : measureColumns) {
        if (!typeBuilder.nameExists(c.getKey())) {
            typeBuilder.add(c.getKey(), c.getValue());
        }
    }
    final RelDataType rowType = typeBuilder.build();
    if (matchRecognize.getMeasureList().size() == 0) {
        ns.setType(getNamespace(matchRecognize.getTableRef()).getRowType());
    } else {
        ns.setType(rowType);
    }
}

Example 4 with SqlMatchRecognize

use of org.apache.calcite.sql.SqlMatchRecognize in project calcite by apache.

the class SqlValidatorImpl method validateMatchRecognize.

@Override
public void validateMatchRecognize(SqlCall call) {
    final SqlMatchRecognize matchRecognize = (SqlMatchRecognize) call;
    final MatchRecognizeScope scope = (MatchRecognizeScope) getMatchRecognizeScope(matchRecognize);
    final MatchRecognizeNamespace ns = getNamespace(call).unwrap(MatchRecognizeNamespace.class);
    assert ns.rowType == null;
    // rows per match
    final SqlLiteral rowsPerMatch = matchRecognize.getRowsPerMatch();
    final boolean allRows = rowsPerMatch != null && rowsPerMatch.getValue() == SqlMatchRecognize.RowsPerMatchOption.ALL_ROWS;
    final RelDataTypeFactory.Builder typeBuilder = typeFactory.builder();
    // parse PARTITION BY column
    SqlNodeList partitionBy = matchRecognize.getPartitionList();
    if (partitionBy != null) {
        for (SqlNode node : partitionBy) {
            SqlIdentifier identifier = (SqlIdentifier) node;
            identifier.validate(this, scope);
            RelDataType type = deriveType(scope, identifier);
            String name = identifier.names.get(1);
            typeBuilder.add(name, type);
        }
    }
    // parse ORDER BY column
    SqlNodeList orderBy = matchRecognize.getOrderList();
    if (orderBy != null) {
        for (SqlNode node : orderBy) {
            node.validate(this, scope);
            SqlIdentifier identifier = null;
            if (node instanceof SqlBasicCall) {
                identifier = (SqlIdentifier) ((SqlBasicCall) node).getOperands()[0];
            } else {
                identifier = (SqlIdentifier) node;
            }
            if (allRows) {
                RelDataType type = deriveType(scope, identifier);
                String name = identifier.names.get(1);
                if (!typeBuilder.nameExists(name)) {
                    typeBuilder.add(name, type);
                }
            }
        }
    }
    if (allRows) {
        final SqlValidatorNamespace sqlNs = getNamespace(matchRecognize.getTableRef());
        final RelDataType inputDataType = sqlNs.getRowType();
        for (RelDataTypeField fs : inputDataType.getFieldList()) {
            if (!typeBuilder.nameExists(fs.getName())) {
                typeBuilder.add(fs);
            }
        }
    }
    // retrieve pattern variables used in pattern and subset
    SqlNode pattern = matchRecognize.getPattern();
    PatternVarVisitor visitor = new PatternVarVisitor(scope);
    pattern.accept(visitor);
    SqlLiteral interval = matchRecognize.getInterval();
    if (interval != null) {
        interval.validate(this, scope);
        if (((SqlIntervalLiteral) interval).signum() < 0) {
            throw newValidationError(interval, RESOURCE.intervalMustBeNonNegative(interval.toValue()));
        }
        if (orderBy == null || orderBy.size() == 0) {
            throw newValidationError(interval, RESOURCE.cannotUseWithinWithoutOrderBy());
        }
        SqlNode firstOrderByColumn = orderBy.getList().get(0);
        SqlIdentifier identifier;
        if (firstOrderByColumn instanceof SqlBasicCall) {
            identifier = (SqlIdentifier) ((SqlBasicCall) firstOrderByColumn).getOperands()[0];
        } else {
            identifier = (SqlIdentifier) firstOrderByColumn;
        }
        RelDataType firstOrderByColumnType = deriveType(scope, identifier);
        if (firstOrderByColumnType.getSqlTypeName() != SqlTypeName.TIMESTAMP) {
            throw newValidationError(interval, RESOURCE.firstColumnOfOrderByMustBeTimestamp());
        }
        SqlNode expand = expand(interval, scope);
        RelDataType type = deriveType(scope, expand);
        setValidatedNodeType(interval, type);
    }
    validateDefinitions(matchRecognize, scope);
    SqlNodeList subsets = matchRecognize.getSubsetList();
    if (subsets != null && subsets.size() > 0) {
        for (SqlNode node : subsets) {
            List<SqlNode> operands = ((SqlCall) node).getOperandList();
            String leftString = ((SqlIdentifier) operands.get(0)).getSimple();
            if (scope.getPatternVars().contains(leftString)) {
                throw newValidationError(operands.get(0), RESOURCE.patternVarAlreadyDefined(leftString));
            }
            scope.addPatternVar(leftString);
            for (SqlNode right : (SqlNodeList) operands.get(1)) {
                SqlIdentifier id = (SqlIdentifier) right;
                if (!scope.getPatternVars().contains(id.getSimple())) {
                    throw newValidationError(id, RESOURCE.unknownPattern(id.getSimple()));
                }
                scope.addPatternVar(id.getSimple());
            }
        }
    }
    // validate AFTER ... SKIP TO
    final SqlNode skipTo = matchRecognize.getAfter();
    if (skipTo instanceof SqlCall) {
        final SqlCall skipToCall = (SqlCall) skipTo;
        final SqlIdentifier id = skipToCall.operand(0);
        if (!scope.getPatternVars().contains(id.getSimple())) {
            throw newValidationError(id, RESOURCE.unknownPattern(id.getSimple()));
        }
    }
    List<Map.Entry<String, RelDataType>> measureColumns = validateMeasure(matchRecognize, scope, allRows);
    for (Map.Entry<String, RelDataType> c : measureColumns) {
        if (!typeBuilder.nameExists(c.getKey())) {
            typeBuilder.add(c.getKey(), c.getValue());
        }
    }
    final RelDataType rowType = typeBuilder.build();
    if (matchRecognize.getMeasureList().size() == 0) {
        ns.setType(getNamespace(matchRecognize.getTableRef()).getRowType());
    } else {
        ns.setType(rowType);
    }
}