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

Example 6 with SqlFunction

use of org.apache.calcite.sql.SqlFunction in project druid by druid-io.

the class ExpressionsTest method testTruncate.

@Test
public void testTruncate() {
    final SqlFunction truncateFunction = new TruncateOperatorConversion().calciteOperator();
    testHelper.testExpression(truncateFunction, testHelper.makeInputRef("a"), DruidExpression.ofExpression(ColumnType.LONG, (args) -> "(cast(cast(" + args.get(0).getExpression() + " * 1,'long'),'double') / 1)", ImmutableList.of(DruidExpression.ofColumn(ColumnType.LONG, "a"))), 10.0);
    testHelper.testExpression(truncateFunction, testHelper.makeInputRef("x"), DruidExpression.ofExpression(ColumnType.FLOAT, (args) -> "(cast(cast(" + args.get(0).getExpression() + " * 1,'long'),'double') / 1)", ImmutableList.of(DruidExpression.ofColumn(ColumnType.FLOAT, "x"))), 2.0);
    testHelper.testExpression(truncateFunction, testHelper.makeInputRef("y"), DruidExpression.ofExpression(ColumnType.LONG, (args) -> "(cast(cast(" + args.get(0).getExpression() + " * 1,'long'),'double') / 1)", ImmutableList.of(DruidExpression.ofColumn(ColumnType.LONG, "y"))), 3.0);
    testHelper.testExpression(truncateFunction, testHelper.makeInputRef("z"), DruidExpression.ofExpression(ColumnType.FLOAT, (args) -> "(cast(cast(" + args.get(0).getExpression() + " * 1,'long'),'double') / 1)", ImmutableList.of(DruidExpression.ofColumn(ColumnType.FLOAT, "z"))), -2.0);
    testHelper.testExpressionString(truncateFunction, ImmutableList.of(testHelper.makeInputRef("x"), testHelper.makeLiteral(1)), DruidExpression.ofExpression(ColumnType.FLOAT, (args) -> "(cast(cast(" + args.get(0).getExpression() + " * 10.0,'long'),'double') / 10.0)", ImmutableList.of(DruidExpression.ofColumn(ColumnType.FLOAT, "x"), DruidExpression.ofLiteral(ColumnType.LONG, DruidExpression.numberLiteral(1)))), 2.2);
    testHelper.testExpressionString(truncateFunction, ImmutableList.of(testHelper.makeInputRef("z"), testHelper.makeLiteral(1)), DruidExpression.ofExpression(ColumnType.FLOAT, (args) -> "(cast(cast(" + args.get(0).getExpression() + " * 10.0,'long'),'double') / 10.0)", ImmutableList.of(DruidExpression.ofColumn(ColumnType.FLOAT, "z"), DruidExpression.ofLiteral(ColumnType.LONG, DruidExpression.numberLiteral(1)))), -2.2);
    testHelper.testExpressionString(truncateFunction, ImmutableList.of(testHelper.makeInputRef("b"), testHelper.makeLiteral(-1)), DruidExpression.ofExpression(ColumnType.LONG, (args) -> "(cast(cast(" + args.get(0).getExpression() + " * 0.1,'long'),'double') / 0.1)", ImmutableList.of(DruidExpression.ofColumn(ColumnType.LONG, "b"), DruidExpression.ofLiteral(ColumnType.LONG, DruidExpression.numberLiteral(-1)))), 20.0);
    testHelper.testExpressionString(truncateFunction, ImmutableList.of(testHelper.makeInputRef("z"), testHelper.makeLiteral(-1)), DruidExpression.ofExpression(ColumnType.FLOAT, (args) -> "(cast(cast(" + args.get(0).getExpression() + " * 0.1,'long'),'double') / 0.1)", ImmutableList.of(DruidExpression.ofColumn(ColumnType.FLOAT, "z"), DruidExpression.ofLiteral(ColumnType.LONG, DruidExpression.numberLiteral(-1)))), 0.0);
}

Example 7 with SqlFunction

use of org.apache.calcite.sql.SqlFunction in project druid by druid-io.

the class ExpressionsTest method testRoundWithInvalidSecondArgument.

@Test
public void testRoundWithInvalidSecondArgument() {
    final SqlFunction roundFunction = new RoundOperatorConversion().calciteOperator();
    expectException(IAE.class, "The second argument to the function[round] should be integer type but got the type: STRING");
    testHelper.testExpressionString(roundFunction, ImmutableList.of(testHelper.makeInputRef("x"), testHelper.makeLiteral("foo")), DruidExpression.ofExpression(ColumnType.FLOAT, DruidExpression.functionCall("round"), ImmutableList.of(DruidExpression.ofColumn(ColumnType.FLOAT, "x"), DruidExpression.ofStringLiteral("foo"))), "IAE Exception");
}

