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

Open
#22,384 0 comments 0 reactions 1 assignee Claimed by @strongduanmu View on GitHub
feature: SQL federation type: bug
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.