zhengxiang378928908-code opened a new issue, #12841:
URL: https://github.com/apache/seatunnel/issues/12841
### Search before asking
- [x] I searched in the issues and found nothing similar.
### What happened
Split out of #12814, where the Spark wide-decimal policy is discussed. That
issue explicitly keeps this source-side clamp out of scope because it happens
on every engine, not only Spark.
`MySqlTypeConverter.convert` maps any signed `DECIMAL(p, s)` with `p > 38`
to `DECIMAL(38, 18)`, whatever the original scale:
```java
case MYSQL_DECIMAL:
...
if (typeDefine.getPrecision() > DEFAULT_PRECISION) { // 38
log.warn("{} will probably cause value overflow.", MYSQL_DECIMAL);
decimalType = new DecimalType(DEFAULT_PRECISION, DEFAULT_SCALE); //
(38, 18)
}
```
From reading the current `dev` code (`51f553cb4`, not yet reproduced end to
end), this causes:
1. **Integer digits are cut to 20.** `DECIMAL(65, 0)` becomes `DECIMAL(38,
18)`, which has room for only 20 integer digits. A column declared `DECIMAL(30,
0)` loses 10 integer digits, even though it needs no scale at all.
2. **Fraction digits above 18 are cut.** `DECIMAL(50, 30)` becomes scale 18.
3. **The declared type no longer matches the values.** The JDBC row
converter reads the value with `getBigDecimal` and does not rescale it
(`AbstractJdbcRowConverter`, `case DECIMAL`), so a row can carry e.g. a
26-digit integer while the column type says `DECIMAL(38, 18)`. The mismatch
shows up later in the sink, far from its cause.
4. **It even breaks MySQL -> MySQL.** MySQL supports precision up to 65, and
the MySQL sink side (`reconvert`) accepts up to `MAX_PRECISION = 65`. But an
auto-created sink table gets `DECIMAL(38, 18)`, so values with more than 20
integer digits fail with `Out of range value` in strict mode, or are silently
clamped to `99999999999999999999.999999999999999999` in non-strict mode.
Fraction digits beyond 18 are rounded.
5. **Signed and unsigned behave differently.** `DECIMAL UNSIGNED` is not
clamped: `DECIMAL(p, s) UNSIGNED` becomes `DECIMAL(p + 1, s)`, so `DECIMAL(65,
0) UNSIGNED` becomes `DECIMAL(66, 0)` while the signed `DECIMAL(65, 0)` becomes
`DECIMAL(38, 18)`.
6. **The only signal is a WARN.** The log line has no column name or target
type in it, and `GenericTypeConverter` does the same clamp without any log at
all.
The same `p > 38 -> (38, 18)` clamp exists in:
- `MySqlTypeConverter` (also used by MySQL-CDC through `MySqlTypeUtils`)
- `OceanBaseMySqlTypeConverter`
- `GenericTypeConverter`
(Other converters such as Oracle, Dameng, Xugu and YashanDB use `(38, 18)`
only for an unknown precision, or their database maximum is 38 anyway, so they
are not affected.)
For comparison, `PostgresTypeConverter` keeps the declared precision
(`NUMERIC(50, 0)` -> `DECIMAL(50, 0)`), and sinks with a lower limit already
handle wide decimals in their own `reconvert` (e.g. Oracle / SQL Server clamp
to their maximum with a WARN that names the column).
### Possible directions (for discussion)
- **A. Keep the declared type.** Map `DECIMAL(p, s)` to `DECIMAL(p, s)` as
PostgreSQL already does, and leave narrowing to each sink's `reconvert`. Spark
is covered separately by #12814. This changes the schema seen by sinks for `p >
38`, so it needs an `incompatible-changes.md` entry.
- **B. Keep a 38 cap, but keep integer digits first.** E.g. `DECIMAL(38,
max(0, 38 - (p - s)))`, plus a WARN with the column name, the original type and
the new type. Still lossy, but `DECIMAL(65, 0)` would at least become
`DECIMAL(38, 0)` instead of `DECIMAL(38, 18)`.
- Either way: make signed and unsigned consistent, apply the same rule to
the OceanBase and generic converters, and add a WARN to the generic converter.
I'd prefer A, because a value that the source can hold and the sink can hold
should not be lost in between. I'd like a maintainer's view before writing code.
### SeaTunnel Version
dev (`51f553cb4`)
### SeaTunnel Config
```conf
env {
parallelism = 1
job.mode = "BATCH"
}
source {
Jdbc {
url = "jdbc:mysql://mysql:3306/test"
driver = "com.mysql.cj.jdbc.Driver"
user = "root"
password = "***"
table_path = "test.t_wide_decimal" # id INT, amount DECIMAL(65, 0),
ratio DECIMAL(50, 30)
}
}
sink {
Jdbc {
url = "jdbc:mysql://mysql:3306/test"
driver = "com.mysql.cj.jdbc.Driver"
user = "root"
password = "***"
database = "test"
table = "t_wide_decimal_copy"
generate_sink_sql = true
schema_save_mode = "CREATE_SCHEMA_WHEN_NOT_EXIST"
}
}
```
With a row such as `amount = 12345678901234567890123456` (26 digits), the
auto-created `t_wide_decimal_copy.amount` is expected to be `DECIMAL(38, 18)`
and the write is expected to fail with `Out of range value` (strict mode).
### Running Command
```shell
./bin/seatunnel.sh --config mysql_wide_decimal.conf -m local
```
### Error Exception
```log
Expected from the code path (not yet reproduced):
WARN MySqlTypeConverter - DECIMAL will probably cause value overflow.
...
java.sql.SQLException: Out of range value for column 'amount' at row 1
```
### Zeta or Flink or Spark Version
All engines (the clamp is in the source type converter). Spark has an
additional limit tracked in #12814.
### Java or Scala Version
Java 8 / 11
### Screenshots
_No response_
### Are you willing to submit PR?
- [x] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of
Conduct](https://www.apache.org/foundation/policies/conduct)
--
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]
