You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "wangmiscoding (via GitHub)" <gi...@apache.org> on 2025/10/18 10:26:40 UTC

[PR] Resolve the conflict between encrypted derived columns and primary key columns(#36841) [shardingsphere]

wangmiscoding opened a new pull request, #36899:
URL: https://github.com/apache/shardingsphere/pull/36899

   Fixes #36841.
   
   Changes proposed in this pull request:
     -
   I have fully reproduced this issue locally and identified the root cause.
   
   Reproduction scenario:
   When the encrypted column is the last field in the table, and both encrypted assisted query columns and Snowflake algorithm for generating primary key IDs are enabled.
   
   Root cause:
   In this insert SQL, Sharding needs to ensure the final SQL includes inserts for both the order_id and assisted_username columns, requiring SQL rewriting by Sharding.
   
   The SQL rewriting process involves the following steps:
   
   Add the values to be inserted - the Snowflake-generated order_id value (1186337235088179200) and the assisted column value generated from username (9de37a0627c25684fdd519ca84073e34):
   a. The logic for adding order_id is located in GeneratedKeyInsertValueParameterRewriter#rewrite, which appends the value 1186337235088179200 to the end of the parameter list.
   b. The logic for adding assisted_username is located in EncryptInsertValueParameterRewriter#rewrite, which finds the position of username in the list and places the assisted_username value immediately after it.
   
   This results in the value list becoming:
   (1, 23.4, 2025-10-18 16:16:21.7380934, 1, PENDING, null, bg+LQMPvSU8G0cnJuWM/1g==, 9de37a0627c25684fdd519ca84073e34, 1186341196495060992)
   
   Add the column names to be inserted - both order_id and assisted_username:
   a. In org.apache.shardingsphere.infra.rewrite.sql.token.keygen.generator.GeneratedKeyInsertColumnTokenGenerator#generateSQLToken,
   order_id is inserted at the end of the column name list.
   b. In org.apache.shardingsphere.encrypt.rewrite.token.generator.insert.EncryptInsertDerivedColumnsTokenGenerator#generateSQLTokens,
   the derived assisted_username is placed after username.
   
   The problem arises here:
   Since username itself is already the last field, placing assisted_username after username is equivalent to placing it at the end, causing disorder between assisted_username and order_id.
   
   As shown in the figure, the insertion indexes become identical.
   <img width="1436" height="430" alt="image" src="https://github.com/user-attachments/assets/7691a64d-f384-48bc-8ee4-64f78c0fab94" />
   
   
   Therefore, to resolve this issue, we need to ensure consistent ordering between the derived parameter columns and derived value columns.
   
   The approach I'm taking:
   Modify the derivation logic to keep the derived primary key token at the very end. This way, even if the encrypted column is in the last position, the derived auxiliary column will still appear before the primary key.
   
   I believe this approach requires minimal changes. I appreciate everyone's perspectives on this solution
   
   
   ---
   
   Before committing this PR, I'm sure that I have checked the following options:
   - [ ] My code follows the [code of conduct](https://shardingsphere.apache.org/community/en/involved/conduct/code/) of this project.
   - [ ] I have self-reviewed the commit code.
   - [ ] I have (or in comment I request) added corresponding labels for the pull request.
   - [ ] I have passed maven check locally : `./mvnw clean install -B -T1C -Dmaven.javadoc.skip -Dmaven.jacoco.skip -e`.
   - [ ] I have made corresponding changes to the documentation.
   - [ ] I have added corresponding unit tests for my changes.
   - [ ] I have updated the Release Notes of the current development version. For more details, see [Update Release Note](https://shardingsphere.apache.org/community/en/involved/contribute/contributor/)
   


-- 
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: [PR] Resolve the conflict between encrypted derived columns and primary key columns(#36841) [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed pull request #36899: Resolve the conflict between encrypted derived columns and primary key columns(#36841)
URL: https://github.com/apache/shardingsphere/pull/36899


-- 
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: [PR] Resolve the conflict between encrypted derived columns and primary key columns(#36841) [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on PR #36899:
URL: https://github.com/apache/shardingsphere/pull/36899#issuecomment-3494626515

   Please see comment on issue


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