MyCATApache/Mycat-Server · error · SQLSyntaxErrorException

In case of slice table,sql has different rules,currently…

Error message

In case of slice table,sql has different rules,currently only one subQuery is supported.

What it means

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).

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.

Example fix

// before
SELECT * FROM t WHERE a IN (SELECT x FROM s1) AND b IN (SELECT y FROM s2);
// after
SELECT * FROM t WHERE a IN (1,2,3) AND b IN ('p','q'); -- values fetched in app first

Defensive patterns

Strategy: validation

Validate before calling

int subqueries = countOccurrences(sql.toLowerCase(), " in (select") + countOccurrences(sql.toLowerCase(), "exists (select");
if (subqueries > 1) throw new IllegalArgumentException("Mycat supports only one subquery per statement");

Try / catch

try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {
    if (e.getMessage().contains("only one subQuery is supported")) {
        // execute subqueries separately and inline literal lists
    } else throw e;
}

Prevention

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.

When it happens

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

Common situations: Complex analytical SQL ported from MySQL; ORMs generating multiple IN-subqueries for filtered collections; report queries combining several lookups.

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/31700a78d8c504a6. Report an issue: GitHub.

Appendix: source

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

				}
			}else if(subQuerySize==1){     //只涉及一张表的子查询,使用  MiddlerResultHandler 获取中间结果后,改写原有 sql 继续执行 TODO 后期可能会考虑多个子查询的情况.
				SQLSelect sqlselect = visitor.getSubQuerys().iterator().next();
				if(!visitor.getRelationships().isEmpty()){     // 当 inner query  和 outer  query  有关联条件时,暂不支持
					String err = "In case of slice table,sql have different rules,the relationship condition is not supported.";
					LOGGER.error(err);
					throw new SQLSyntaxErrorException(err);
				}else{
					SQLSelectQuery sqlSelectQuery = sqlselect.getQuery();
					if(((MySqlSelectQueryBlock)sqlSelectQuery).getFrom() instanceof SQLExprTableSource) {
						rrs.setCacheAble(false);
						rrs.setFinishedRoute(true);
						rrsResult = middlerResultRoute(schema,charset,sqlselect,sqlType,statement,sc);
					}
				}
			}else if(subQuerySize >=2){
				String err = "In case of slice table,sql has different rules,currently only one subQuery is supported.";
				LOGGER.error(err);
				throw new SQLSyntaxErrorException(err);
			}
		}
		return rrsResult;
	}

	// 批量update,delete路由方法
	private RouteResultset routeMultiSqlWithAST(SchemaConfig schema, String stmt, RouteResultset rrs, String charset,
			LayerCachePool cachePool, int sqlType, ServerConnection sc) throws SQLNonTransientException {
		List<RouteResultsetNode> allNodes = new ArrayList<>(64);
		// 拆分出一个个SQL解析路由
		String remingSql = stmt;
		String eachSqlItem = null;
		do {
			int index = ParseUtil.findNextBreak(remingSql);
			if (index + 1 < remingSql.length() && !ParseUtil.isEOF(remingSql, index)) {
				eachSqlItem = remingSql.substring(0, index);
				remingSql = remingSql.substring(index + 1, remingSql.length());
				RouteResultset rrsTemp = new RouteResultset(eachSqlItem, sqlType);

View on GitHub (pinned to 65f8d8beb7)