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

[strongduanmu]

Bug Report

Which version of ShardingSphere did you use?

443899a

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).