You are viewing a plain text version of this content. The canonical link for it is here.

Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2022/10/27 10:17:57 UTC

[GitHub] [shardingsphere] zhang-zhipeng opened a new issue, #21799: mybatis-plus3.5.2、shardingsphere5.1.1、oracle，When querying by page, the sql of the queried field name is spliced with quotation marks, resulting in an error in the query. ORA-00904: "update_time": the ID is invalid

zhang-zhipeng opened a new issue, #21799:
URL: https://github.com/apache/shardingsphere/issues/21799

## 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.1.1

### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC

### Expected behavior
mybatis-plus3.5.2、shardingsphere5.1.1、oracle.
When querying by page, the sql of the queried field name has no concatenated quotation marks.
I found that the latest version of OracleDataType also uses QuoteCharacter.QUOTE, which still causes the rewritten sql to be quoted.
### Actual behavior
When querying by page, the sql of the queried field name is spliced with quotation marks, resulting in an error in the query. ORA-00904: "update_time": the ID is invalid

### Reason analyze (If you can)

### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

### Example codes for reproduce this issue (such as a github link).

--
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

[GitHub] [shardingsphere] strongduanmu commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1（Using encrypt）、oracle，When querying by page,rewirte sql is error, regards the ROWNUM as a real field, but it is not

Posted by GitBox <gi...@apache.org>.

strongduanmu commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1323367803

   > function
   
   @zhang-zhipeng Thank you for your remind, I will check this issue today.


-- 
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

[GitHub] [shardingsphere] zhang-zhipeng commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1（Using encrypt）、oracle，When querying by page,rewirte sql is error, regards the ROWNUM as a real field, but it is not

Posted by GitBox <gi...@apache.org>.

zhang-zhipeng commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1294526443

   Entity：
   @Data
   @TableName(value = "T_CIPHER_OLD")
   public class CipherOld implements Serializable {
       @TableId(value = "ID")
       private Long id;
       @TableId(value = "NAME")
       private String name;
       @TableId(value = "PWD")
       private String pwd;
       @TableId(value = "MOBILE")
       private String mobile;
       @TableId(value = "CUSTOMER_ID")
       private Long customerId;
       @TableId(value = "CREATE_TIME")
       private Date createTime;
       @TableId(value = "UPDATE_TIME")
       private Date updateTime;
   }


-- 
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

[GitHub] [shardingsphere] zhang-zhipeng commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1、oracle，When querying by page, the sql of the queried field name is spliced with quotation marks, resulting in an error in the query. ORA-00904: "update_time": the ID is invalid

Posted by GitBox <gi...@apache.org>.

zhang-zhipeng commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1294457338

   If all field queries do not splice quotation marks, or ROWNUM does not splice quotation marks, SQL is correct


-- 
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

[GitHub] [shardingsphere] strongduanmu commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1（Using encrypt）、oracle，When querying by page,rewirte sql is error, regards the ROWNUM as a real field, but it is not

Posted by GitBox <gi...@apache.org>.

strongduanmu commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1294517456

   Hi @zhang-zhipeng, thank you for your feedback. Can you show your encrpt configuration and table init sql?


-- 
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

[GitHub] [shardingsphere] zhang-zhipeng commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1（Using encrypt）、oracle，When querying by page,rewirte sql is error, regards the ROWNUM as a real field, but it is not

Posted by GitBox <gi...@apache.org>.

zhang-zhipeng commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1322963964

   @strongduanmu  Will it be fixed in the latest version？


-- 
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

[GitHub] [shardingsphere] zhang-zhipeng commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1（Using encrypt）、oracle，When querying by page,rewirte sql is error, regards the ROWNUM as a real field, but it is not

Posted by GitBox <gi...@apache.org>.

