JamesRTaylor opened a new issue, #31657:
URL: https://github.com/apache/shardingsphere/issues/31657
## Feature Request
### Is your feature request related to a problem?
CTEs are not considered when rewriting a logical query with Postgres.
Instead a TableNotFoundException occurs when trying to select from the CTE. To
reproduce, add the following into
src/test/resources/scenario/sharding/case/dml/select.xml and run
ShardingSQLRewriterIT:
<rewrite-assertion id="select_from_cte">
<input sql="WITH ok_account AS (SELECT * FROM t_account WHERE status
= 'OK') SELECT * FROM ok_account WHERE account_id = ?" parameters="100" />
<output sql="WITH ok_account AS (SELECT * FROM t_account_0 WHERE
status = 'OK') SELECT * FROM ok_account WHERE account_id = ?" parameters="100"
/>
</rewrite-assertion>
The following exception occurs for PostgreSQL:
org.apache.shardingsphere.infra.exception.kernel.metadata.TableNotFoundException:
Table or view 'ok_account' does not exist.
at
org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.lambda$checkTableExists$4(SimpleTableSegmentBinder.java:151)
at
org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:44)
at
org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.checkTableExists(SimpleTableSegmentBinder.java:148)
at
org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.bind(SimpleTableSegmentBinder.java:84)
at
org.apache.shardingsphere.infra.binder.segment.from.TableSegmentBinder.bind(TableSegmentBinder.java:55)
at
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.lambda$bind$1(SelectStatementBinder.java:60)
at java.base/java.util.Optional.map(Optional.java:260)
at
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:60)
at
org.apache.shardingsphere.infra.binder.statement.dml.SelectStatementBinder.bind(SelectStatementBinder.java:48)
at
org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindDMLStatement(SQLBindEngine.java:82)
at
org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:72)
at
org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:63)
at
org.apache.shardingsphere.test.it.rewrite.engine.SQLRewriterIT.createSQLRewriteUnits(SQLRewriterIT.java:143)
at
org.apache.shardingsphere.test.it.rewrite.engine.SQLRewriterIT.assertRewrite(SQLRewriterIT.java:106)
at java.base/java.lang.reflect.Method.invoke(Method.java:568)
at
java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
at
java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at
java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179)
at
java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at
java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
at
java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at
java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at
java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at
java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
at
java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at
java.base/java.util.LinkedList$LLSpliterator.forEachRemaining(LinkedList.java:1242)
at
java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
at
java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
at
java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
at
java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
at
java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at
java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
at
java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
at
java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at
java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at
java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
at
java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625)
at
java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
at
java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
at
java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
at
java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
at
java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at
java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
at
java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
at
java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625)
at
java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
at
java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
at
java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
at
java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
at
java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
at
java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
at java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
### Describe the feature you would like.
With MySQL, the exception doesn't occur, however the query isn't rewritten
correctly. Instead, the output query is the same as the input query. Instead,
the query should be rewritten as shown above.
--
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]
