Marko Sisovic created SPARK-58406:
-------------------------------------
Summary: Make cast pushdown in V2ExpressionSQLBuilder explicit
about engine cast semantics
Key: SPARK-58406
URL: https://issues.apache.org/jira/browse/SPARK-58406
Project: Spark
Issue Type: Improvement
Components: SQL
Affects Versions: 5.0.0
Reporter: Marko Sisovic
Spark and the external engines it pushes down to do not agree on cast
semantics. The clearest case is fractional to integral casts: Spark truncates
toward zero, while MySQL, Oracle, Postgres and Snowflake round half away from
zero, so a pushed down cast silently returns different results than Spark would
produce locally (SPARK-58372). While fixing that, two more cast bugs surfaced
in the same area: MySQL rejected the pushed CAST(... AS INTEGER) because it
requires SIGNED, and other engines have no equivalent of the truncating
function at all.
The common root cause is that V2ExpressionSQLBuilder.visitCast has a permissive
default. It renders CAST(expr AS type) for any dialect that does not override
it, which implicitly assumes the engine's cast semantics match Spark's. Nothing
forces a dialect author to confirm that assumption, so mismatches are only
found when someone notices wrong results.
Proposal: make the cast entry point explicit. Add a dedicated method for casts
whose default implementation throws (following the existing visitUnexpectedExpr
convention), so a dialect that has not declared its cast semantics does not
push casts down at all.
Note this only affects pushdown, not correctness. compileExpression catches the
exception and returns None, so the cast falls back to Spark and the query
returns the same results as before, just without that pushdown. The cost of not
implementing the method is a lost optimization, never a wrong answer.
The method should be concrete with a throwing default rather than abstract, so
external DSv2 builders that subclass V2ExpressionSQLBuilder still compile.
Scope note for the implementation: the dialects that currently rely on the base
visitCast (DB2, Databricks, Derby, H2, MS SQL Server, Teradata) need the new
method implemented as part of the same change, otherwise they silently lose
cast pushdown.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
