[I] Sharding column not tracked through aliases [shardingsphere]

["JamesRTaylor (via GitHub)" <gi...@apache.org>]

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: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] Sharding column not tracked through aliases [shardingsphere]

["strongduanmu (via GitHub)" <gi...@apache.org>]

strongduanmu closed issue #31656: Sharding column not tracked through aliases
URL: https://github.com/apache/shardingsphere/issues/31656


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

Re: [I] Sharding column not tracked through aliases [shardingsphere]

["strongduanmu (via GitHub)" <gi...@apache.org>]

strongduanmu commented on issue #31656:
URL: https://github.com/apache/shardingsphere/issues/31656#issuecomment-2161798602

   Hi @JamesRTaylor, thank you for your feedback. This is indeed a bug. I think we can use SQL Binder to analyze the original column of the alias column and then support this case when extracting the Sharding Condition. 
   
   Let's see if anyone wants to help with this.


-- 
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: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org