Examples with CoalesceExpression io.trino.sql.tree.CoalesceExpression used on opensource projects

Example 1 with CoalesceExpression

use of io.trino.sql.tree.CoalesceExpression in project trino by trinodb.

the class TransformExistsApplyToCorrelatedJoin method rewriteToNonDefaultAggregation.

private Optional<PlanNode> rewriteToNonDefaultAggregation(ApplyNode applyNode, Context context) {
    checkState(applyNode.getSubquery().getOutputSymbols().isEmpty(), "Expected subquery output symbols to be pruned");
    Symbol subqueryTrue = context.getSymbolAllocator().newSymbol("subqueryTrue", BOOLEAN);
    PlanNode subquery = new ProjectNode(context.getIdAllocator().getNextId(), new LimitNode(context.getIdAllocator().getNextId(), applyNode.getSubquery(), 1L, false), Assignments.of(subqueryTrue, TRUE_LITERAL));
    PlanNodeDecorrelator decorrelator = new PlanNodeDecorrelator(plannerContext, context.getSymbolAllocator(), context.getLookup());
    if (decorrelator.decorrelateFilters(subquery, applyNode.getCorrelation()).isEmpty()) {
        return Optional.empty();
    }
    Symbol exists = getOnlyElement(applyNode.getSubqueryAssignments().getSymbols());
    Assignments.Builder assignments = Assignments.builder().putIdentities(applyNode.getInput().getOutputSymbols()).put(exists, new CoalesceExpression(ImmutableList.of(subqueryTrue.toSymbolReference(), BooleanLiteral.FALSE_LITERAL)));
    return Optional.of(new ProjectNode(context.getIdAllocator().getNextId(), new CorrelatedJoinNode(applyNode.getId(), applyNode.getInput(), subquery, applyNode.getCorrelation(), LEFT, TRUE_LITERAL, applyNode.getOriginSubquery()), assignments.build()));
}

Example 2 with CoalesceExpression

use of io.trino.sql.tree.CoalesceExpression in project trino by trinodb.

the class LogicalPlanner method noTruncationCast.

/*
    According to the standard, for the purpose of store assignment (INSERT),
    no non-space characters of a character string, and no non-zero octets
    of a binary string must be lost when the inserted value is truncated to
    fit in the target column type.
    The following method returns a cast from source type to target type
    with a guarantee of no illegal truncation.
    TODO Once BINARY and parametric VARBINARY types are supported, they should be handled here.
    TODO This workaround is insufficient to handle structural types
     */
private Expression noTruncationCast(Expression expression, Type fromType, Type toType) {
    if (fromType instanceof UnknownType || (!(toType instanceof VarcharType) && !(toType instanceof CharType))) {
        return new Cast(expression, toSqlType(toType));
    }
    int targetLength;
    if (toType instanceof VarcharType) {
        if (((VarcharType) toType).isUnbounded()) {
            return new Cast(expression, toSqlType(toType));
        }
        targetLength = ((VarcharType) toType).getBoundedLength();
    } else {
        targetLength = ((CharType) toType).getLength();
    }
    checkState(fromType instanceof VarcharType || fromType instanceof CharType, "inserting non-character value to column of character type");
    ResolvedFunction spaceTrimmedLength = metadata.resolveFunction(session, QualifiedName.of("$space_trimmed_length"), fromTypes(VARCHAR));
    ResolvedFunction fail = metadata.resolveFunction(session, QualifiedName.of("fail"), fromTypes(VARCHAR));
    return new IfExpression(// check if the trimmed value fits in the target type
    new ComparisonExpression(GREATER_THAN_OR_EQUAL, new GenericLiteral("BIGINT", Integer.toString(targetLength)), new CoalesceExpression(new FunctionCall(spaceTrimmedLength.toQualifiedName(), ImmutableList.of(new Cast(expression, toSqlType(VARCHAR)))), new GenericLiteral("BIGINT", "0"))), new Cast(expression, toSqlType(toType)), new Cast(new FunctionCall(fail.toQualifiedName(), ImmutableList.of(new Cast(new StringLiteral(format("Cannot truncate non-space characters when casting from %s to %s on INSERT", fromType.getDisplayName(), toType.getDisplayName())), toSqlType(VARCHAR)))), toSqlType(toType)));
}

Example 3 with CoalesceExpression

use of io.trino.sql.tree.CoalesceExpression in project trino by trinodb.

the class TestSqlParser method testCoalesce.

