MyCATApache/Mycat-Server · error · SQLSyntaxErrorException

In case of slice table,sql have different rules,the…

Error message

In case of slice table,sql have different rules,the relationship condition is not supported.

What it means

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.

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.

Example fix

// before
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.vip = 1);
// after
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip = 1);

Defensive patterns

Strategy: validation

Validate before calling

// detect correlated subqueries (inner WHERE references an outer alias)
java.util.regex.Pattern p = java.util.regex.Pattern.compile("(?is)select.*from\s+(\w+)\s+\w+.*exists\s*\(.*\1\.");
if (p.matcher(sql).find()) throw new IllegalArgumentException("Correlated subquery unsupported; rewrite as IN");

Try / catch

try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {
    if (e.getMessage().contains("relationship condition is not supported")) {
        // two-step: fetch subquery values, then outer query with IN list
    } else throw e;
}

Prevention

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.

When it happens

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

Common situations: Correlated EXISTS/IN subqueries written for monolithic MySQL; ORMs emitting correlation predicates; queries where both tables are sharded with different rules.

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/5a81ff24b6f09bb2. Report an issue: GitHub.

Appendix: source

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

				}
			}
			rrsResult = directRoute(rrs,ctx,schema,druidParser,statement,cachePool);
		}else{
			int subQuerySize = visitor.getSubQuerys().size();
			if(subQuerySize==0&&ctx.getTables().size()==2){ //两表关联,考虑使用catlet
				if(!visitor.getRelationships().isEmpty()){
					rrs.setCacheAble(false);
					rrs.setFinishedRoute(true);
					rrsResult = catletRoute(schema,ctx.getSql(),charset,sc);
				}else{
					rrsResult = directRoute(rrs,ctx,schema,druidParser,statement,cachePool);
				}
			}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路由方法

View on GitHub (pinned to 65f8d8beb7)