[
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]