[Encrypt] Issue with JOIN UPDATE when non-encrypt table is used as the primary table

[yuyongxing]

Title

[Encrypt] Issue with JOIN UPDATE when non-encrypt table is used as the primary table

---

Description

ShardingSphere Version:

5.4.0

Problem Description:

When performing a JOIN UPDATE between two tables, where:

• Table A (intermediary_service_pass) has no encrypted fields.
• Table B (intermediary_service) has encrypted fields.

If Table A is used as the primary table in the UPDATE statement, the following error occurs:
[44000][20709] Can not find encrypt table: A

SQL Examples:

1. SQL that triggers the error:

update intermediary_service_pass A, intermediary_service B
set A.flag=-1
where A.flag=0
  and A.service_id=B.id;

Error message:

[44000][20709] Can not find encrypt table: intermediary_service_pass

SQL that works correctly:

update intermediary_service B, intermediary_service_pass A
set A.flag=-1
where A.flag=0
  and A.service_id=B.id;

Expected Behavior:

The SQL should execute successfully regardless of the table order in the UPDATE statement.

Actual Behavior:

An error is thrown when the non-encrypted table (A) is used as the primary table in the UPDATE statement.

Environment:

ShardingSphere: 5.4.0
Database: MySQL 5.7

[strongduanmu]

Hi @yuyongxing, can you try the latest 5.5.1?

[yuyongxing]

When using the 5.5.1 version, this issue was resolved, but a new problem occurred: after adding a new data source, it is unable to query the tables that already exist in the data source.

[strongduanmu]

Can you close this issue, and submit a new issue for 5.5.1 exception?