zhang-zhipeng commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1294523247

   encrypt config：
   rules:
         encrypt:
           encryptors:
             pwd-encryptor:
               type: AES
               props:
                 aes-key-value: 123456abc
           tables:
             T_CIPHER_OLD:
               columns:
                 PWD:
                   cipher-column: PWD_CIPHER
                   encryptor-name: pwd-encryptor
   
   create table sql:
   CREATE TABLE "T_CIPHER_OLD"
   (
   	"ID" VARCHAR2(50) NOT NULL ENABLE,
   	"NAME" VARCHAR2(255),
   	"CREATE_TIME" TIMESTAMP(6),
   	"UPDATE_TIME" TIMESTAMP(6),
   	"MOBILE" VARCHAR2(100),
   	"PWD_CIPHER" VARCHAR2(100),
   	"CUSTOMER_ID" VARCHAR2(100),
   	PRIMARY KEY("ID")
   )


-- 
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

[GitHub] [shardingsphere] strongduanmu commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1（Using encrypt）、oracle，When querying by page,rewirte sql is error, regards the ROWNUM as a real field, but it is not

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.

strongduanmu commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1670453672

   Hi @zhang-zhipeng, shardingsphere can not support shorthand expand when subuqery contains encrypt column, you can try to modify shorthand to columns. 
   
   For more details, you can refer - https://shardingsphere.apache.org/document/current/en/features/encrypt/appendix/


-- 
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

[GitHub] [shardingsphere] zhang-zhipeng commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1、oracle，When querying by page, the sql of the queried field name is spliced with quotation marks, resulting in an error in the query. ORA-00904: "update_time": the ID is invalid

Posted by GitBox <gi...@apache.org>.

zhang-zhipeng commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1294456383

   Logic SQL: 
   SELECT * FROM (SELECT TMP.*, ROWNUM ROW_ID FROM (SELECT ID, NAME, PWD, MOBILE, CUSTOMER_ID, CREATE_TIME, UPDATe_TIME, FROM T_CIPHER_OLD WHERE (ID = ?) ) TMP WHERE ROWNUM <= ?) WHERE ROW_ID > ?
   
   ACTUAL SQL：
   SELECT "ID", "NAME", "PWD_CIPHER" AS "PWD", "MOBILE", "CUSTOMER_ID", "CREATE_TIME", "UPDATE_TIME", "ROWNUM" AS "ROW_ID" FROM ( SELECT "ID", "NAME", "PWD_CIPHER", "MOBILE", "CUSTOMER_ID", "CREATE_TIME", "UPDATE_TIME", ROWNUM AS ROW_ID FROM (SELECT SELECT ID, NAME, PWD, MOBILE, CUSTOMER_ID, CREATE_TIME, UPDATe_TIME, FROM T_CIPHER_OLD WHERE (ID = ?) ) TMP WHERE ROWNUM <= ?) WHERE ROW_ID > ?
   
   The "ROWNUM" AS "ROW_ID" in ACTUAL SQL regards the ROWNUM as a real field, but it is not


-- 
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

[GitHub] [shardingsphere] zhang-zhipeng commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1、oracle，When querying by page, the sql of the queried field name is spliced with quotation marks, resulting in an error in the query. ORA-00904: "update_time": the ID is invalid

Posted by GitBox <gi...@apache.org>.

zhang-zhipeng commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1294443839

   Encrypt function is used


-- 
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

[GitHub] [shardingsphere] strongduanmu closed issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1（Using encrypt）、oracle，When querying by page,rewirte sql is error, regards the ROWNUM as a real field, but it is not

Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.

strongduanmu closed issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1（Using encrypt）、oracle，When querying by page,rewirte sql is error, regards the ROWNUM as a real field, but it is not
URL: https://github.com/apache/shardingsphere/issues/21799


-- 
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

[GitHub] [shardingsphere] zhang-zhipeng commented on issue #21799: mybatis-plus3.5.2、shardingsphere5.1.1（Using encrypt）、oracle，When querying by page,rewirte sql is error, regards the ROWNUM as a real field, but it is not

Posted by GitBox <gi...@apache.org>.

zhang-zhipeng commented on issue #21799:
URL: https://github.com/apache/shardingsphere/issues/21799#issuecomment-1323483865

   @strongduanmu okay


-- 
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