fatmanverse opened a new issue, #12915:
URL: https://github.com/apache/seatunnel/issues/12915
### Search before asking
- [x] I had searched in the issues and found no similar issues.
### What happened
In the SQL transform, `CAST(x AS DECIMAL(p,s))` rounds with
`RoundingMode.CEILING` when `x` is a DECIMAL or STRING value, and with
`HALF_UP` when `x` is a FLOAT or DOUBLE (#11523). The same number gives a
different result depending on its source type, and the CEILING results differ
from `CAST` in MySQL, PostgreSQL and H2:
| expression (`DECIMAL(6,1)`) | DECIMAL source | STRING source | DOUBLE
source | MySQL 8 |
| --- | --- | --- | --- | --- |
| `CAST(1.21 ...)` | **1.3** | **1.3** | 1.2 | 1.2 |
| `CAST(-1.21 ...)` | -1.2 | -1.2 | -1.2 | -1.2 |
| `CAST(-1.25 ...)` | **-1.2** | **-1.2** | -1.3 | -1.3 |
Measured on a Zeta cluster with FakeSource -> Sql -> Jdbc (MySQL); the MySQL
column is `SELECT CAST(1.21 AS DECIMAL(6,1))` on the same server.
The CEILING branch is in `SystemFunction#castAs` (`case "DECIMAL"`). #11523
changed only the Float/Double branches and left the rounding mode of the other
branches untouched on purpose to keep that PR small; the code comment there
already notes that CEILING "would silently corrupt ordinary DECIMAL casts".
### How to reproduce
```hocon
source {
FakeSource {
schema = { fields { id = int, c_dec = "decimal(20, 2)", c_str = string,
c_dbl = double } }
rows = [{ kind = INSERT, fields = [1, 1.21, "1.21", 1.21] }]
}
}
transform {
Sql {
query = "select id, CAST(c_dec AS DECIMAL(6, 1)) as d_dec, CAST(c_str AS
DECIMAL(6, 1)) as d_str, CAST(c_dbl AS DECIMAL(6, 1)) as d_dbl from dual"
}
}
sink { Console {} }
```
Output: `1, 1.3, 1.3, 1.2`.
### Expected behavior
`CAST ... AS DECIMAL` rounds half away from zero for every source type: `1,
1.2, 1.2, 1.2`. This changes results for existing jobs, so it needs an entry in
`incompatible-changes.md`.
### SeaTunnel Version
dev (3.0.1-SNAPSHOT, e2957d2)
### Zeta or Flink or Spark Version
Zeta
### Java or Scala Version
JDK 11
### Are you willing to submit PR?
- [x] Yes I am willing to submit a PR!
--
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]
