[GitHub] [shardingsphere] sabz90 edited a comment on issue #7703: Can not update sharding key, logic table: [**], column: [**]

[GitBox <gi...@apache.org>]

sabz90 edited a comment on issue #7703:
URL: https://github.com/apache/shardingsphere/issues/7703#issuecomment-737068772


   > @strongduanmu Thank you for diving into it. @tristaZero My apologies that the proposed solution may not work as I originally agreed. The where clause doesn't contain the sharding key.
   > 
   > e.g. `update ACCOUNT_INFO set name=??, account_id=??, zip=??, version=?? where id = ??` was the generated sql with account_id being the sharding key. The `shardingValue` is empty in the code @strongduanmu posted (ShardingUpdateStatementValidator.java).
   > 
   > ![image](https://user-images.githubusercontent.com/3125184/100831541-d7d84100-341a-11eb-85d8-3336a726d57e.png)
   > 
   > So the logic may be
   > 
   > ```
   > if (no shardingKey in SET Clause)
   >     then PASS
   > else if (no shardingKey in WHERE Clause)
   >     then append sharding column and value to WHERE Clause
   > else if (the value of shardingKey in SET Clause == the value of shardingKey in WHERE Clause)
   >     then PASS
   > else
   >     then an exception
   > ```
   
   The second condition will fix this issue but it is a risky option - we are just assuming that the user did not intend to put the sharding column in the SET clause. 
   
   ```
   else if (no shardingKey in WHERE Clause)
       then append sharding column and value to WHERE Clause
   ```
   
   So a safer option would be to define an Annotation, something like "@ForceWhereClause" which should then indicate to sharding-sphere that it has to use the value from SET clause and put in a where clause along in the final statement.
   
   For now i have solved the problem by using CompositeKey @IdClass method , which makes hibernate include the column in where clause, but it's more of a hack since the sharding column is not really a composite key in the database level.


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

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