Integer CastException occur when select in subquery

[MaricoHan]

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

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

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-Proxy

Expected behavior

select successfully

select * from t_accounts where id in (select max(id) from t_accounts group by project_id);

Actual behavior

[ERROR] 2022-10-31 09:34:26.811 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur: 
java.sql.SQLException: exception while executing query: class java.math.BigInteger cannot be cast to class java.lang.Long (java.math.BigInteger and java.lang.Long are in module java.base of loader 'bootstrap')
	at org.apache.calcite.avatica.Helper.createException(Helper.java:56)
	at org.apache.calcite.avatica.Helper.createException(Helper.java:41)
	at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:579)
	at org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137)
	at org.apache.shardingsphere.infra.federation.executor.original.OriginalFederationExecutor.executeQuery(OriginalFederationExecutor.java:87)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecuteFederation(JDBCDatabaseCommunicationEngine.java:165)
	at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:123)
	at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:94)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110)
	at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.lang.ClassCastException: class java.math.BigInteger cannot be cast to class java.lang.Long (java.math.BigInteger and java.lang.Long are in module java.base of loader 'bootstrap')
	at Baz$4.apply(Unknown Source)
	at Baz$4.apply(Unknown Source)
	at org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:981)
	at org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:781)
	at org.apache.calcite.linq4j.DefaultEnumerable.groupBy(DefaultEnumerable.java:311)
	at Baz.bind(Unknown Source)
	at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:363)
	at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:323)
	at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:578)
	at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:569)
	at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:184)
	at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
	at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
	at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:575)
	... 10 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).

[strongduanmu]

Hi @MaricoHan, can you provide your configuration and table init sql?

[MaricoHan]

Hi @MaricoHan, can you provide your configuration and table init sql?

Hi @strongduanmu,as shown below:

Create Table: CREATE TABLE `t_accounts` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `project_id` bigint unsigned NOT NULL DEFAULT '0' ,
  `address` char(42) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uidx_address` (`address`) USING BTREE,
  KEY `idx_project_id` (`project_id`) USING BTREE,
  KEY `idx_created_at` (`created_at`) USING BTREE,
  KEY `idx_updated_at` (`updated_at`) USING BTREE,
) ENGINE=InnoDB AUTO_INCREMENT=355479852 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

server.yaml

props:
 max-connections-size-per-query: 1
 kernel-executor-size: 16  # Infinite by default.
 proxy-frontend-flush-threshold: 128  # The default value is 128.
 proxy-hint-enabled: false
 sql-show: false
 check-table-metadata-enabled: false
   # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
   # The default value is -1, which means set the minimum value for different JDBC drivers.
 proxy-backend-query-fetch-size: -1
 proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
   # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
   # and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.
 proxy-backend-executor-suitable: OLAP
 proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
 sql-federation-enabled: true
   # Available proxy backend driver type: JDBC (default), ExperimentalVertx
 proxy-backend-driver-type: JDBC
 proxy-mysql-default-version: 8.0.23 # In the absence of schema name, the default version will be used.
 proxy-default-port: 3307 # Proxy default port.
 proxy-netty-backlog: 1024 # Proxy netty backlog.