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]

Reply via email to