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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
