You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "daoxincc (via GitHub)" <gi...@apache.org> on 2026/05/14 07:21:42 UTC
[I] I want to use the same table field for both assistedQuery and likeQuery [shardingsphere]
daoxincc opened a new issue, #38693:
URL: https://github.com/apache/shardingsphere/issues/38693
## Question
I want to use the same table field for both assistedQuery and likeQuery,I know it doesn't sound reasonable.
But I want to know if it is possible to remove duplicate field names when generating the actual SQL, when I set two configurations to have the same fields
## version
```xml
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>shardingsphere-jdbc</artifactId>
<version>5.5.3</version>
</dependency>
```
## config
```yaml
rules:
- !ENCRYPT
tables:
member:
columns:
val:
cipher:
name: val
encryptorName: aes_encryptor
assistedQuery:
name: assisted_val
encryptorName: like_encryptor
likeQuery:
name: assisted_val
encryptorName: like_encryptor
```
when I use this config, I will got an error
```text
Caused by: java.sql.SQLSyntaxErrorException: Column 'assisted_val' specified twice
```
--
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] I want to use the same table field for both assistedQuery and likeQuery [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #38693: I want to use the same table field for both assistedQuery and likeQuery
URL: https://github.com/apache/shardingsphere/issues/38693
--
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] I want to use the same table field for both assistedQuery and likeQuery [shardingsphere]
Posted by "Phixsura (via GitHub)" <gi...@apache.org>.
Phixsura commented on issue #38693:
URL: https://github.com/apache/shardingsphere/issues/38693#issuecomment-4474235636
@daoxincc
checked 5.5.3 source. there's no config switch for this, the rewriter just doesn't dedup on physical column name.
root cause for the INSERT error is in `EncryptInsertDerivedColumnsTokenGenerator.getDerivedColumnNames` (`features/encrypt/core/src/main/java/org/apache/shardingsphere/encrypt/rewrite/token/generator/insert/`):
```java
encryptColumn.getAssistedQuery().ifPresent(optional -> result.add(optional.getName()));
encryptColumn.getLikeQuery().ifPresent(optional -> result.add(optional.getName()));
```
two independent `ifPresent` calls, no distinct. when both slots resolve to `assisted_val`, both get appended and the rewritten INSERT carries the column twice, so mysql trips on "specified twice".
the matching values are appended by `EncryptInsertValuesTokenGenerator.encryptToken` with the same shape: two independent `if (...isPresent())` blocks that each bump `indexDelta`. so the column list and the values list are kept in sync only because both are non-deduped. any dedup fix has to touch both at once, otherwise columns and values go out of alignment.
UPDATE goes through a different path (`EncryptUpdateAssignmentTokenGenerator` delegates to `EncryptAssignmentTokenGenerator` in `.../generator/assignment/`), and `generateParameterSQLToken` / `addAssistedQueryAssignment` / `addLikeAssignment` follow the same pattern. same behavior, different file.
**so concretely:**
* no engine-side workaround in 5.5.3. only schema-side option is giving the two slots distinct `name`s.
* if you want to send a PR, `getDerivedColumnNames` is the smallest entry point, but a complete fix needs the values-list dedup in `EncryptInsertValuesTokenGenerator` and the assignment-side dedup in `EncryptAssignmentTokenGenerator` to match. otherwise UPDATE and INSERT-on-duplicate-key paths will still misbehave.
* reviewers will probably ask for a concrete use case beyond "save a column". worth describing your actual scenario in this issue before opening a PR, otherwise it'll likely stall on motivation rather than on the patch itself.
--
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] I want to use the same table field for both assistedQuery and likeQuery [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #38693:
URL: https://github.com/apache/shardingsphere/issues/38693#issuecomment-4545639643
Hi @daoxincc, thanks for the question.
This configuration is not supported by the current Encrypt rule model.
`assistedQuery` and `likeQuery` are not two aliases of the same physical column. They represent two different derived-column roles:
- `assistedQuery` is used for equivalent-query support.
- `likeQuery` is used for `LIKE` query support.
Therefore they should be mapped to different physical columns.
If two different algorithms are used, one physical column cannot store both derived values at the same time. If the same algorithm is used, the current rule model still does not define one physical column as both the assisted-query column and the like-query column. The SQL rewrite engine handles these two slots independently, so generating both columns is expected behavior under the current design.
So this is not considered a bug in ShardingSphere-JDBC 5.5.3. Please configure separate physical columns for `assistedQuery.name` and `likeQuery.name`.
If there is a concrete use case for one derived column to support both equivalent query and `LIKE` query, please open a separate enhancement discussion with the expected algorithm semantics, write behavior, and compatibility considerations. For this issue, I suggest closing it as invalid usage / question.
--
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