What causes "unsupported TextOrderByItemSegment" in apache/shardingsphere?

Rewrite the ORDER BY item as an explicit column reference or standard expression (e.g. ORDER BY column name or expression, not raw vendor syntax).

apache/shardingsphere · error · UnsupportedSQLOperationException

unsupported TextOrderByItemSegment

Error message

unsupported TextOrderByItemSegment

What it means

OrderByItemConverterUtils converts ORDER BY items to Calcite SqlNodes for federation. Column, Expression, and Index order-by items have converters; TextOrderByItemSegment — an order-by item preserved as raw text — is explicitly rejected with UnsupportedSQLOperationException, because raw text cannot be translated into the federated plan.

Source

Thrown at kernel/sql-federation/compiler/src/main/java/org/apache/shardingsphere/sqlfederation/compiler/sql/ast/converter/segment/orderby/item/OrderByItemConverterUtils.java:56

    
    /**
     * Convert order by items to SQL node.
     *
     * @param orderByItems order by items
     * @return SQL nodes converted by order by item
     * @throws UnsupportedSQLOperationException unsupported SQL operation exception
     */
    public static Collection<SqlNode> convert(final Collection<OrderByItemSegment> orderByItems) {
        Collection<SqlNode> result = new LinkedList<>();
        for (OrderByItemSegment each : orderByItems) {
            if (each instanceof ColumnOrderByItemSegment) {
                result.add(ColumnOrderByItemConverter.convert((ColumnOrderByItemSegment) each));
            } else if (each instanceof ExpressionOrderByItemSegment) {
                ExpressionOrderByItemConverter.convert((ExpressionOrderByItemSegment) each).ifPresent(result::add);
            } else if (each instanceof IndexOrderByItemSegment) {
                IndexOrderByItemConverter.convert((IndexOrderByItemSegment) each).ifPresent(result::add);
            } else if (each instanceof TextOrderByItemSegment) {
                throw new UnsupportedSQLOperationException("unsupported TextOrderByItemSegment");
            }
        }
        return result;
    }
}

View on GitHub (pinned to e952770a21)

Solutions

Rewrite the ORDER BY item as an explicit column reference or standard expression (e.g. ORDER BY column name or expression, not raw vendor syntax).
Move exotic ordering logic to the application by sorting the returned result set.
Disable federation for the statement so ordering runs on the storage node.

Example fix

-- before
SELECT * FROM t ORDER BY DATE_FORMAT(created, '%Y%m'); -- may parse as text item
-- after
SELECT * FROM t ORDER BY created;

Defensive patterns

Strategy: fallback

Try / catch

try {
    rs = executeFederated(sql);
} catch (final UnsupportedSQLOperationException ex) {
    if (ex.getMessage().contains("TextOrderByItemSegment")) { rs = executeRouted(sqlWithoutExoticOrder); } else { throw ex; }
}

Prevention

Order by explicit columns or standard expressions, not vendor-specific text.
Sort exotic orderings in application code.
Verify ORDER BY items parse to column/expression segments before enabling federation.

When it happens

Trigger: A federated query's ORDER BY contains an item the parser kept as text (e.g. ORDER BY with a non-standard expression, ordinal in an odd position, or dialect-specific ordering syntax) instead of a recognized column/expression/index item.

Common situations: ORDER BY with vendor-specific functions or collation syntax; parser fallback capturing the item as text; queries ported between dialects.

Related errors

AI-assisted analysis of apache/shardingsphere@e952770a21 (2026-08-14). Data as JSON: /api/errors/f2072b0a01613c53. Report an issue: GitHub.