I want to use the same table field for both assistedQuery and likeQuery

[daoxincc]

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

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc</artifactId>
    <version>5.5.3</version>
</dependency>

config

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

Caused by: java.sql.SQLSyntaxErrorException: Column 'assisted_val' specified twice

[Phixsura]

@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/):

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

[terrymanu]

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.