{"record":{"id":"25bdc8142b9b530a","repo":"MyCATApache/Mycat-Server","slug":"in-subquery-the-or-condition-is-not-supported","errorCode":null,"errorMessage":"In subQuery,the or condition is not supported.","messagePattern":"In subQuery,the or condition is not supported\\.","errorType":"exception","errorClass":"SQLSyntaxErrorException","httpStatus":null,"severity":"error","filePath":"src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java","lineNumber":163,"sourceCode":"\t\t} catch (Exception t) {\n\t\t\tLOGGER.error(\"DruidMycatRouteStrategyError\", t);\n\t\t\tthrow new SQLSyntaxErrorException(t);\n\t\t}\n\n\t\t/**\n\t\t * 检验unsupported statement\n\t\t */\n\t\tcheckUnSupportedStatement(statement);\n\n\t\tDruidParser druidParser = DruidParserFactory.create(schema, statement, visitor);\n\t\tdruidParser.parser(schema, rrs, statement, stmt,cachePool,visitor);\n\t\tDruidShardingParseInfo ctx=  druidParser.getCtx() ;\n\t\trrs.setTables(ctx.getTables());\n\n\t\tif(visitor.isSubqueryRelationOr()){\n\t\t\tString err = \"In subQuery,the or condition is not supported.\";\n\t\t\tLOGGER.error(err);\n\t\t\tthrow new SQLSyntaxErrorException(err);\n\t\t}\n\n\t\t/* 按照以下情况路由\n\t\t\t1.2.1 可以直接路由.\n       \t\t1.2.2 两个表夸库join的sql.调用calat\n       \t\t1.2.3 需要先执行subquery 的sql.把subquery拆分出来.获取结果后,与outerquery\n\t\t */\n\n\t\t//add huangyiming 分片规则不一样的且表中带查询条件的则走Catlet\n\t\tList<String> tables = ctx.getTables();\n\t\tSchemaConfig schemaConf = MycatServer.getInstance().getConfig().getSchemas().get(schema.getName());\n\t\tint index = 0;\n\t\tRuleConfig firstRule = null;\n\t\tboolean directRoute = true;\n\t\tSet<String> firstDataNodes = new HashSet<String>();\n\t\tMap<String, TableConfig> tconfigs = schemaConf==null?null:schemaConf.getTables();\n\n\t\tMap<String,RuleConfig> rulemap = new HashMap<>();","sourceCodeStart":145,"sourceCodeEnd":181,"githubUrl":"https://github.com/MyCATApache/Mycat-Server/blob/65f8d8beb752f935752f2a0eec0ab017facab9ef/src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java#L145-L181","documentation":"When routing a statement containing subqueries, Mycat checks the visitor's relationship analysis; if the subquery (or relation between inner and outer query) uses an OR condition, the router cannot determine shard pruning safely and throws. OR conditions across sharded relations are unsupported because Mycat cannot split them into per-shard predicates.","triggerScenarios":"Executing SQL like 'select * from t where id in (select id from s where a=1 or b=2)' or a join/subquery relation combined with OR, so visitor.isSubqueryRelationOr() returns true during routeNormalSqlWithAST0.","commonSituations":"ORMs generating OR filters inside subqueries; hand-written analytics SQL with OR between join keys; developers moving from a monolithic MySQL to Mycat without reviewing subquery predicates.","solutions":["Rewrite the OR as a UNION of two queries with AND-free single-key predicates.","Push the OR condition outside the subquery or restructure so each subquery predicate is a single shard-key condition.","Split the query into two separate queries in application code and merge results.","Remove the subquery entirely by pre-fetching the inner result set and using a literal IN list."],"exampleFix":"// before\nSELECT * FROM orders WHERE cust_id IN (SELECT id FROM custs WHERE region='EU' OR region='US');\n// after\nSELECT * FROM orders WHERE cust_id IN (SELECT id FROM custs WHERE region='EU')\nUNION\nSELECT * FROM orders WHERE cust_id IN (SELECT id FROM custs WHERE region='US');","handlingStrategy":"validation","validationCode":"// reject OR inside subqueries before routing\nif (sql.matches(\"(?is).*in\\s*\\(\\s*select.*or\\s.*\\).*\")) {\n    throw new IllegalArgumentException(\"Rewrite OR inside subquery: unsupported by Mycat router\");\n}","typeGuard":null,"tryCatchPattern":"try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {\n    if (e.getMessage().contains(\"or condition is not supported\")) {\n        // fall back to UNION rewrite or application-side merge\n    } else throw e;\n}","preventionTips":["Keep subquery predicates limited to single shard-key equality/IN conditions.","Review ORM-generated SQL for OR filters inside IN/EXISTS subqueries.","Prefer UNION over OR for cross-key conditions.","Test sharded queries against Mycat before production rollout."],"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"}