aillamsun opened a new issue, #33894:
URL: https://github.com/apache/shardingsphere/issues/33894
## config
### oracle DB
### shardingsphere 5.2.1
### mybatis
```java
TR_USER_VEHICLE_AUTH:
columns:
VIN:
plainColumn: VIN
cipherColumn: VIN_CIPHER
assistedQueryColumn: VIN_QUERY_CIPHER
assistedQueryEncryptorName: assisted_query_encryptor
encryptorName: kms_encryptor
OWNER:
plainColumn: OWNER
cipherColumn: OWNER_CIPHER
assistedQueryColumn: OWNER_QUERY_CIPHER
assistedQueryEncryptorName: assisted_query_encryptor
encryptorName: kms_encryptor
VEHICLE_PLATE_NO:
plainColumn: VEHICLE_PLATE_NO
cipherColumn: VEHICLE_PLATE_NO_CIPHER
assistedQueryColumn: VEHICLE_PLATE_NO_QUERY_CIPHER
assistedQueryEncryptorName: assisted_query_encryptor
encryptorName: kms_encryptor
```
### Logic SQL:
```SQL
SELECT "ID" AS "ID",
VEHICLE_PLATE_NO AS "VEHICLE_PLATE_NO",
STEP AS "STEP",
OWNER AS "OWNER",
USER_ID AS "USER_ID",
VIN AS "VIN",
MERCHANT_CODE AS "MERCHANT_CODE",
BIND_STATUS AS "BIND_STATUS",
AUTH_STATUS AS "AUTH_STATUS",
AUTH_TYPE AS "AUTH_TYPE",
UNBIND_TIME AS "UNBIND_TIME",
AUTH_SUB_TIME AS "AUTH_SUB_TIME",
AUTH_FINISH_TIME AS "AUTH_FINISH_TIME",
UPDATE_TIME AS "UPDATE_TIME",
GRANT_SUCC_TYPE AS "GRANT_SUCC_TYPE",
CREATE_BY AS "CREATE_BY",
REPLACE_FLAG AS "REPLACE_FLAG",
PIC_TYPE AS PIC_TYPE
FROM
(SELECT TMP_PAGE."ID" AS "ID",
TMP_PAGE.VEHICLE_PLATE_NO AS "VEHICLE_PLATE_NO",
TMP_PAGE.STEP AS "STEP",
TMP_PAGE.OWNER AS "OWNER",
TMP_PAGE.USER_ID AS "USER_ID",
TMP_PAGE.VIN AS "VIN",
TMP_PAGE.MERCHANT_CODE AS "MERCHANT_CODE",
TMP_PAGE.BIND_STATUS AS "BIND_STATUS",
TMP_PAGE.AUTH_STATUS AS "AUTH_STATUS",
TMP_PAGE.AUTH_TYPE AS "AUTH_TYPE",
TMP_PAGE.UNBIND_TIME AS "UNBIND_TIME",
TMP_PAGE.AUTH_SUB_TIME AS "AUTH_SUB_TIME",
TMP_PAGE.AUTH_FINISH_TIME AS "AUTH_FINISH_TIME",
TMP_PAGE.UPDATE_TIME AS "UPDATE_TIME",
TMP_PAGE.GRANT_SUCC_TYPE AS "GRANT_SUCC_TYPE",
TMP_PAGE.CREATE_BY AS "CREATE_BY",
TMP_PAGE.REPLACE_FLAG AS "REPLACE_FLAG",
TMP_PAGE.PIC_TYPE AS PIC_TYPE,
ROWNUM ROW_ID
FROM
(SELECT TRU."ID" AS "ID",
TRU.VEHICLE_PLATE_NO AS "VEHICLE_PLATE_NO",
TRU.STEP AS "STEP",
TRU.OWNER AS "OWNER",
TRU.USER_ID AS "USER_ID",
TRU.VIN AS "VIN",
TRU.MERCHANT_CODE AS "MERCHANT_CODE",
TRU.BIND_STATUS AS "BIND_STATUS",
TRU.AUTH_STATUS AS "AUTH_STATUS",
TRU.AUTH_TYPE AS "AUTH_TYPE",
TRU.UNBIND_TIME AS "UNBIND_TIME",
TRU.AUTH_SUB_TIME AS "AUTH_SUB_TIME",
TRU.AUTH_FINISH_TIME AS "AUTH_FINISH_TIME",
TRU.UPDATE_TIME AS "UPDATE_TIME",
TRU.GRANT_SUCC_TYPE AS "GRANT_SUCC_TYPE",
TRU.CREATE_BY AS "CREATE_BY",
TRU.REPLACE_FLAG AS "REPLACE_FLAG",
TVCI.PIC_TYPE AS PIC_TYPE
FROM TR_USER_VEHICLE_AUTH TRU
LEFT JOIN
(SELECT DISTINCT TVCI.AUTH_ID,
TVCI.PIC_TYPE
FROM TB_VEHICLE_CERTIFICATION_INFO TVCI
WHERE TVCI.IS_DELETE = '0'
AND TVCI.PIC_TYPE IN ('3', '7', '8', '10') ) TVCI
ON TRU.ID = TVCI.AUTH_ID
WHERE TRU.AUTH_STATUS in('0','8','9')
AND TRU.IS_DELETE = '0'
ORDER BY TRU.UPDATE_TIME DESC ) TMP_PAGE)
WHERE ROW_ID <= ?
AND ROW_ID > ?
```
## Actual SQL:
```SQL
SELECT "ID" AS "ID",
VEHICLE_PLATE_NO_CIPHER AS VEHICLE_PLATE_NO,
STEP AS "STEP",
OWNER_CIPHER AS OWNER,
USER_ID AS "USER_ID",
VIN_CIPHER AS VIN,
MERCHANT_CODE AS "MERCHANT_CODE",
BIND_STATUS AS "BIND_STATUS",
AUTH_STATUS AS "AUTH_STATUS",
AUTH_TYPE AS "AUTH_TYPE",
UNBIND_TIME AS "UNBIND_TIME",
AUTH_SUB_TIME AS "AUTH_SUB_TIME",
AUTH_FINISH_TIME AS "AUTH_FINISH_TIME",
UPDATE_TIME AS "UPDATE_TIME",
GRANT_SUCC_TYPE AS "GRANT_SUCC_TYPE",
CREATE_BY AS "CREATE_BY",
REPLACE_FLAG AS "REPLACE_FLAG",
PIC_TYPE AS PIC_TYPE
FROM
(SELECT TMP_PAGE."ID" AS "ID",
TMP_PAGE.VEHICLE_PLATE_NO AS "VEHICLE_PLATE_NO",
TMP_PAGE.STEP AS "STEP",
TMP_PAGE.OWNER AS "OWNER",
TMP_PAGE.USER_ID AS "USER_ID",
TMP_PAGE.VIN AS "VIN",
TMP_PAGE.MERCHANT_CODE AS "MERCHANT_CODE",
TMP_PAGE.BIND_STATUS AS "BIND_STATUS",
TMP_PAGE.AUTH_STATUS AS "AUTH_STATUS",
TMP_PAGE.AUTH_TYPE AS "AUTH_TYPE",
TMP_PAGE.UNBIND_TIME AS "UNBIND_TIME",
TMP_PAGE.AUTH_SUB_TIME AS "AUTH_SUB_TIME",
TMP_PAGE.AUTH_FINISH_TIME AS "AUTH_FINISH_TIME",
TMP_PAGE.UPDATE_TIME AS "UPDATE_TIME",
TMP_PAGE.GRANT_SUCC_TYPE AS "GRANT_SUCC_TYPE",
TMP_PAGE.CREATE_BY AS "CREATE_BY",
TMP_PAGE.REPLACE_FLAG AS "REPLACE_FLAG",
TMP_PAGE.PIC_TYPE AS PIC_TYPE,
ROWNUM ROW_ID
FROM
(SELECT TRU."ID" AS "ID",
TRU.VEHICLE_PLATE_NO_CIPHER,
TRU.VEHICLE_PLATE_NO_QUERY_CIPHER,
TRU.VEHICLE_PLATE_NO,
TRU.STEP AS "STEP",
TRU.OWNER_CIPHER,
TRU.OWNER_QUERY_CIPHER,
TRU.OWNER,
TRU.USER_ID AS "USER_ID",
TRU.VIN_CIPHER,
TRU.VIN_QUERY_CIPHER,
TRU.VIN,
TRU.MERCHANT_CODE AS "MERCHANT_CODE",
TRU.BIND_STATUS AS "BIND_STATUS",
TRU.AUTH_STATUS AS "AUTH_STATUS",
TRU.AUTH_TYPE AS "AUTH_TYPE",
TRU.UNBIND_TIME AS "UNBIND_TIME",
TRU.AUTH_SUB_TIME AS "AUTH_SUB_TIME",
TRU.AUTH_FINISH_TIME AS "AUTH_FINISH_TIME",
TRU.UPDATE_TIME AS "UPDATE_TIME",
TRU.GRANT_SUCC_TYPE AS "GRANT_SUCC_TYPE",
TRU.CREATE_BY AS "CREATE_BY",
TRU.REPLACE_FLAG AS "REPLACE_FLAG",
TVCI.PIC_TYPE AS PIC_TYPE
FROM TR_USER_VEHICLE_AUTH TRU
LEFT JOIN
(SELECT DISTINCT TVCI.AUTH_ID,
TVCI.PIC_TYPE
FROM TB_VEHICLE_CERTIFICATION_INFO TVCI
WHERE TVCI.IS_DELETE = '0'
AND TVCI.PIC_TYPE IN ('3', '7', '8', '10') ) TVCI
ON TRU.ID = TVCI.AUTH_ID
WHERE TRU.AUTH_STATUS in('0','8','9')
AND TRU.IS_DELETE = '0'
ORDER BY TRU.UPDATE_TIME DESC ) TMP_PAGE)
WHERE ROW_ID <= ?
AND ROW_ID > ?
```
## error msg
```java
### Cause: java.sql.SQLSyntaxErrorException: ORA-00904: "VIN_CIPHER": 标识符无效
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException:
ORA-00904: "VIN_CIPHER": 标识符无效
```
TMP_PAGE.* sql VEHICLE_PLATE_NO_CIPHER、OWNER_QUERY_CIPHER、VIN_CIPHER
the encrypted field is not filled in.
after removing LEFT JOIN SQL, it is possible
--
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]
