{"record":{"id":"5a81ff24b6f09bb2","repo":"MyCATApache/Mycat-Server","slug":"in-case-of-slice-table-sql-have-different-rules-th","errorCode":null,"errorMessage":"In case of slice table,sql have different rules,the relationship condition is not supported.","messagePattern":"In case of slice table,sql have different rules,the relationship condition is not supported\\.","errorType":"exception","errorClass":"SQLSyntaxErrorException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java","lineNumber":248,"sourceCode":"\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);\n\t\t\t\t\tthrow new SQLSyntaxErrorException(err);\n\t\t\t\t}else{\n\t\t\t\t\tSQLSelectQuery sqlSelectQuery = sqlselect.getQuery();\n\t\t\t\t\tif(((MySqlSelectQueryBlock)sqlSelectQuery).getFrom() instanceof SQLExprTableSource) {\n\t\t\t\t\t\trrs.setCacheAble(false);\n\t\t\t\t\t\trrs.setFinishedRoute(true);\n\t\t\t\t\t\trrsResult = middlerResultRoute(schema,charset,sqlselect,sqlType,statement,sc);\n\t\t\t\t\t}\n\t\t\t\t}\n\t\t\t}else if(subQuerySize >=2){\n\t\t\t\tString err = \"In case of slice table,sql has different rules,currently only one subQuery is supported.\";\n\t\t\t\tLOGGER.error(err);\n\t\t\t\tthrow new SQLSyntaxErrorException(err);\n\t\t\t}\n\t\t}\n\t\treturn rrsResult;\n\t}\n\n\t// 批量update,delete路由方法","sourceCodeStart":230,"sourceCodeEnd":266,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java#L230-L266","documentation":"When a query contains exactly one subquery and a relationship (correlation) condition exists between the inner and outer query, Mycat's middler-result routing cannot rewrite and execute the SQL correctly, so it throws. The middleware currently supports only uncorrelated single subqueries for the subquery-rewrite path.","triggerScenarios":"A correlated subquery like 'select * from t1 where exists (select 1 from t2 where t2.id = t1.id)' routed via the non-direct branch (subQuerySize==1 and relationships non-empty) in routeNormalSqlWithAST0.","commonSituations":"Correlated EXISTS/IN subqueries written for monolithic MySQL; ORMs emitting correlation predicates; queries where both tables are sharded with different rules.","solutions":["Rewrite the correlated subquery as an uncorrelated one (e.g. derive the inner result independently and use an IN list).","Replace the subquery with an application-side two-step query: fetch inner values first, then query the outer table with a literal IN list.","Use a JOIN on the sharding key instead of a correlated subquery.","Route the query directly to the backend (direct DB or a global-table design) if correlation is unavoidable."],"exampleFix":"// before\nSELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip = 1);\n// after\nSELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);","handlingStrategy":"validation","validationCode":"// detect correlated subqueries (inner WHERE references an outer alias)\njava.util.regex.Pattern p = java.util.regex.Pattern.compile(\"(?is)select.*from\\s+(\\w+)\\s+\\w+.*exists\\s*\\(.*\\1\\.\");\nif (p.matcher(sql).find()) throw new IllegalArgumentException(\"Correlated subquery unsupported; rewrite as IN\");","typeGuard":null,"tryCatchPattern":"try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {\n    if (e.getMessage().contains(\"relationship condition is not supported\")) {\n        // two-step: fetch subquery values, then outer query with IN list\n    } else throw e;\n}","preventionTips":["Prefer uncorrelated IN subqueries over correlated EXISTS.","Pre-fetch inner results in application code for shard-key pruning.","Avoid joining outer and inner queries on different rule columns.","Keep both tables sharded on the same key when correlating."],"tags":["sql","subquery","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"}