{"record":{"id":"31700a78d8c504a6","repo":"MyCATApache/Mycat-Server","slug":"in-case-of-slice-table-sql-has-different-rules-cur","errorCode":null,"errorMessage":"In case of slice table,sql has different rules,currently only one subQuery is supported.","messagePattern":"In case of slice table,sql has different rules,currently only one subQuery is supported\\.","errorType":"exception","errorClass":"SQLSyntaxErrorException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java","lineNumber":260,"sourceCode":"\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路由方法\n\tprivate RouteResultset routeMultiSqlWithAST(SchemaConfig schema, String stmt, RouteResultset rrs, String charset,\n\t\t\tLayerCachePool cachePool, int sqlType, ServerConnection sc) throws SQLNonTransientException {\n\t\tList<RouteResultsetNode> allNodes = new ArrayList<>(64);\n\t\t// 拆分出一个个SQL解析路由\n\t\tString remingSql = stmt;\n\t\tString eachSqlItem = null;\n\t\tdo {\n\t\t\tint index = ParseUtil.findNextBreak(remingSql);\n\t\t\tif (index + 1 < remingSql.length() && !ParseUtil.isEOF(remingSql, index)) {\n\t\t\t\teachSqlItem = remingSql.substring(0, index);\n\t\t\t\tremingSql = remingSql.substring(index + 1, remingSql.length());\n\t\t\t\tRouteResultset rrsTemp = new RouteResultset(eachSqlItem, sqlType);","sourceCodeStart":242,"sourceCodeEnd":278,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java#L242-L278","documentation":"Mycat's subquery routing path only handles one subquery per statement. When visitor.getSubQuerys() reports two or more SQLSelect subqueries in the non-direct-route branch, the router throws because it cannot rewrite multiple subqueries into staged executions (the code notes multi-subquery support as future work).","triggerScenarios":"Statements containing two or more subqueries, e.g. 'select * from t1 where a in (select ...) and b in (select ...)' or scalar subqueries in both select list and where clause, hitting routeNormalSqlWithAST0.","commonSituations":"Complex analytical SQL ported from MySQL; ORMs generating multiple IN-subqueries for filtered collections; report queries combining several lookups.","solutions":["Combine the subqueries into one (join the inner tables) so only a single SQLSelect remains.","Pre-execute each subquery in the application and inject literal value lists into the final query.","Use temporary tables or a Catlet to stage intermediate results.","Simplify the query into multiple sequential statements against Mycat."],"exampleFix":"// before\nSELECT * FROM t WHERE a IN (SELECT x FROM s1) AND b IN (SELECT y FROM s2);\n// after\nSELECT * FROM t WHERE a IN (1,2,3) AND b IN ('p','q'); -- values fetched in app first","handlingStrategy":"validation","validationCode":"int subqueries = countOccurrences(sql.toLowerCase(), \" in (select\") + countOccurrences(sql.toLowerCase(), \"exists (select\");\nif (subqueries > 1) throw new IllegalArgumentException(\"Mycat supports only one subquery per statement\");","typeGuard":null,"tryCatchPattern":"try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {\n    if (e.getMessage().contains(\"only one subQuery is supported\")) {\n        // execute subqueries separately and inline literal lists\n    } else throw e;\n}","preventionTips":["Limit statements to a single subquery when targeting Mycat.","Combine multiple inner lookups via joins in one subquery.","Replace secondary IN-subqueries with pre-fetched literal lists.","Use temp tables or a Catlet for multi-stage analytics."],"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"}