Marko Sisovic created SPARK-58372:
-------------------------------------
Summary: Truncate fractional to integral casts pushed down to JDBC
sources
Key: SPARK-58372
URL: https://issues.apache.org/jira/browse/SPARK-58372
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 4.0.3, 4.1.2, 3.5.8, 3.4.4, 5.0.0
Reporter: Marko Sisovic
Spark truncates toward zero when casting a fractional value to an integral
type, but MySQL, Oracle, Postgres and Snowflake round half away from zero. When
such a cast is pushed down to the JDBC source, the query silently returns
different results than Spark would produce locally - there is no error, just
wrong values.
Given a table with the values 1.5, 2.5 and -1.5:
{code:java}
SELECT CAST(c AS INT) FROM t {code}
Spark returns 1, 2, -1, while these databases return 2, 3, -2.
The same divergence affects filters, where the rows are then eliminated on the
database side, so WHERE CAST(c AS INT) = 2 matches a different row than Spark
would.
Affected dialects are the ones whose database rounds: MySQL, Oracle, Postgres
and Snowflake. Dialects whose cast already truncates like Spark (MS SQL Server,
DB2, Derby, H2, Teradata) are not affected.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
