You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "waynesura (via GitHub)" <gi...@apache.org> on 2026/09/18 07:14:24 UTC
[I] CTE-UnknownDatabaseException [shardingsphere]
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] CTE-UnknownDatabaseException [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #39885:
URL: https://github.com/apache/shardingsphere/issues/39885#issuecomment-5754615611
Hi @waynesura, thanks for the detailed reproduction. This is a duplicate of an SQL binder bug present in 5.5.2 and already fixed by [#37914](https://github.com/apache/shardingsphere/pull/37914).
Your trigger analysis is essentially correct, but the immediate failure is an empty bound database name rather than the CTE alias `a` itself. For an expression projection such as `1 AS code`, 5.5.2 propagates empty database/schema binding information to the qualified outer reference. `TablesContext` then records the empty database name, and `SelectStatementContext#getTableMapperRuleAttributes` attempts to resolve it, resulting in `UnknownDatabaseException`.
PR #37914 filters empty database and schema bindings in `TablesContext`. The change is included in [ShardingSphere 5.5.3](https://github.com/apache/shardingsphere/releases/tag/5.5.3).
Please upgrade to 5.5.3 or later and verify the same SQL. If it still fails, please provide the new version and stack trace so we can investigate the remaining path.
Recommended labels: `type: duplicate`, `in: SQL bind`. We can close this issue as duplicate of #37914.
--
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
Re: [I] CTE-UnknownDatabaseException [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #39885: CTE-UnknownDatabaseException
URL: https://github.com/apache/shardingsphere/issues/39885
--
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