alibaba/canal · error · UnsupportedOperationException

Unsupported for complex of on-condition

Error message

Unsupported for complex of on-condition

What it means

visitOnCondition only accepts join ON expressions built from '=' (Equality) and AND. This specific throw fires on the LEFT side of an '=' pair: after visitColumn, the left field must contain exactly one plain column and must not be a method call or binary operation. If the left operand is a function/compound expression, the join condition is deemed too complex and unsupported.

Source

Thrown at client-adapter/escore/src/main/java/com/alibaba/otter/canal/client/adapter/es/core/config/SqlParser.java:229

    /**
     * 解析on条件
     *
     * @param expr sql expr
     * @param tableItem 表对象
     */
    private static void visitOnCondition(SQLExpr expr, TableItem tableItem) {
        if (!(expr instanceof SQLBinaryOpExpr)) {
            throw new UnsupportedOperationException();
        }
        SQLBinaryOpExpr sqlBinaryOpExpr = (SQLBinaryOpExpr) expr;
        if (sqlBinaryOpExpr.getOperator() == BooleanAnd) {
            visitOnCondition(sqlBinaryOpExpr.getLeft(), tableItem);
            visitOnCondition(sqlBinaryOpExpr.getRight(), tableItem);
        } else if (sqlBinaryOpExpr.getOperator() == Equality) {
            FieldItem leftFieldItem = new FieldItem();
            visitColumn(sqlBinaryOpExpr.getLeft(), leftFieldItem);
            if (leftFieldItem.getColumnItems().size() != 1 || leftFieldItem.isMethod() || leftFieldItem.isBinaryOp()) {
                throw new UnsupportedOperationException("Unsupported for complex of on-condition");
            }
            FieldItem rightFieldItem = new FieldItem();
            visitColumn(sqlBinaryOpExpr.getRight(), rightFieldItem);
            if (rightFieldItem.getColumnItems().size() != 1 || rightFieldItem.isMethod() || rightFieldItem.isBinaryOp()) {
                throw new UnsupportedOperationException("Unsupported for complex of on-condition");
            }
            tableItem.getRelationFields().add(new RelationFieldsPair(leftFieldItem, rightFieldItem));
        } else {
            throw new UnsupportedOperationException("Unsupported for complex of on-condition");
        }
    }

    public static MySqlSelectQueryBlock parseSQLSelectQueryBlock(String sql) {
        if (sql == null || "".equals(sql)) {
            return null;
        }
        SQLStatementParser parser = new MySqlStatementParser(sql);
        SQLSelectStatement statement = (SQLSelectStatement) parser.parseStatement();

View on GitHub (pinned to 87be50e876)

Solutions

1. Rewrite the ON condition so each side of '=' is a single qualified column (e.g. 'ON a.id = b.aid'), using AND to combine multiple equalities.
2. Pre-compute derived values into a real column upstream so the join key is a plain column reference.
3. Drop the join and use a flat single-table mapping if the join cannot be simplified.
4. Verify the alias prefix matches between ON and the table alias declared in FROM.

Example fix

-- before
SELECT ... FROM t1 a JOIN t2 b ON UPPER(a.id) = b.aid

-- after
SELECT ... FROM t1 a JOIN t2 b ON a.id = b.aid

// Reject ON conditions with non-column left operands before deploying
String sql = mapping.getSql();
// crude heuristic: each '=' in a JOIN ON should have a bare column on each side
if (sql.toUpperCase().contains(" JOIN ")) {
    // parse with Druid and assert each ON equality side is a single column
    MySqlSelectQueryBlock q = SqlParser.parseSQLSelectQueryBlock(sql);
    // walk SQLJoinTableSource.getCondition() and verify SQLPropertyExpr/SQLIdentifierExpr on both sides
}

try {
    SqlParser.parse(sql);
} catch (UnsupportedOperationException e) {
    if (e.getMessage() != null && e.getMessage().contains("on-condition")) {
        logger.error("JOIN ON condition too complex; simplify to plain col=col [AND col=col]");
    }
    throw e;
}

When it happens

Trigger: An ES mapping SQL with a JOIN whose ON clause uses a function or computed expression on the left of '=', e.g. 'ON UPPER(a.id) = b.aid' or 'ON (a.x + 1) = b.y', or where the left operand resolves to zero or multiple columns.

Common situations: Authoring a multi-table ES sync mapping and using a non-trivial expression on the join key; migrating an existing SQL view that used computed join predicates.

Related errors

• Parse sql error
• Not found primary key field in main table

AI-assisted analysis of alibaba/canal@87be50e876 (2026-08-14). Data as JSON: /api/errors/ddda154f67eab623. Report an issue: GitHub.