prestodb/presto · error · SemanticException

TYPE_MISMATCH

TYPE_MISMATCH

Error message

Types are not comparable with NULLIF: ${firstType} vs ${secondType}

What it means

NULLIF requires its two argument types to be coercible to a common super type. When first and second argument types differ and functionAndTypeResolver.getCommonSuperType returns empty, the translator throws TYPE_MISMATCH because the values can never be meaningfully compared.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/sql/relational/SqlToRowExpressionTranslator.java:939

        @Override
        protected RowExpression visitNullIfExpression(NullIfExpression node, Context context)
        {
            RowExpression first = process(node.getFirst(), context);
            RowExpression second = process(node.getSecond(), context);
            Type returnType = getType(node);

            if (!functionAndTypeManager.nullIfSpecialFormEnabled()) {
                // If the first type is unknown, as per presto's NULL_IF semantics we should not infer the type using second argument.
                // Always return a null with unknown type.
                if (first.getType().equals(UnknownType.UNKNOWN)) {
                    return constantNull(UnknownType.UNKNOWN);
                }
                RowExpression firstArgWithoutCast = first;

                if (!second.getType().equals(first.getType())) {
                    Optional<Type> commonType = functionAndTypeResolver.getCommonSuperType(first.getType(), second.getType());
                    if (!commonType.isPresent()) {
                        throw new SemanticException(TYPE_MISMATCH, node, "Types are not comparable with NULLIF: %s vs %s", first.getType(), second.getType());
                    }

                    // cast(first as <common type>)
                    if (!first.getType().equals(commonType.get())) {
                        first = call(
                                getSourceLocation(node),
                                CAST.name(),
                                functionAndTypeResolver.lookupCast(CAST.name(), first.getType(), commonType.get()),
                                commonType.get(), first);
                    }
                    // cast(second as <common type>)
                    if (!second.getType().equals(commonType.get())) {
                        second = call(
                                getSourceLocation(node),
                                CAST.name(),
                                functionAndTypeResolver.lookupCast(CAST.name(), second.getType(), commonType.get()),
                                commonType.get(), second);
                    }

View on GitHub (pinned to 55bb57d202)

Solutions

1. Add an explicit CAST on one argument so both sides share a comparable type.
2. Change one of the arguments so types align (e.g. compare scalar fields of a row, not the row itself).
3. Fix upstream column definitions/schema so both expressions have the same type.
4. If types were recently changed in a migration, update dependent NULLIF expressions accordingly.

Example fix

// before
SELECT NULLIF(row_col, 1)
// after
SELECT NULLIF(row_col.field, 1)

// Ensure both sides of NULLIF resolve to the same type before building SQL
if (!exprA.getType().equals(exprB.getType()) &&
    !metadata.getFunctionAndTypeResolver().getCommonSuperType(exprA.getType(), exprB.getType()).isPresent()) {
    throw new IllegalArgumentException("NULLIF args have no common supertype");
}

boolean nullifComparable(Type a, Type b, FunctionAndTypeResolver r) {
    return a.equals(b) || r.getCommonSuperType(a, b).isPresent();
}

try {
    session.execute(sql);
} catch (SemanticException e) {
    if (e.getCode() == SemanticErrorCode.TYPE_MISMATCH) {
        // add explicit CASTs and retry
    } else throw e;
}

When it happens

Trigger: Calling NULLIF(a, b) where a and b have unrelated types (e.g. a row/array/map type vs a scalar, or two types with no common super type) during expression analysis/translation.

Common situations: Comparing a struct/row with a primitive; comparing incompatible complex types after schema changes; dynamically generated SQL passing mismatched column types.

Related errors

• MISSING_ATTRIBUTE
• Expected column to be a time type but is
• NOT_SUPPORTED
• MISSING_TABLE
• MISSING_ROLE

AI-assisted analysis of prestodb/presto@55bb57d202 (2026-09-04). Data as JSON: /api/errors/3d3924e4529b7c95. Report an issue: GitHub.