terrymanu commented on issue #37757:
URL:
https://github.com/apache/shardingsphere/issues/37757#issuecomment-3769144014
### Problem Understanding
- In legacy schemas, columns are CHAR/VARCHAR while Java fields are
numeric or boolean (e.g., MyBatis-Plus enums as Integer). With the MySQL
driver, getObject(index, Integer.class) converts numeric strings to Integer;
through ShardingSphere JDBC, ResultSetUtils only parses date/time strings, so
conversion fails and throws “unsupported”.
### Root Cause
-
infra/executor/src/main/java/org/apache/shardingsphere/infra/executor/sql/execute/result/query/impl/driver/jdbc/type/util/ResultSetUtils.java
(convertValue) only supports string-to-Timestamp/Date/Time; it lacks
string-to-numeric/boolean parsing.
- By design, ShardingSphere enforces cross-dialect consistent typing and
does not replicate each driver’s lenient conversions, so CHAR/VARCHAR →
numeric/boolean requests are rejected.
### Problem Analysis
- The current behavior is “strict type matching” by design, not an
omission; but in MySQL dialect we miss a compatible conversion similar to the
native driver, making migrations harder when strings store numbers.
- To offer dialect-aware lenient conversion, we need an extension point; a
global relaxation risks divergent behavior on PostgreSQL/SQLServer, etc.
- The conversion path currently does not know the dialect; we need to
propagate DatabaseType into convertValue and pick converters accordingly.
### Problem Conclusion (design-level fix suggestion; PRs welcome)
- Treat this as a compatibility enhancement while keeping strict defaults:
add pluggable, dialect-specific string→target-type conversion. Suggested design:
1. Introduce a dialect converter SPI, e.g.:
```java
public interface DialectValueConverter {
boolean supports(DatabaseType databaseType);
Optional<Object> convert(Object value, Class<?> targetType);
}
```
2. In ResultSetUtils.convertValue, invoke the SPI: if a converter
returns a result, use it; otherwise fallback to current logic. Pass
DatabaseType from the QueryResult / MergedResult context into convertValue.
3. Provide a MySQL implementation that mirrors a safe subset of the
driver:
- Support String →
Byte/Short/Integer/Long/Float/Double/BigDecimal/Boolean (including primitives).
- Rules: trim first; parse signed/unsigned numeric strings via new
BigDecimal; boolean accepts t/y/1/true, etc.; empty or invalid strings throw
UnsupportedDataTypeConversionException to avoid silent errors.
4. Keep default behavior unchanged (strict) when no dialect match or
compatibility is not enabled, preserving users who rely on the current
exception.
5. Add unit tests:
- MySQL dialect success cases for string-to-numeric/boolean.
- Invalid/empty strings raise exceptions.
- Non-MySQL dialect stays with current exceptions.
- Warm invitation to community contributors: please submit a PR with the
implementation and tests so we can discuss precise rules and switches, ensuring
compatibility and cross-dialect consistency.
- To help validate, please share a minimal repro (DDL, sample data, SQL),
runtime mode (JDBC/Proxy), database and driver versions, and your expected
handling of invalid inputs.
--
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]
