shard failed when there is a subquery and shard column name existed in both tables

[KolorYan]

Bug Report

simple sql：
SELECT * FROM a WHERE pid = #{pid} AND typeIN (selecttypefrom b where pid = #{bpid} );

1. a is a shard table, pid is shard column
2. b is a single table, it has a column named 'pid' too
3. in method org.apache.shardingsphere.sharding.route.engine.condition.engine.WhereClauseShardingConditionEngine#createShardingConditions(org.apache.shardingsphere.infra.binder.context.statement.SQLStatementContext, java.util.List<java.lang.Object>)
   variable columnExpressionTableNames mapping column and table, because table a and b both exist a column 'pid', so the mapping result is pid -> b, but expected is pid -> a, because b is a single table, should be ignored

Which version of ShardingSphere did you use?

5.5.1

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

shard on table a_xxx

Actual behavior

shard on all tables for a

[terrymanu]

Do you enable SQL_FEDERATION rule?

[KolorYan]

SQL_FEDERATION is disabled, it will bring more performance loss and uncertainty, so I don't plan to turn it on. However, in the scenario I mentioned, should we consider optimizing the code logic? After all, b is not a shard table

[terrymanu]

Can not support if SQL_FEDERATION is disabled

[github-actions]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.