apache / apache/shardingsphere
Conversion to relational algebra failed to preserve datatypes exception occurs when I execute `SELECT o.*, * FROM t_order o FULL JOIN t_order_item i using(user_id, order_id) WHERE o.user_id = 10 ORDER BY o.order_id;` with sql federation
- Dominant language
- Java
- Stars
- 20.8k
- Forks
- 6.9k
- Avg merge
- 11h 35m
- Merged PRs (30d)
- 326
Description
## Bug Report
### Which version of ShardingSphere did you use?
https://github.com/apache/shardingsphere/commit/443899a9a09daf6ac74026a0794c61c8d51d22c6
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-Proxy
### Expected behavior
Execute `SELECT o.*, * FROM t_order o FULL JOIN t_order_item i using(user_id, order_id) WHERE o.user_id = 10 ORDER BY o.order_id;` successfully.
### Actual behavior
```
[ERROR] 2022-11-24 16:11:27.355 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
java.lang.RuntimeException: java.lang.AssertionError: Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) status, JavaType(class java.lang.Integer) merchant_id, JavaType(class java.lang.String) remark, JavaType(class java.sql.Date) creation_date, JavaType(class java.lang.Integer) order_id0, JavaType(class java.lang.Integer) merchant_id0, JavaType(class java.lang.String) remark0, JavaType(class java.sql.Date) creation_date0) NOT NULL
converted type:
RecordType(INTEGER user_id, JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) status, JavaType(class java.lang.Integer) merchant_id, JavaType(class java.lang.String) remark, JavaType(class java.sql.Date) creation_date, JavaType(class java.lang.Integer) order_id0, JavaType(class java.lang.Integer) merchant_id0, JavaType(class java.lang.String) remark0, JavaType(class java.sql.Date) creation_date0) NOT NULL
rel:
LogicalSort(sort0=[$6], dir0=[ASC])
LogicalProject(user_id=[COALESCE($1, CAST($2):INTEGER)], order_id=[COALESCE($0, $1)], status=[$2], merchant_id=[$3], remark=[$4], creation_date=[$5], order_id0=[$0], merchant_id0=[$3], remark0=[$4], creation_date0=[$5])
LogicalFilter(condition=[=(CAST($1):INTEGER, 10)])
LogicalJoin(condition=[AND(=($1, $8), =($0, $7))], joinType=[full])
LogicalTableScan(table=[[public, t_order]])
LogicalTableScan(table=[[public, t_order_item]])
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:86)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.lang.AssertionError: Conversion to relational algebra failed to preserve datatypes:
validated type:
RecordType(JavaType(class java.lang.Integer) user_id, JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) status, JavaType(class java.lang.Integer) merchant_id, JavaType(class java.lang.String) remark, JavaType(class java.sql.Date) creation_date, JavaType(class java.lang.Integer) order_id0, JavaType(class java.lang.Integer) merchant_id0, JavaType(class java.lang.String) remark0, JavaType(class java.sql.Date) creation_date0) NOT NULL
converted type:
RecordType(INTEGER user_id, JavaType(class java.lang.Integer) order_id, JavaType(class java.lang.String) status, JavaType(class java.lang.Integer) merchant_id, JavaType(class java.lang.String) remark, JavaType(class java.sql.Date) creation_date, JavaType(class java.lang.Integer) order_id0, JavaType(class java.lang.Integer) merchant_id0, JavaType(class java.lang.String) remark0, JavaType(class java.sql.Date) creation_date0) NOT NULL
rel:
LogicalSort(sort0=[$6], dir0=[ASC])
LogicalProject(user_id=[COALESCE($1, CAST($2):INTEGER)], order_id=[COALESCE($0, $1)], status=[$2], merchant_id=[$3], remark=[$4], creation_date=[$5], order_id0=[$0], merchant_id0=[$3], remark0=[$4], creation_date0=[$5])
LogicalFilter(condition=[=(CAST($1):INTEGER, 10)])
LogicalJoin(condition=[AND(=($1, $8), =($0, $7))], joinType=[full])
LogicalTableScan(table=[[public, t_order]])
LogicalTableScan(table=[[public, t_order_item]])
at org.apache.calcite.sql2rel.SqlToRelConverter.checkConvertedType(SqlToRelConverter.java:492)
at org.apache.calcite.sql2rel.SqlToRelConverter.convertQuery(SqlToRelConverter.java:607)
at org.apache.shardingsphere.sqlfederation.optimizer.SQLOptimizeEngine.optimize(SQLOptimizeEngine.java:50)
at org.apache.shardingsphere.sqlfederation.advanced.AdvancedSQLFederationExecutor.execute(AdvancedSQLFederationExecutor.java:151)
at org.apache.shardingsphere.sqlfederation.advanced.AdvancedSQLFederationExecutor.executeQuery(AdvancedSQLFederationExecutor.java:119)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecuteFederation(JDBCDatabaseCommunicationEngine.java:164)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:122)
at org.apache.shardingsphere.proxy.frontend.opengauss.command.query.simple.OpenGaussComQueryExecutor.execute(OpenGaussComQueryExecutor.java:76)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:111)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:78)
... 3 common frames omitted
```
### Reason analyze (If you can)
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
### Example codes for reproduce this issue (such as a github link).
Contributor guide
Assessment
This issue has not been assessed yet.