{"record":{"id":"ddda154f67eab623","repo":"alibaba/canal","slug":"unsupported-for-complex-of-on-condition","errorCode":null,"errorMessage":"Unsupported for complex of on-condition","messagePattern":"Unsupported for complex of on-condition","errorType":"validation","errorClass":"UnsupportedOperationException","httpStatus":null,"severity":"error","filePath":"client-adapter/escore/src/main/java/com/alibaba/otter/canal/client/adapter/es/core/config/SqlParser.java","lineNumber":229,"sourceCode":"    /**\n     * 解析on条件\n     *\n     * @param expr sql expr\n     * @param tableItem 表对象\n     */\n    private static void visitOnCondition(SQLExpr expr, TableItem tableItem) {\n        if (!(expr instanceof SQLBinaryOpExpr)) {\n            throw new UnsupportedOperationException();\n        }\n        SQLBinaryOpExpr sqlBinaryOpExpr = (SQLBinaryOpExpr) expr;\n        if (sqlBinaryOpExpr.getOperator() == BooleanAnd) {\n            visitOnCondition(sqlBinaryOpExpr.getLeft(), tableItem);\n            visitOnCondition(sqlBinaryOpExpr.getRight(), tableItem);\n        } else if (sqlBinaryOpExpr.getOperator() == Equality) {\n            FieldItem leftFieldItem = new FieldItem();\n            visitColumn(sqlBinaryOpExpr.getLeft(), leftFieldItem);\n            if (leftFieldItem.getColumnItems().size() != 1 || leftFieldItem.isMethod() || leftFieldItem.isBinaryOp()) {\n                throw new UnsupportedOperationException(\"Unsupported for complex of on-condition\");\n            }\n            FieldItem rightFieldItem = new FieldItem();\n            visitColumn(sqlBinaryOpExpr.getRight(), rightFieldItem);\n            if (rightFieldItem.getColumnItems().size() != 1 || rightFieldItem.isMethod() || rightFieldItem.isBinaryOp()) {\n                throw new UnsupportedOperationException(\"Unsupported for complex of on-condition\");\n            }\n            tableItem.getRelationFields().add(new RelationFieldsPair(leftFieldItem, rightFieldItem));\n        } else {\n            throw new UnsupportedOperationException(\"Unsupported for complex of on-condition\");\n        }\n    }\n\n    public static MySqlSelectQueryBlock parseSQLSelectQueryBlock(String sql) {\n        if (sql == null || \"\".equals(sql)) {\n            return null;\n        }\n        SQLStatementParser parser = new MySqlStatementParser(sql);\n        SQLSelectStatement statement = (SQLSelectStatement) parser.parseStatement();","sourceCodeStart":211,"sourceCodeEnd":247,"githubUrl":"https://github.com/alibaba/canal/blob/87be50e87686a3e8af08c368d0e1ffd1f59eb04a/client-adapter/escore/src/main/java/com/alibaba/otter/canal/client/adapter/es/core/config/SqlParser.java#L211-L247","documentation":"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.","triggerScenarios":"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.","commonSituations":"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.","solutions":["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.","Pre-compute derived values into a real column upstream so the join key is a plain column reference.","Drop the join and use a flat single-table mapping if the join cannot be simplified.","Verify the alias prefix matches between ON and the table alias declared in FROM."],"exampleFix":"-- before\nSELECT ... FROM t1 a JOIN t2 b ON UPPER(a.id) = b.aid\n\n-- after\nSELECT ... FROM t1 a JOIN t2 b ON a.id = b.aid","handlingStrategy":"validation","validationCode":"// Reject ON conditions with non-column left operands before deploying\nString sql = mapping.getSql();\n// crude heuristic: each '=' in a JOIN ON should have a bare column on each side\nif (sql.toUpperCase().contains(\" JOIN \")) {\n    // parse with Druid and assert each ON equality side is a single column\n    MySqlSelectQueryBlock q = SqlParser.parseSQLSelectQueryBlock(sql);\n    // walk SQLJoinTableSource.getCondition() and verify SQLPropertyExpr/SQLIdentifierExpr on both sides\n}","typeGuard":null,"tryCatchPattern":"try {\n    SqlParser.parse(sql);\n} catch (UnsupportedOperationException e) {\n    if (e.getMessage() != null && e.getMessage().contains(\"on-condition\")) {\n        logger.error(\"JOIN ON condition too complex; simplify to plain col=col [AND col=col]\");\n    }\n    throw e;\n}","preventionTips":["Author JOIN ON as a conjunction of simple 'alias.col = alias.col' equalities.","Avoid functions/expressions on either side of '=' in the ON clause.","Add a build-time test that SqlParser.parse succeeds for every ES mapping SQL."],"tags":["es-adapter","sql-parser","join","on-condition"],"backgroundTag":null,"analyzedSha":"87be50e87686a3e8af08c368d0e1ffd1f59eb04a","analyzedAt":"2026-08-14T04:30:11.918Z","schemaVersion":2},"datasetVersion":"2026-08-14T05:17:29.042Z"}