waynesura opened a new issue, #39885: URL: https://github.com/apache/shardingsphere/issues/39885
## 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.5.2 ```xml <dependency> <groupId>org.apache.shardingsphere</groupId> <artifactId>shardingsphere-jdbc</artifactId> <version>5.5.2</version> </dependency> ``` ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy? ShardingSphere-JDBC ### Expected behavior A CTE whose projection column is a constant should bind successfully when the outer query references it with a CTE-qualified column name. ```sql with a as (select 1 as code from t_order) select a.code from a ``` The query should execute and return `1`. ### Actual behavior `UnknownDatabaseException` is thrown during the **bind** phase, before any routing happens. Note the exception is raised from `Connection.prepareStatement(...)` (because `SQLBindEngine.bind` is invoked inside the `ShardingSpherePreparedStatement` constructor), and it is a `RuntimeException`, not a `SQLException`. In a MyBatis application it surfaces as: ``` org.mybatis.spring.MyBatisSystemException: ### Error querying database. Cause: org.apache.ibatis.executor.ExecutorException: Error preparing statement. Cause: org.apache.shardingsphere.infra.exception.dialect.exception.syntax.database.UnknownDatabaseException ``` Full stack trace: ``` org.apache.shardingsphere.infra.exception.dialect.exception.syntax.database.UnknownDatabaseException at SelectStatementContext.lambda$getTableMapperRuleAttributes$0(SelectStatementContext.java:186) at ShardingSpherePreconditions.checkNotNull(ShardingSpherePreconditions.java:58) at SelectStatementContext.getTableMapperRuleAttributes(SelectStatementContext.java:186) at SelectStatementContext.isContainsEnhancedTable(SelectStatementContext.java:170) at SelectStatementContext.isContainsEnhancedTable(SelectStatementContext.java:162) at SelectStatementContext.<init>(SelectStatementContext.java:130) at SelectStatementContext.createSubqueryContexts(SelectStatementContext.java:195) at SelectStatementContext.<init>(SelectStatementContext.java:124) at SQLStatementContextFactory.getDMLStatementContext(SQLStatementContextFactory.java:147) at SQLStatementContextFactory.newInstance(SQLStatementContextFactory.java:131) at SQLBindEngine.bind(SQLBindEngine.java:59) at ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:143) at ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:115) at ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:108) ``` ### Reason analyze (If you can) **This is NOT a configuration issue.** The logical database name is correctly registered and resolvable — we verified this explicitly: ```java conn.getSchema(); // -> "demo_ds" (correct logical db name) metaData.getAllDatabases(); // -> [demo_ds] metaData.getDatabase("demo_ds"); // -> ShardingSphereDatabase (NOT null) ``` So the `UnknownDatabaseException` here is **not** caused by a mismatch between the connection database name and the registered database name. The real trigger is the combination of the following **three** conditions: 1. The SQL contains a CTE (`WITH ... AS (...)`); 2. The outer query references a CTE column **with the CTE alias qualifier** (e.g. `a.code`); 3. That projected column **does not originate from any real table** — e.g. a constant, an expression, or a function result. When condition 3 holds, ShardingSphere cannot determine the table ownership of `a.code`. It then **mistakes the CTE alias `a` for the database (schema owner) the column belongs to**, and passes it as `databaseName` into `getTableMapperRuleAttributes`. Since no logical database named `a` exists, `metaData.getDatabase("a")` returns `null` and `checkNotNull` throws `UnknownDatabaseException`. We bisected the problem and confirmed the following behavior matrix: | SQL | Result | |---|---| | `with a as (select 1 as code from t_order) select a.code from a` | **FAILS** (`UnknownDatabaseException`) | | `with a as (select id as code from t_order) select a.code from a` | works (projection comes from a real table) | | `with a as (select 1 as code from t_order) select code from a` | works (unqualified column reference) | | `with a as (select 1 as code from t_order) select * from a` | works (`SELECT *`) | | `select a.code from (select 1 as code from t_order) a` | works (plain subquery instead of CTE) | Additional findings: - The bug is unrelated to the **number** of CTEs (a single CTE already triggers it). - The bug is unrelated to the **alias string** (`a`, `my_alias`, etc. all trigger it). - Merely adding a real-table column to the CTE (e.g. `select 1 as code, id as rid from t_order`) does **not** work around it, as long as the outer reference is still the non-real-table column `a.code`. - Replacing the CTE with an equivalent plain subquery (`FROM (...) a`) avoids the problem, which indicates the issue lies in the CTE handling path (i.e. how `SubqueryExtractor` / `createSubqueryContexts` processes CTE segments) rather than in general subquery handling. ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc. **Table**: ```sql CREATE TABLE t_order (id INT PRIMARY KEY, code VARCHAR(50)); ``` **ShardingSphere configuration** — a single logical database backed by a single datasource is sufficient. No sharding/encrypt/readwrite-splitting rules are required: ```yaml databaseName: demo_ds dataSources: ds_1: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: org.h2.Driver jdbcUrl: jdbc:h2:mem:demo_ds;DB_CLOSE_DELAY=-1 username: sa password: "" rules: - !SINGLE tables: - "*.*" ``` **Reproduce** — the exception is thrown at `prepareStatement`, so no data is needed in the table: ```java Class.forName("org.apache.shardingsphere.driver.ShardingSphereDriver"); try (Connection conn = DriverManager.getConnection( "jdbc:shardingsphere:classpath:sharding.yaml", "sa", ""); PreparedStatement ps = conn.prepareStatement( "with a as (select 1 as code from t_order) select a.code from a")) { // -> UnknownDatabaseException is thrown here, inside the PreparedStatement constructor try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { System.out.println(rs.getString(1)); } } } ``` ### Example codes for reproduce this issue (such as a github link). N/A — the minimal SQL and configuration above are sufficient to reproduce. -- 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]
