{"record":{"id":"9c032f0660ee13a2","repo":"MyCATApache/Mycat-Server","slug":"invalid-route-in-sql-multi-tables-found-but-datan","errorCode":null,"errorMessage":"invalid route in sql, multi tables found but datanode has no intersection  sql:{sql}","messagePattern":"invalid route in sql, multi tables found but datanode has no intersection  sql:(.+?)","errorType":"exception","errorClass":"SQLNonTransientException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/util/RouterUtil.java","lineNumber":1323,"sourceCode":"\t\t\t}\r\n\t\t}\r\n\r\n\r\n\t\tboolean isFirstAdd = true;\r\n\t\tfor(Map.Entry<String, Set<String>> entry : tablesRouteMap.entrySet()) {\r\n\t\t\tif(entry.getValue() == null || entry.getValue().size() == 0) {\r\n\t\t\t\tthrow new SQLNonTransientException(\"parent key can't find any valid datanode \");\r\n\t\t\t} else {\r\n\t\t\t\tif(isFirstAdd) {\r\n\t\t\t\t\tretNodesSet.addAll(entry.getValue());\r\n\t\t\t\t\tisFirstAdd = false;\r\n\t\t\t\t} else {\r\n\t\t\t\t\tretNodesSet.retainAll(entry.getValue());\r\n\t\t\t\t\tif(retNodesSet.size() == 0) {//两个表的路由无交集\r\n\t\t\t\t\t\tString errMsg = \"invalid route in sql, multi tables found but datanode has no intersection \"\r\n\t\t\t\t\t\t\t\t+ \" sql:\" + ctx.getSql();\r\n\t\t\t\t\t\tLOGGER.warn(errMsg);\r\n\t\t\t\t\t\tthrow new SQLNonTransientException(errMsg);\r\n\t\t\t\t\t}\r\n\t\t\t\t}\r\n\t\t\t}\r\n\t\t}\r\n\r\n\t\tif(retNodesSet != null && retNodesSet.size() > 0) {\r\n\t\t\tString tableName = tables.get(0);\r\n\t\t\tTableConfig tableConfig = schema.getTables().get(tableName.toUpperCase());\r\n\t\t\tif(tableConfig.isDistTable()){\r\n\t\t\t    routeToDistTableNode(schema, rrs, ctx.getSql(), tablesAndConditions, cachePool, isSelect, null);\r\n\t\t\t\treturn rrs;\r\n\t\t\t}\r\n\r\n\t\t\tif(retNodesSet.size() > 1 && isAllGlobalTable(ctx, schema)) {\r\n\t\t\t\t// mulit routes ,not cache route result\r\n\t\t\t\tif (isSelect) {\r\n\t\t\t\t\trrs.setCacheAble(false);\r\n\t\t\t\t\tArrayList<String> retNodeList = new ArrayList<String>(retNodesSet);\r","sourceCodeStart":1305,"sourceCodeEnd":1341,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/util/RouterUtil.java#L1305-L1341","documentation":"Thrown when routing a SQL statement referencing multiple sharded tables whose calculated datanode sets have no intersection. Mycat routes multi-table statements to a single set of datanodes via retainAll(), and an empty intersection means the statement cannot be executed anywhere.","triggerScenarios":"A join or multi-table statement where each table's partition-column conditions route to disjoint datanodes (retNodesSet.size() == 0 after retainAll).","commonSituations":"Joining two sharded tables that are sharded differently (different algorithms or column values landing on different nodes); queries joining tables co-located on different nodes; misaligned sharding configs across related tables.","solutions":["Align sharding rules so related tables are co-located on the same datanodes (same algorithm/column for join keys)","Rewrite the SQL to route to a single datanode (add explicit equality on the partition column of each table matching the same node)","Use Mycat ER-division (childTable) configuration for parent-child join tables","Split the query into multiple single-table queries executed separately"],"exampleFix":"// before: join of two tables sharded to different nodes\nSELECT * FROM orders o JOIN customer c ON o.cid = c.id WHERE c.id = 5;\n// after: also constrain orders' partition column so both route to the same node\nSELECT * FROM orders o JOIN customer c ON o.cid = c.id WHERE c.id = 5 AND o.cid = 5;","handlingStrategy":"validation","validationCode":"// Pre-check intersection of routes for tables in a join\nSet<String> t1 = routeOf(tableA, condA), t2 = routeOf(tableB, condB);\nt1.retainAll(t2);\nif (t1.isEmpty()) throw new IllegalArgumentException(\"join tables not co-located\");","typeGuard":null,"tryCatchPattern":"try { rrs = route(...); } catch (SQLNonTransientException e) { if (e.getMessage().contains(\"no intersection\")) { LOG.warn(\"rewrite join or align sharding rules\"); } throw e; }","preventionTips":["Configure ER joins (childTable) for frequently joined parent-child tables","Use the same partition column and algorithm for joined tables","Avoid joins between tables sharded to disjoint nodes"],"tags":["routing","sharding","join"],"backgroundTag":"invalid-state-transition","analyzedSha":"65f8d8beb752f935752f2a0eec0ab017facab9ef","analyzedAt":"2026-09-11T00:12:21.696Z","contentChangedAt":"2026-09-11T00:12:21.696Z","schemaVersion":2},"datasetVersion":"2026-09-16T04:17:20.429Z"}