{"record":{"id":"50d7832f6ecb24a8","repo":"MyCATApache/Mycat-Server","slug":"in-case-of-slice-table-there-is-no-rule-field-in-t","errorCode":null,"errorMessage":"In case of slice table,there is no rule field in the relationship condition!","messagePattern":"In case of slice table,there is no rule field in the relationship condition!","errorType":"exception","errorClass":"SQLSyntaxErrorException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java","lineNumber":229,"sourceCode":"\t\t\t\t\t\trulemap.put(tc.getName(), ruleCfg);\n\t\t\t\t\t\t//如果匹配规则不相同或者分片的datanode不相同则需要走子查询处理\n\t\t\t\t\t\tif(firstRule!=null&&((ruleCfg !=null && !ruleCfg.getRuleAlgorithm().equals(firstRule.getRuleAlgorithm()) )||( !dataNodes.equals(firstDataNodes)))){\n\t\t\t\t\t\t\tdirectRoute = false;\n\t\t\t\t\t\t\tbreak;\n\t\t\t\t\t\t}\n\t\t\t\t\t}\n\t\t\t\t}\n\t\t\t\tindex++;\n\t\t\t}\n\t\t}\n\n\t\tRouteResultset rrsResult = rrs;\n\t\tif(directRoute){ //直接路由\n\t\t\tif(!RouterUtil.isAllGlobalTable(ctx, schemaConf)){\n\t\t\t\tif(rulemap.size()>1&&!checkRuleField(rulemap,visitor)){\n\t\t\t\t\tString err = \"In case of slice table,there is no rule field in the relationship condition!\";\n\t\t\t\t\tLOGGER.error(err);\n\t\t\t\t\tthrow new SQLSyntaxErrorException(err);\n\t\t\t\t}\n\t\t\t}\n\t\t\trrsResult = directRoute(rrs,ctx,schema,druidParser,statement,cachePool);\n\t\t}else{\n\t\t\tint subQuerySize = visitor.getSubQuerys().size();\n\t\t\tif(subQuerySize==0&&ctx.getTables().size()==2){ //两表关联,考虑使用catlet\n\t\t\t\tif(!visitor.getRelationships().isEmpty()){\n\t\t\t\t\trrs.setCacheAble(false);\n\t\t\t\t\trrs.setFinishedRoute(true);\n\t\t\t\t\trrsResult = catletRoute(schema,ctx.getSql(),charset,sc);\n\t\t\t\t}else{\n\t\t\t\t\trrsResult = directRoute(rrs,ctx,schema,druidParser,statement,cachePool);\n\t\t\t\t}\n\t\t\t}else if(subQuerySize==1){     //只涉及一张表的子查询,使用  MiddlerResultHandler 获取中间结果后,改写原有 sql 继续执行 TODO 后期可能会考虑多个子查询的情况.\n\t\t\t\tSQLSelect sqlselect = visitor.getSubQuerys().iterator().next();\n\t\t\t\tif(!visitor.getRelationships().isEmpty()){     // 当 inner query  和 outer  query  有关联条件时,暂不支持\n\t\t\t\t\tString err = \"In case of slice table,sql have different rules,the relationship condition is not supported.\";\n\t\t\t\t\tLOGGER.error(err);","sourceCodeStart":211,"sourceCodeEnd":247,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java#L211-L247","documentation":"For a direct-route join involving multiple sharded (slice) tables, Mycat requires that the relationship/join condition include each table's sharding (rule) field so it can prune datanodes. When rulemap shows more than one table needing a rule and checkRuleField finds the join condition does not constrain the rule columns, routing is aborted because a cartesian scan across all shards would otherwise be required.","triggerScenarios":"A join between two or more sharded tables whose ON clause omits one table's sharding key (e.g. joining on a non-partition column), with directRoute=true and not all tables being global tables.","commonSituations":"Joining a sharded table to another sharded table on a secondary column; schema evolution added a new sharded table without updating join keys; ORM-generated joins that don't know about shard keys.","solutions":["Add the sharding/rule columns of every involved table to the join (or where) condition.","Change one of the tables to a global table so it doesn't require a rule condition.","Redesign the sharding rule so both tables share the same partition column and join on it.","Use a Catlet or application-side join: query each table separately and merge in code."],"exampleFix":"// before\nSELECT * FROM orders o JOIN items i ON i.order_no = o.order_no;\n// after (assuming both shard on customer_id)\nSELECT * FROM orders o JOIN items i ON i.order_no = o.order_no AND i.customer_id = o.customer_id;","handlingStrategy":"validation","validationCode":"// before issuing a join between sharded tables, assert shard keys are in the ON clause\nSet<String> needed = Set.of(\"o.customer_id\", \"i.customer_id\");\nif (needed.stream().anyMatch(k -> !joinSql.toLowerCase().contains(k))) {\n    throw new IllegalArgumentException(\"Join must include rule/sharding columns of all slice tables\");\n}","typeGuard":null,"tryCatchPattern":"try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {\n    if (e.getMessage().contains(\"no rule field in the relationship condition\")) {\n        // fall back to separate per-table queries merged in application\n    } else throw e;\n}","preventionTips":["Always join sharded tables on their partition columns.","Make lookup/dimension tables global tables to avoid rule-field requirements.","Keep sharding key design aligned with common join paths.","Lint SQL for joins between sharded tables missing shard keys."],"tags":["sql","join","sharding"],"backgroundTag":"unsupported-operation","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"}