[ 
https://issues.apache.org/jira/browse/SPARK-62136?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
 ]

ASF GitHub Bot updated SPARK-62136:
-----------------------------------
    Labels: pull-request-available  (was: )

> 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
>            Priority: Major
>              Labels: pull-request-available
>
> 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