JamesRTaylor opened a new issue, #31656:
URL: https://github.com/apache/shardingsphere/issues/31656
### Which version of ShardingSphere did you use?
5.5.1-SNAPSHOT
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
Through unit tests
### Expected behavior
I'd expect that aliases would not have an impact on the logic of rewriting a
query to run against each shard. The actual alias name doesn't matter - it's
the original column that it references that matters (see repro steps below)
### Actual behavior
The column originally aliased in a query is not tracked, but instead only
the name is considered.
### Reason analyze (If you can)
When rewriting a query by replacing the logical table name with the physical
table name, aliases must be tracked so that you know which alias represents the
sharding column. Otherwise, the query will not be correctly rewritten.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
Add the following to
src/test/resources/scenario/sharding/case/dml/select.xml and run
ShardingSQLRewriterIT. This unit test should pass:
<rewrite-assertion id="select_over_aliased_shard_column">
<input sql="SELECT * FROM (SELECT account_id aliased_account_id FROM
t_account) WHERE aliased_account_id = ?" parameters="100" />
<output sql="SELECT * FROM (SELECT account_id aliased_account_id
FROM t_account_0) WHERE aliased_account_id = ?" parameters="100" />
</rewrite-assertion>
Instead ShardingSphere outputs two queries against t_account_0 and
t_account_1 not realizing that the sharding column is being constrained.
The reverse, when a different column is aliased with the name of the
sharding column. ShardingSphere thinks that the sharding column is being
constrained while this is not the case. This unit test should pass:
<rewrite-assertion id="select_over_alias_renamed_as_shard_column">
<input sql="SELECT * FROM (SELECT amount account_id FROM t_account)
WHERE account_id = ?" parameters="100" />
<output sql="SELECT * FROM (SELECT amount account_id FROM
t_account_0) WHERE account_id = ?" parameters="100" />
<output sql="SELECT * FROM (SELECT amount account_id FROM
t_account_1) WHERE account_id = ?" parameters="100" />
</rewrite-assertion>
Instead ShardingSphere thinks that the sharding column is being constrained.
### Example codes for reproduce this issue (such as a github link).
I can put together a PR that adds these tests if that's helpful
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