@Test
public void testCoalesce() {
    assertInvalidExpression("coalesce()", "The 'coalesce' function must have at least two arguments");
    assertInvalidExpression("coalesce(5)", "The 'coalesce' function must have at least two arguments");
    assertInvalidExpression("coalesce(1, 2) filter (where true)", "FILTER not valid for 'coalesce' function");
    assertInvalidExpression("coalesce(1, 2) OVER ()", "OVER clause not valid for 'coalesce' function");
    assertExpression("coalesce(13, 42)", new CoalesceExpression(new LongLiteral("13"), new LongLiteral("42")));
    assertExpression("coalesce(6, 7, 8)", new CoalesceExpression(new LongLiteral("6"), new LongLiteral("7"), new LongLiteral("8")));
    assertExpression("coalesce(13, null)", new CoalesceExpression(new LongLiteral("13"), new NullLiteral()));
    assertExpression("coalesce(null, 13)", new CoalesceExpression(new NullLiteral(), new LongLiteral("13")));
    assertExpression("coalesce(null, null)", new CoalesceExpression(new NullLiteral(), new NullLiteral()));
}

Example 4 with CoalesceExpression

use of io.trino.sql.tree.CoalesceExpression in project trino by trinodb.

the class PushAggregationThroughOuterJoin method coalesceWithNullAggregation.

// When the aggregation is done after the join, there will be a null value that gets aggregated over
// where rows did not exist in the inner table.  For some aggregate functions, such as count, the result
// of an aggregation over a single null row is one or zero rather than null. In order to ensure correct results,
// we add a coalesce function with the output of the new outer join and the aggregation performed over a single
// null row.
private Optional<PlanNode> coalesceWithNullAggregation(AggregationNode aggregationNode, PlanNode outerJoin, SymbolAllocator symbolAllocator, PlanNodeIdAllocator idAllocator) {
    // Create an aggregation node over a row of nulls.
    MappedAggregationInfo aggregationOverNullInfo = createAggregationOverNull(aggregationNode, symbolAllocator, idAllocator);
    AggregationNode aggregationOverNull = aggregationOverNullInfo.getAggregation();
    Map<Symbol, Symbol> sourceAggregationToOverNullMapping = aggregationOverNullInfo.getSymbolMapping();
    // Do a cross join with the aggregation over null
    JoinNode crossJoin = new JoinNode(idAllocator.getNextId(), JoinNode.Type.INNER, outerJoin, aggregationOverNull, ImmutableList.of(), outerJoin.getOutputSymbols(), aggregationOverNull.getOutputSymbols(), false, Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty(), Optional.empty(), ImmutableMap.of(), Optional.empty());
    // Add coalesce expressions for all aggregation functions
    Assignments.Builder assignmentsBuilder = Assignments.builder();
    for (Symbol symbol : outerJoin.getOutputSymbols()) {
        if (aggregationNode.getAggregations().containsKey(symbol)) {
            assignmentsBuilder.put(symbol, new CoalesceExpression(symbol.toSymbolReference(), sourceAggregationToOverNullMapping.get(symbol).toSymbolReference()));
        } else {
            assignmentsBuilder.put(symbol, symbol.toSymbolReference());
        }
    }
    return Optional.of(new ProjectNode(idAllocator.getNextId(), crossJoin, assignmentsBuilder.build()));
}

Example 5 with CoalesceExpression

use of io.trino.sql.tree.CoalesceExpression in project trino by trinodb.

the class HashGenerationOptimizer method getHashExpression.

public static Optional<Expression> getHashExpression(Session session, Metadata metadata, SymbolAllocator symbolAllocator, List<Symbol> symbols) {
    if (symbols.isEmpty()) {
        return Optional.empty();
    }
    Expression result = new GenericLiteral(StandardTypes.BIGINT, String.valueOf(INITIAL_HASH_VALUE));
    for (Symbol symbol : symbols) {
        Expression hashField = FunctionCallBuilder.resolve(session, metadata).setName(QualifiedName.of(HASH_CODE)).addArgument(symbolAllocator.getTypes().get(symbol), new SymbolReference(symbol.getName())).build();
        hashField = new CoalesceExpression(hashField, new LongLiteral(String.valueOf(NULL_HASH_CODE)));
        result = FunctionCallBuilder.resolve(session, metadata).setName(QualifiedName.of("combine_hash")).addArgument(BIGINT, result).addArgument(BIGINT, hashField).build();
    }
    return Optional.of(result);
}