MyCATApache/Mycat-Server · error · SQLSyntaxErrorException

In case of slice table,there is no rule field in the…

Error message

In case of slice table,there is no rule field in the relationship condition!

What it means

For a direct-route join involving multiple sharded (slice) tables, Mycat requires that the relationship/join condition include each table's sharding (rule) field so it can prune datanodes. When rulemap shows more than one table needing a rule and checkRuleField finds the join condition does not constrain the rule columns, routing is aborted because a cartesian scan across all shards would otherwise be required.

Solutions

Add the sharding/rule columns of every involved table to the join (or where) condition.
Change one of the tables to a global table so it doesn't require a rule condition.
Redesign the sharding rule so both tables share the same partition column and join on it.
Use a Catlet or application-side join: query each table separately and merge in code.

Example fix

// before
SELECT * FROM orders o JOIN items i ON i.order_no = o.order_no;
// after (assuming both shard on customer_id)
SELECT * FROM orders o JOIN items i ON i.order_no = o.order_no AND i.customer_id = o.customer_id;

Defensive patterns

Strategy: validation

Validate before calling

// before issuing a join between sharded tables, assert shard keys are in the ON clause
Set<String> needed = Set.of("o.customer_id", "i.customer_id");
if (needed.stream().anyMatch(k -> !joinSql.toLowerCase().contains(k))) {
    throw new IllegalArgumentException("Join must include rule/sharding columns of all slice tables");
}

Try / catch

try { rrs = route(sql); } catch (SQLSyntaxErrorException e) {
    if (e.getMessage().contains("no rule field in the relationship condition")) {
        // fall back to separate per-table queries merged in application
    } else throw e;
}

Prevention

Always join sharded tables on their partition columns.
Make lookup/dimension tables global tables to avoid rule-field requirements.
Keep sharding key design aligned with common join paths.
Lint SQL for joins between sharded tables missing shard keys.

When it happens

Trigger: A join between two or more sharded tables whose ON clause omits one table's sharding key (e.g. joining on a non-partition column), with directRoute=true and not all tables being global tables.

Common situations: Joining a sharded table to another sharded table on a secondary column; schema evolution added a new sharded table without updating join keys; ORM-generated joins that don't know about shard keys.

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/50d7832f6ecb24a8. Report an issue: GitHub.

Appendix: source

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

						rulemap.put(tc.getName(), ruleCfg);
						//如果匹配规则不相同或者分片的datanode不相同则需要走子查询处理
						if(firstRule!=null&&((ruleCfg !=null && !ruleCfg.getRuleAlgorithm().equals(firstRule.getRuleAlgorithm()) )||( !dataNodes.equals(firstDataNodes)))){
							directRoute = false;
							break;
						}
					}
				}
				index++;
			}
		}

		RouteResultset rrsResult = rrs;
		if(directRoute){ //直接路由
			if(!RouterUtil.isAllGlobalTable(ctx, schemaConf)){
				if(rulemap.size()>1&&!checkRuleField(rulemap,visitor)){
					String err = "In case of slice table,there is no rule field in the relationship condition!";
					LOGGER.error(err);
					throw new SQLSyntaxErrorException(err);
				}
			}
			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);

View on GitHub (pinned to 65f8d8beb7)