YM-Sky opened a new issue, #39120:
URL: https://github.com/apache/shardingsphere/issues/39120
## Bug Report
### Which version of ShardingSphere did you use?
Apache ShardingSphere-JDBC 5.5.3.
Related modules:
- `shardingsphere-jdbc`: 5.5.3
- `shardingsphere-jdbc-dialect-mysql`: 5.5.3
- `shardingsphere-sharding-mysql`: 5.5.3
Environment:
- Java 17
- MySQL dialect
- SQL Federation enabled
- Apache Calcite resolved as 1.40.0
- MyBatis-Plus pagination can trigger this, but plain JDBC
`PreparedStatement#setLong` can also reproduce it.
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
ShardingSphere-JDBC.
### Expected behavior
A SQL Federation query using MySQL dialect should execute successfully when
`LIMIT ?` is bound with a numeric JDBC parameter such as `Long`.
For example:
```java
PreparedStatement ps = connection.prepareStatement("""
SELECT so.id AS id, u.name AS record_user_name
FROM ims_sales_order so
LEFT JOIN mnt_base_user u ON so.record_user_id = u.id
WHERE so.tenant_id = 1
ORDER BY so.create_time DESC
LIMIT ?
""");
ps.setLong(1, 20L);
ps.executeQuery();
```
### Actual behavior
The query enters SQL Federation and fails with:
```text
SQL Federation execute failed, sql ... ORDER BY so.create_time DESC LIMIT ?,
parameters [20],
reason class java.lang.Long cannot be cast to class java.lang.Integer
```
Stack trace excerpt:
```text
java.sql.SQLException: class java.lang.Long cannot be cast to class
java.lang.Integer
at
org.apache.shardingsphere.infra.exception.external.sql.ShardingSphereSQLException.toSQLException(ShardingSphereSQLException.java:81)
at
org.apache.shardingsphere.database.exception.core.SQLExceptionTransformEngine.toSQLException(SQLExceptionTransformEngine.java:54)
at
org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.execute(ShardingSpherePreparedStatement.java:244)
```
Observed SQL log:
```text
Preparing: SELECT so.id AS id, u.name AS record_user_name
FROM ims_sales_order so
LEFT JOIN mnt_base_user u ON so.record_user_id = u.id AND u.tenant_id = 1
AND u.deleted = 0
WHERE so.tenant_id = 1 AND so.deleted = 0
ORDER BY so.create_time DESC LIMIT ?
Parameters: 20(Long)
SQL Federation Execution Plan:
EnumerableLimit(fetch=[?0])
EnumerableSort(...)
EnumerableHashJoin(..., joinType=[left])
```
### Reason analyze (If you can)
I checked the ShardingSphere 5.5.3 source locally.
Source location:
```text
D:\source\shardingsphere\shardingsphere-5.5.3\kernel\sql-federation\core\src\main\java\org\apache\shardingsphere\sqlfederation\engine\processor\impl\StandardSQLFederationProcessor.java
```
`createParameters` copies query parameters as-is:
```java
private Map<String, Object> createParameters(final List<Object> params) {
Map<String, Object> result = new HashMap<>(params.size(), 1F);
int index = 0;
for (Object each : params) {
result.put("?" + index++, each);
}
return result;
}
```
Then this source:
```text
D:\source\shardingsphere\shardingsphere-5.5.3\kernel\sql-federation\executor\src\main\java\org\apache\shardingsphere\sqlfederation\executor\context\ExecutorBindContext.java
```
returns the parameter value as-is:
```java
@Override
public Object get(final String name) {
return parameters.get(name);
}
```
But Calcite `EnumerableLimit#getExpression` converts dynamic `LIMIT/OFFSET`
parameters to `Integer`:
```text
calcite-core-1.40.0
org/apache/calcite/adapter/enumerable/EnumerableLimit.java
```
```java
return Expressions.convert_(
Expressions.call(DataContext.ROOT,
BuiltInMethod.DATA_CONTEXT_GET.method,
Expressions.constant("?" + param.getIndex())),
Integer.class);
```
So if the JDBC parameter is `Long`, SQL Federation eventually causes a `Long
-> Integer` cast failure.
MyBatis-Plus MySQL pagination binds pagination parameters as `long.class`,
which seems reasonable because page size/current values are long values.
Checked source:
```text
D:\source\mybatis-plus\mybatis-plus-3.5.3.2\mybatis-plus-extension\src\main\java\com\baomidou\mybatisplus\extension\plugins\pagination\DialectModel.java
```
```java
new ParameterMapping.Builder(configuration, FIRST_PARAM_NAME,
long.class).build()
new ParameterMapping.Builder(configuration, SECOND_PARAM_NAME,
long.class).build()
```
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule
configuration, when exception occur etc.
Use ShardingSphere-JDBC 5.5.3 with MySQL dialect and SQL Federation enabled.
Sanitized YAML, preserving the real rule structure:
```yaml
databaseName: logic_db
mode:
type: Standalone
repository:
type: JDBC
transaction:
defaultType: LOCAL
dataSources:
ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl:
jdbc:mysql://127.0.0.1:3306/demo_master?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
username: root
password: password
ds_1:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver
jdbcUrl:
jdbc:mysql://127.0.0.1:3306/demo_sharding_1?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
username: root
password: password
sqlFederation:
sqlFederationEnabled: true
allQueryUseSQLFederation: false
executionPlanCache:
initialCapacity: 2000
maximumSize: 65535
props:
sql-show: true
rules:
- !SINGLE
tables:
- ds_0.mnt_base_user
- !SHARDING
tables: {}
defaultDatabaseStrategy:
complex:
shardingColumns: tenant_id
shardingAlgorithmName: custom_database_tenant_sharding_algorithm
defaultTableStrategy:
complex:
shardingColumns: tenant_id
shardingAlgorithmName: tenant_sharding_algorithm
shardingAlgorithms:
custom_database_tenant_sharding_algorithm:
type: CLASS_BASED
props:
strategy: COMPLEX
algorithmClassName:
com.example.sharding.CustomDatabaseTenantIdShardingAlgorithm
dbPrefix: ds_
tenantIdFiled: tenant_id
tenant_sharding_algorithm:
type: CLASS_BASED
props:
strategy: COMPLEX
algorithmClassName:
com.example.sharding.CustomTableTenantIdShardingAlgorithm
tenantIdFiled: tenant_id
```
Run a query joining a sharded table and a single table:
```sql
SELECT so.id AS id, u.name AS record_user_name
FROM ims_sales_order so
LEFT JOIN mnt_base_user u ON so.record_user_id = u.id
WHERE so.tenant_id = 1
ORDER BY so.create_time DESC
LIMIT ?
```
Bind the parameter as Java `Long`, for example `20L`.
### Example codes for reproduce this issue (such as a github link).
```java
try (Connection connection = dataSource.getConnection();
PreparedStatement ps = connection.prepareStatement("""
SELECT so.id AS id, u.name AS record_user_name
FROM ims_sales_order so
LEFT JOIN mnt_base_user u ON so.record_user_id = u.id
WHERE so.tenant_id = 1
ORDER BY so.create_time DESC
LIMIT ?
""")) {
ps.setLong(1, 20L);
ps.executeQuery();
}
```
### Suggested fix
SQL Federation should normalize dynamic parameters used by Calcite
`EnumerableLimit` fetch/offset before binding, or otherwise adapt `Number`
values to the type expected by Calcite.
Possible directions:
- If a dynamic parameter is used as `LIMIT/OFFSET` and the value is
`Number`, convert it to `Integer` when it is within integer range.
- Or make the SQL Federation `DataContext`/parameter binding layer return a
compatible type for Calcite's generated `EnumerableLimit` expression.
- Or update the generated expression path so numeric fetch/offset parameters
are accepted consistently.
Application pagination frameworks should not need ShardingSphere-specific
parameter conversion.
--
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]
