zhengxiang378928908-code commented on issue #12841:
URL: https://github.com/apache/seatunnel/issues/12841#issuecomment-6258957032
@SEZ9 I ran the end-to-end reproduction. **One correction first: MySQL ->
MySQL is not affected.** My item 4 was wrong, and I've fixed the title and the
description.
**Setup:** Zeta local mode, `dev` at `e2957d27a` (starter + connector-jdbc
built from it), MySQL 8.0.46 (strict mode), PostgreSQL 16. Auto-created sink
tables (`RECREATE_SCHEMA`). Source table:
```sql
CREATE TABLE src.wide_dec (
id INT PRIMARY KEY,
d65_0 DECIMAL(65,0), d50_30 DECIMAL(50,30),
d65_0_u DECIMAL(65,0) UNSIGNED, d38_10 DECIMAL(38,10)
);
```
Six rows: 30 integer digits, the 65-digit max/min, `0` /
`0.000000000000000000000000000001`, a 20-digit integer / `1.5`, and all NULL.
**dev, MySQL -> MySQL:** the job succeeds, and the DDL and all values
round-trip:
```
`d65_0` decimal(65,0), `d50_30` decimal(50,30), `d65_0_u` decimal(65,0)
unsigned, `d38_10` decimal(38,10)
```
This is because `MysqlCreateTableSqlBuilder` (lines 195-197) reuses
`column.getSourceType()` when the sink is MySQL, so the clamped `DECIMAL(38,
18)` is never used for the DDL, and the reader passes the `BigDecimal` through
unscaled. The clamp only shows up when a sink builds its DDL from the SeaTunnel
type.
**dev, MySQL -> PostgreSQL:** this is where it breaks.
| column | auto-created PG type |
|---|---|
| `d65_0` DECIMAL(65,0) | `numeric(38,18)` |
| `d50_30` DECIMAL(50,30) | `numeric(38,18)` |
| `d65_0_u` DECIMAL(65,0) UNSIGNED | `numeric(66,0)` (wider than MySQL's own
65) |
| `d38_10` DECIMAL(38,10) | `numeric(38,10)` |
- All rows: the job fails with `ERROR: numeric field overflow - A field with
precision 38, scale 18 must round to an absolute value less than 10^20` on the
30-digit row.
- Only the rows that fit (ids 4, 5, 6): the job **succeeds**, but `d50_30 =
0.000000000000000000000000000001` is stored as `0.000000000000000000`. Data is
lost silently.
- The only log line is `WARN MySqlTypeConverter - DECIMAL will probably
cause value overflow.`, with no column name.
**#12853 (`dad4e1244`), same jobs:** the connector-jdbc jar was built from
@Eymenonar's branch, everything else unchanged.
- MySQL -> PostgreSQL: `numeric(65,0)`, `numeric(50,30)`, `numeric(65,0)`
for the unsigned column, `numeric(38,10)`. All 6 rows are written, and the
values match the source exactly (65-digit extremes, the 30-digit fraction,
NULLs).
- MySQL -> MySQL: unchanged, all 6 rows equal to the source (checked with a
join on every column).
So approach A fixes the PostgreSQL case without changing MySQL -> MySQL, and
signed and unsigned now give the same type. Two suggestions for #12853:
1. The tests could cover a non-MySQL target, since MySQL -> MySQL passed
before the fix too. For example, assert the `reconvert` output of the
PostgreSQL converter for the converted column, or add an E2E MySQL ->
PostgreSQL case.
2. The `incompatible-changes` entry should say that MySQL -> MySQL
auto-create was already preserving the declared type, so the visible change is
for other sinks and transforms.
--
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]
