SQL that contains encrypt column in subquery and uses asterisks for outer projection is not supported.

[WangSenkai]

Is your feature request related to a problem?

yes

Describe the feature you would like.

I used shardingsphere-jdbc-5.4.0（using Encryption）、Mybatis-PageHelper、Oracle in my project, However, when querying in paging, an error was reported due to the this problem , SQL that contains encrypt column in subquery and uses asterisks for outer projection is not supported.

Regarding this issue, will there be support in the future?

[WangSenkai]

Or is there any other solution currently?

[strongduanmu]

Can you use columns instead of asterisks when use encrypt feature?

[WangSenkai]

Can you use columns instead of asterisks when use encrypt feature?

It comes with the Mybatis-PageHelper framework, I have no way to change it

[strongduanmu]

Can you show the sql generated by PageHelper framework? And provide your encrypt config, init sql script.

[WangSenkai]

Can you show the sql generated by PageHelper framework? And provide your encrypt config, init sql script.

dataSources:
  unique_ds:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: oracle.jdbc.OracleDriver
    url: jdbc:oracle:thin:@192.168.0.1:1521:xxxx
    username: xxxx
    password: xxxx

rules:
  - !ENCRYPT
    tables:
      user_info:
        columns:
          name:
            cipher:
              name: name_encrypt
              encryptorName: i_encryptor
            likeQuery:
              name: name_like
              encryptorName: like_encryptor
    encryptors:
      i_encryptor:
        type: IZ
        props:
          sm4-key: f6ea011b68a74b379d178e6b3147078a
          sm4-iv: b3da78e63b814ad18c8c99633edb7a4c
      like_encryptor:
        type: CHAR_DIGEST_LIKE
props:
  sql-show: true

SELECT
    * 
FROM
    (
    SELECT
        TMP_PAGE.*,
        ROWNUM ROW_ID 
    FROM
        (
        SELECT
            a.id,
            a.name,
            a.email,
            b.username
        FROM
            user_info a
            LEFT JOIN s_user b ON a.agent_id = b.id
        WHERE a.email = '123'
        ORDER BY
            create_time DESC 
        ) TMP_PAGE 
    ) 
WHERE
    ROW_ID <= 3 
    AND ROW_ID > 1

CREATE TABLE "USER_INFO" (
  "ID" VARCHAR(255) NOT NULL,
  "NAME" VARCHAR2(255),
  "EMAIL" VARCHAR2(255),
  "AGENT_ID" VARCHAR2(255),
  "CREATE_TIME" TIMESTAMP,
  PRIMARY KEY ("ID")
);

CREATE TABLE "S_USER" (
  "ID" VARCHAR(255) NOT NULL,
  "USERNAME" VARCHAR2(255),
  PRIMARY KEY ("ID")
);

[strongduanmu]

@WangSenkai Thank you for providing this information. Currently, this statement is not supported. The semantics in the subquery are relatively complex. Is it possible to implement this kind of SQL by handwriting?

[WangSenkai]

@WangSenkai Thank you for providing this information. Currently, this statement is not supported. The semantics in the subquery are relatively complex. Is it possible to implement this kind of SQL by handwriting?

OK, thanks. There is also a question, does this framework support PolarDB PostgreSQL version (compatible with Oracle).

This is the document address: https://help.aliyun.com/zh/polardb/polardb-for-oracle/what-is-polardb-for-oracle

[strongduanmu]

Currently, the ShardingSphere supports PostgreSQL databases, and if PolarDB PostgreSQL were fully compatible with PostgreSQL, I think it would be supported.

[WangSenkai]

PolarDB PostgreSQL has its own driver package. In this case, will it be incompatible?

[terrymanu]

No plan to do it