Recursive query logic table times error：Table or view 'tmp' does not exist.

[yyyyyyyysssss]

version:

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

sharding.yaml：
mode:
  type: Standalone
  repository:
    type: JDBC
dataSources:
  master:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/im?useUnicode=true&allowPublicKeyRetrieval=true&useSSL=false&useLegacyDatetimeCode=false&allowMultiQueries=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: ***
    password: ***
    autoCommit: true
    connectionTimeout: 30000
    idleTimeout: 0
    keepaliveTime: 300000
    maxLifetime: 1800000
    minimumIdle: 5
    maximumPoolSize: 50
  slave0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://127.0.0.1:3306/im?useUnicode=true&allowPublicKeyRetrieval=true&useSSL=false&useLegacyDatetimeCode=false&allowMultiQueries=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
    username: ***
    password: ***
    autoCommit: true
    connectionTimeout: 30000
    idleTimeout: 0
    keepaliveTime: 300000
    maxLifetime: 1800000
    minimumIdle: 5
    maximumPoolSize: 50

rules:
  - !SINGLE
    tables:
      - "*.*"
  - !READWRITE_SPLITTING
    dataSources:
      readwrite_ds:
        writeDataSourceName: master
        readDataSourceNames:
          - slave0
        transactionalReadQueryStrategy: PRIMARY
        loadBalancerName: roundRobin
    loadBalancers:
      roundRobin:
        type: ROUND_ROBIN

props:
  sql-show: true

execute sql:

WITH RECURSIVE tmp as (
SELECT t.* FROM im_authority t WHERE t.id = #{}
UNION ALL
SELECT f.* FROM im_authority f INNER JOIN tmp on f.parent_id = tmp.id
) select * from tmp

[naah69]

we had the same problem when use WITH

[linghengqian]

• CTE for MySQL is actually supported via SQL Federation at https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/rules/sql-federation/ . But for a wider range of databases, this actually requires Calcite 1.36 which adds recursive with parsing support. That is https://issues.apache.org/jira/browse/CALCITE-129 . But due to the merge of https://github.com/apache/calcite/pull/3429 , ShardingSphere is not compatible with Calcite 1.36, which is reflected in CI #29229.

• Feel free to raise your PR to fix it.

[naah69]

• CTE for MySQL is actually supported via SQL Federation at https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/rules/sql-federation/ . But for a wider range of databases, this actually requires Calcite 1.36 which adds recursive with parsing support. That is https://issues.apache.org/jira/browse/CALCITE-129 . But due to the merge of [CALCITE-6006] RelToSqlConverter loses charset information calcite#3429 , ShardingSphere is not compatible with Calcite 1.36, which is reflected in CI Updates Calcite to 1.36.0 to further support the WITH RECURSIVE keyword #29229.MySQL 的 CTE 实际上是通过 SQL Federation 支持的，网址为https://shardingsphere.apache.org/document/current/cn/user-manual/shardingsphere-jdbc/yaml-config/rules/sql-federation/ 。但对于更广泛的数据库，这实际上需要 Calcite 1.36，它添加了recursive with解析支持。那是https://issues.apache.org/jira/browse/CALCITE-129 。但由于合并 [CALCITE-6006] RelToSqlConverter loses charset information calcite#3429 ，ShardingSphere不兼容Calcite 1.36，体现在CI中 Updates Calcite to 1.36.0 to further support the WITH RECURSIVE keyword #29229 。
• Feel free to raise your PR to fix it. @linghengqian sorry，I leaved out some infomation. In my case，we use postgresql and WITH,it also throwed exception that is about Table or view 'xxx' does not exist

[linghengqian]

• @naah69 I believe a Postgresql dialect-specific implementation of the SQL standard's WITH [RECURSIVE] syntax has been discussed at https://issues.apache.org/jira/browse/CALCITE-129 and https://github.com/apache/calcite/pull/3480 .
• This relates to https://www.postgresql.org/docs/current/queries-with.html#QUERIES-WITH-RECURSIVE and https://github.com/apache/calcite/pull/3480#discussion_r1369643978 .
• The problem involved here is that Calcite 1.36.0 breaks the unit test of ShardingSphere, because starting from Calcite 1.36.0 , charset information in SQL began to be parsed correctly. Refer to https://github.com/apache/shardingsphere/pull/29229 and https://github.com/apache/calcite/pull/3429 .
• You can submit a PR to fix this issue at any time, or ask the relevant committer how to fix the unit test of ShardingSphere's SQL Federation. I am not the main contributor of the SQL Federation feature.
• The general understanding is to update calcite to 1.36.0 and fix the unit test, and then call the relevant calcite internal API in shardingsphere to support parsing WITH [RECURSIVE] syntax.

[linghengqian]

https://github.com/apache/shardingsphere/pull/33279 has been merged. Since you did not provide a minimal reproducible example git with unit tests, you need to use the master branch for testing.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[linghengqian]

Cannot reproduce on master branch. If the problem still exists, please provide a git with unit tests.