dangmingyang opened a new issue, #37025: URL: https://github.com/apache/shardingsphere/issues/37025
## Bug Report **For English only**, other languages will not accept. Before report a bug, make sure you have: - Searched open and closed [GitHub issues](https://github.com/apache/shardingsphere/issues). - Read documentation: [ShardingSphere Doc](https://shardingsphere.apache.org/document/current/en/overview). Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will **close it**. Please answer these questions before submitting your issue. Thanks! ### Which version of ShardingSphere did you use? 5.5.2 ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-Proxy ### Expected behavior When querying encrypted fields in a MySQL database, the results obtained using functions such as CONCAT, UPPER, LOWER, LENGTH, and SUBSTRING are all the operation results of the ciphertext. When updating encrypted fields in a MySQL database, plaintext is written to the database when statements such as CASE WHEN and JOIN are used. ### Actual behavior ### Reason analyze (If you can) ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. I have created an encryption policy for the status column of the safe_string_constraints table using DistSQL. Encrypted data has been written into the status field. For example: #DBESV1#00000006status7r/j3UsjqcTG9eBz5jkUZhqqJe/x2h2vIJeLOXh0gUQ=. 1. When I use : ``` select concat(status,'_update') from safe_string_constraints; ``` The queried result is #DBESV1#00000006status7r/j3UsjqcTG9eBz5jkUZhqqJe/x2h2vIJeLOXh0gUQ=_update It seems that it did not perform decryption; instead, it directly concatenated the ciphertext with "_update". 2. When I use : ``` SELECT status, UPPER(status) as upper_status, LOWER(status) as lower_status, LENGTH(status) as status_length, SUBSTRING(status, 1, 4) as status_prefix FROM safe_string_constraints; ``` <img width="1758" height="781" alt="Image" src="https://github.com/user-attachments/assets/6172db37-61e1-4784-b286-b5ff7bb2b4a6"; /> It also did not perform decryption; instead, it directly processed the ciphertext as a regular string. 3. When I use ``` UPDATE safe_string_constraints SET status = CONCAT(status, '_updated') WHERE id = 5; ``` After the execution succeeds, the data in the database is #DBESV1#00000006status7r/j3UsjqcTG9eBz5jkUZhqqJe/x2h2vIJeLOXh0gUQ=_updated. Does this seem to be the same issue as the previous one? 4. When I use ``` UPDATE safe_string_constraints SET status = CASE WHEN rating = '4' THEN 'active' WHEN rating = '3' THEN 'pending' WHEN rating = '5' THEN 'suspended' ELSE status END; ``` ``` UPDATE safe_string_constraints s JOIN table1 p ON s.id = p.id SET s.status = p.status WHERE p.create_date > '2025-09-01'; ``` After the execution is successful, the data in the status field of the database are all plaintext values such as 'suspended' and 'pending', and no encryption has been performed. Do all the above SQL operations on encrypted fields not get supported? The specific descriptions of the support status for these scenarios are not found here: https://shardingsphere.apache.org/document/current/en/features/encrypt/appendix/ Thank you for your explanation. -- 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]
