MyCATApache/Mycat-Server · error · SQLSyntaxErrorException

In subQuery,the or condition is not supported.

Error message

In subQuery,the or condition is not supported.

What it means

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.

Solutions

  1. Rewrite the OR as a UNION of two queries with AND-free single-key predicates.
  2. Push the OR condition outside the subquery or restructure so each subquery predicate is a single shard-key condition.
  3. Split the query into two separate queries in application code and merge results.
  4. Remove the subquery entirely by pre-fetching the inner result set and using a literal IN list.

Example fix

// before
SELECT * FROM orders WHERE cust_id IN (SELECT id FROM custs WHERE region='EU' OR region='US');
// after
SELECT * FROM orders WHERE cust_id IN (SELECT id FROM custs WHERE region='EU')
UNION
SELECT * FROM orders WHERE cust_id IN (SELECT id FROM custs WHERE region='US');
Defensive patterns

Strategy: validation

Validate before calling

// reject OR inside subqueries before routing
if (sql.matches("(?is).*in\s*\(\s*select.*or\s.*\).*")) {
    throw new IllegalArgumentException("Rewrite OR inside subquery: unsupported by Mycat router");
}

Try / catch

try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {
    if (e.getMessage().contains("or condition is not supported")) {
        // fall back to UNION rewrite or application-side merge
    } else throw e;
}

Prevention

When it happens

Trigger: 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.

Common situations: 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.

Understand the failure class

Background: UnsupportedOperationException and "is not supported" errors: when a library deliberately refuses a call — this error's family across 30 libraries.

Related errors

AI-assisted analysis of MyCATApache/Mycat-Server@65f8d8beb7 (2026-09-11). Data as JSON: /api/errors/25bdc8142b9b530a. Report an issue: GitHub.

Appendix: source

Thrown at src/main/java/io/mycat/route/impl/DruidMycatRouteStrategy.java:163

		} catch (Exception t) {
			LOGGER.error("DruidMycatRouteStrategyError", t);
			throw new SQLSyntaxErrorException(t);
		}

		/**
		 * 检验unsupported statement
		 */
		checkUnSupportedStatement(statement);

		DruidParser druidParser = DruidParserFactory.create(schema, statement, visitor);
		druidParser.parser(schema, rrs, statement, stmt,cachePool,visitor);
		DruidShardingParseInfo ctx=  druidParser.getCtx() ;
		rrs.setTables(ctx.getTables());

		if(visitor.isSubqueryRelationOr()){
			String err = "In subQuery,the or condition is not supported.";
			LOGGER.error(err);
			throw new SQLSyntaxErrorException(err);
		}

		/* 按照以下情况路由
			1.2.1 可以直接路由.
       		1.2.2 两个表夸库join的sql.调用calat
       		1.2.3 需要先执行subquery 的sql.把subquery拆分出来.获取结果后,与outerquery
		 */

		//add huangyiming 分片规则不一样的且表中带查询条件的则走Catlet
		List<String> tables = ctx.getTables();
		SchemaConfig schemaConf = MycatServer.getInstance().getConfig().getSchemas().get(schema.getName());
		int index = 0;
		RuleConfig firstRule = null;
		boolean directRoute = true;
		Set<String> firstDataNodes = new HashSet<String>();
		Map<String, TableConfig> tconfigs = schemaConf==null?null:schemaConf.getTables();

		Map<String,RuleConfig> rulemap = new HashMap<>();

View on GitHub (pinned to 65f8d8beb7)