Alek Jarmov created SPARK-62136:
-----------------------------------
Summary: MsSqlServer dialect pushes down CAST from FLOAT, DOUBLE
and BOOLEAN to STRING and returns wrong results
Key: SPARK-62136
URL: https://issues.apache.org/jira/browse/SPARK-62136
Project: Spark
Issue Type: Bug
Components: SQL
Affects Versions: 5.0.0
Reporter: Alek Jarmov
The SQL Server JDBC dialect compiles a cast to STRING as {{CAST(x AS
NVARCHAR(MAX))}} and pushes it down. Every atomic-to-string cast is an up-cast,
so filters, aggregates and join conditions that contain one are pushed in both
ANSI and non-ANSI mode. That includes implicit casts, such as {{d LIKE '1.%'}}
on a DOUBLE column.
SQL Server formats FLOAT and REAL with at most 6 significant digits and BIT as
1 or 0, so the pushed expression compares different strings than Spark does and
the query silently returns wrong rows:
||Value||Spark CAST(x AS STRING)||SQL Server CAST(x AS NVARCHAR(MAX))||
|DOUBLE 100.0|100.0|100|
|DOUBLE 123.456789|123.456789|123.457|
|DOUBLE 1234567.0|1234567.0|1.23457e+006|
|REAL 123.4567|123.4567|123.457|
|BOOLEAN true|true|1|
For example, with a SQL Server table {{t(id INT, d DOUBLE)}} that contains the
row (2, 100.0):
{code:sql}
SELECT id FROM t WHERE CAST(d AS STRING) = '100.0'
{code}
returns no rows instead of 2.
Proposed fix: {{MsSqlServerSQLBuilder}} does not compile casts from FLOAT,
DOUBLE or BOOLEAN to STRING, so Spark evaluates them. Casts to STRING from
integral, DECIMAL and other types are still pushed down.
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]