prestodb/presto · error · PrestoException

INVALID_ARGUMENTS

INVALID_ARGUMENTS

Error message

Type %s does not allow ordering

What it means

The BETWEEN operator specialized for DISTINCT types requires the underlying type to be orderable. During specialize, if the bound type variable T is a DistinctType whose base type does not support ordering, Presto throws INVALID_ARGUMENTS stating the type does not allow ordering. This happens at query planning/specialization time when the operator is resolved against a non-orderable type.

Source

Thrown at presto-main-base/src/main/java/com/facebook/presto/operator/scalar/distinct/DistinctTypeBetweenOperator.java:58

        extends SqlOperator
{
    public static final DistinctTypeBetweenOperator DISTINCT_TYPE_BETWEEN_OPERATOR = new DistinctTypeBetweenOperator();

    private DistinctTypeBetweenOperator()
    {
        super(BETWEEN,
                ImmutableList.of(withVariadicBound("T", DISTINCT_TYPE)),
                ImmutableList.of(),
                parseTypeSignature(BOOLEAN),
                ImmutableList.of(parseTypeSignature("T"), parseTypeSignature("T"), parseTypeSignature("T")));
    }

    @Override
    public BuiltInScalarFunctionImplementation specialize(BoundVariables boundVariables, int arity, FunctionAndTypeManager functionAndTypeManager)
    {
        DistinctType type = (DistinctType) boundVariables.getTypeVariable("T");
        if (!type.isOrderable()) {
            throw new PrestoException(INVALID_ARGUMENTS, format("Type %s does not allow ordering", type.getDisplayName()));
        }
        Type baseType = type.getBaseType();
        FunctionHandle functionHandle = functionAndTypeManager.resolveOperator(BETWEEN, fromTypes(baseType, baseType, baseType));

        return new BuiltInScalarFunctionImplementation(
                false,
                ImmutableList.of(valueTypeArgumentProperty(RETURN_NULL_ON_NULL), valueTypeArgumentProperty(RETURN_NULL_ON_NULL), valueTypeArgumentProperty(RETURN_NULL_ON_NULL)),
                functionAndTypeManager.getJavaScalarFunctionImplementation(functionHandle).getMethodHandle(),
                Optional.empty());
    }
}

View on GitHub (pinned to 55bb57d202)

Solutions

1. Order only on the base type: cast the distinct values to the base type before BETWEEN (e.g. CAST(x AS baseType) BETWEEN ...).
2. Recreate the DISTINCT type over an orderable base type (e.g. varchar, bigint) instead of a complex one.
3. Rewrite the predicate with explicit equality/range logic if ordering is genuinely undefined.
4. Check type.isOrderable() semantics for the base type in your Presto version.

Example fix

// before
SELECT * FROM t WHERE d_val BETWEEN d_lo AND d_hi; -- d_* non-orderable distinct type
// after
SELECT * FROM t WHERE CAST(d_val AS varchar) BETWEEN CAST(d_lo AS varchar) AND CAST(d_hi AS varchar);

-- only compare when the distinct type is orderable (app-level metadata check)
-- if (!distinctType.isOrderable()) use base-type cast comparisons instead

boolean canUseBetween(DistinctType t) {
    return t != null && t.isOrderable();
}

try { rows = query("SELECT * FROM t WHERE d_val BETWEEN d_lo AND d_hi"); } catch (PrestoException e) { if (e.getErrorCode().getName().equals("INVALID_ARGUMENTS")) { rows = query("SELECT * FROM t WHERE CAST(d_val AS " + baseType + ") BETWEEN CAST(d_lo AS " + baseType + ") AND CAST(d_hi AS " + baseType + ")"); } else { throw e; } }

When it happens

Trigger: Evaluating x BETWEEN a AND b where x, a, b are of a DISTINCT type whose base type is not orderable (e.g. a distinct type over a non-comparable type such as a map or row with non-orderable fields).

Common situations: User-defined DISTINCT types created over complex/non-orderable base types; queries comparing distinct-typed columns after a base-type change; cross-version differences in which types are orderable.

Related errors

• INVALID_ARGUMENTS
• INVALID_ARGUMENTS
• INVALID_ARGUMENTS
• INVALID_ARGUMENTS
• INVALID_CAST_ARGUMENT

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