Example 8 with SqlFunction

use of org.apache.calcite.sql.SqlFunction in project hazelcast by hazelcast.

the class NamedOperandCheckerProgram method check.

public boolean check(HazelcastCallBinding callBinding, boolean throwOnFailure) {
    boolean res = true;
    SqlCall call = callBinding.getCall();
    SqlFunction operator = (SqlFunction) call.getOperator();
    for (int i = 0; i < call.operandCount(); i++) {
        SqlNode operand = call.operand(i);
        assert operand.getKind() == SqlKind.ARGUMENT_ASSIGNMENT;
        SqlIdentifier id = ((SqlCall) operand).operand(1);
        OperandChecker checker = findOperandChecker(id, operator);
        res &= checker.check(callBinding, false, i);
    }
    if (!res && throwOnFailure) {
        throw callBinding.newValidationSignatureError();
    }
    return res;
}

Example 9 with SqlFunction

use of org.apache.calcite.sql.SqlFunction in project hive by apache.

the class HiveSubQueryRemoveRule method rewriteInExists.

private RexNode rewriteInExists(RexSubQuery e, Set<CorrelationId> variablesSet, RelOptUtil.Logic logic, RelBuilder builder, int offset, boolean isCorrScalarAgg) {
    // 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)
    // 
    // 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 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 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 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 e
    // inner join (select distinct deptno from emp) as dt
    // on e.deptno = dt.deptno
    // 
    final List<RexNode> fields = new ArrayList<>();
    if (e.getKind() == SqlKind.IN) {
        builder.push(e.rel);
        fields.addAll(builder.fields());
        // will produce wrong results (because we further rewrite such queries into JOIN)
        if (isCorrScalarAgg) {
            // returns single row/column
            builder.aggregate(builder.groupKey(), builder.count(false, "cnt_in"));
            if (!variablesSet.isEmpty()) {
                builder.join(JoinRelType.LEFT, builder.literal(true), variablesSet);
            } else {
                builder.join(JoinRelType.INNER, builder.literal(true), variablesSet);
            }
            SqlFunction inCountCheck = new SqlFunction("sq_count_check", SqlKind.OTHER_FUNCTION, ReturnTypes.BOOLEAN, InferTypes.RETURN_TYPE, OperandTypes.NUMERIC, SqlFunctionCategory.USER_DEFINED_FUNCTION);
            // we create FILTER (sq_count_check(count())) instead of PROJECT
            // because RelFieldTrimmer ends up getting rid of Project
            // since it is not used further up the tree.
            // sq_count_check returns true when subquery returns single row, else it fails
            builder.filter(// true here indicates that sq_count_check is for IN/NOT IN subqueries
            builder.call(inCountCheck, builder.field("cnt_in"), builder.literal(true)));
            offset = offset + 1;
            builder.push(e.rel);
        }
    } else if (e.getKind() == SqlKind.EXISTS && !variablesSet.isEmpty()) {
        // Query has 'exists' and correlation:
        // select * from web_sales ws1
        // where exists (select 1 from web_sales ws2 where ws1.ws_order_number = ws2.ws_order_number limit 1);
        // 
        // HiveRelDecorrelator will replace LogicalCorrelate with a SemiJoin. Hence the right hand side won't be
        // evaluated for every row coming from left and SortLimit cuts the right result set incorrectly. (HIVE-24199)
        builder.push(e.rel.accept(new HiveSortLimitRemover()));
    } else {
        // Query may has exists but no correlation
        // select * from web_sales ws1
        // where exists (select 1 from web_sales ws2 where ws2.ws_order_number = 2 limit 1);
        builder.push(e.rel);
    }
    boolean isCandidateForAntiJoin = false;
    // 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;
                if (conf.getBoolVar(HiveConf.ConfVars.HIVE_CONVERT_ANTI_JOIN)) {
                    // TODO : As of now anti join is first converted to left outer join
                    // and then converted to anti join.
                    // logic = RelOptUtil.Logic.FALSE;
                    isCandidateForAntiJoin = true;
                }
                break;
            }
            builder.aggregate(builder.groupKey(), builder.count(false, "c"), builder.aggregateCall(SqlStdOperatorTable.COUNT, false, null, "ck", builder.fields()));
            builder.as("ct");
            if (!variablesSet.isEmpty()) {
                // builder.join(JoinRelType.INNER, builder.literal(true), variablesSet);
                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
    String trueLiteral = "literalTrue";
    switch(logic) {
        case TRUE:
        case FALSE:
            if (fields.isEmpty()) {
                if (logic == RelOptUtil.Logic.TRUE) {
                    builder.project(builder.alias(builder.literal(true), trueLiteral));
                } else {
                    builder.project(builder.alias(builder.literal(false), "literalFalse"));
                }
                if (!variablesSet.isEmpty() && (e.getKind() == SqlKind.EXISTS || e.getKind() == SqlKind.IN)) {
                    // since this is rewritting into semijoin
                    break;
                } else {
                    builder.aggregate(builder.groupKey(0));
                }
            } else {
                if (!variablesSet.isEmpty() && (e.getKind() == SqlKind.EXISTS || e.getKind() == SqlKind.IN)) {
                    // since this is rewritting into semijoin
                    break;
                } else {
                    builder.aggregate(builder.groupKey(fields));
                }
            }
            break;
        default:
            fields.add(builder.alias(builder.literal(true), trueLiteral));
            builder.project(fields);
            // later during semi/anti join processing at genMapGroupByForSemijoin.
            if (!isCandidateForAntiJoin || variablesSet.isEmpty()) {
                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.SEMI, builder.and(conditions), variablesSet);
            return builder.literal(true);
        case FALSE:
            builder.join(JoinRelType.ANTI, builder.and(conditions), variablesSet);
            return builder.literal(false);
    }
    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));
            // now that we are using LEFT OUTER JOIN to join inner count, count(*)
            // with outer table, we wouldn't be able to tell if count is zero
            // for inner table since inner join with correlated values will get rid
            // of all values where join cond is not true (i.e where actual inner table
            // will produce zero result). To  handle this case we need to check both
            // count is zero or count is null
            operands.add((builder.isNull(builder.field("ct", "c"))), builder.literal(false));
            break;
    }
    operands.add(builder.isNotNull(builder.field("dt", trueLiteral)), builder.literal(true));
    if (!keyIsNulls.isEmpty()) {
        // Calcite creates null literal with Null type here but
        // because HIVE doesn't support null type it is appropriately typed boolean
        operands.add(builder.or(keyIsNulls), e.rel.getCluster().getRexBuilder().makeNullLiteral(SqlTypeName.BOOLEAN));
    // we are creating filter here so should not be returning NULL.
    // Not sure why Calcite return NULL
    }
    RexNode b = builder.literal(true);
    switch(logic) {
        case TRUE_FALSE_UNKNOWN:
            b = e.rel.getCluster().getRexBuilder().makeNullLiteral(SqlTypeName.BOOLEAN);
        // fall through
        case UNKNOWN_AS_TRUE:
            operands.add(builder.call(SqlStdOperatorTable.LESS_THAN, builder.field("ct", "ck"), builder.field("ct", "c")), b);
            break;
    }
    operands.add(builder.literal(false));
    return builder.call(SqlStdOperatorTable.CASE, operands.build());
}

Example 10 with SqlFunction

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

the class SqlValidatorImpl method handleUnresolvedFunction.

public CalciteException handleUnresolvedFunction(SqlCall call, SqlFunction unresolvedFunction, List<RelDataType> argTypes, List<String> argNames) {
    // For builtins, we can give a better error message
    final List<SqlOperator> overloads = new ArrayList<>();
    opTab.lookupOperatorOverloads(unresolvedFunction.getNameAsId(), null, SqlSyntax.FUNCTION, overloads);
    if (overloads.size() == 1) {
        SqlFunction fun = (SqlFunction) overloads.get(0);
        if ((fun.getSqlIdentifier() == null) && (fun.getSyntax() != SqlSyntax.FUNCTION_ID)) {
            final int expectedArgCount = fun.getOperandCountRange().getMin();
            throw newValidationError(call, RESOURCE.invalidArgCount(call.getOperator().getName(), expectedArgCount));
        }
    }
    AssignableOperandTypeChecker typeChecking = new AssignableOperandTypeChecker(argTypes, argNames);
    String signature = typeChecking.getAllowedSignatures(unresolvedFunction, unresolvedFunction.getName());
    throw newValidationError(call, RESOURCE.validatorUnknownFunction(signature));
}