[SPARK-29980] Whitespaces handling for Cast and BinaryOperation between StringType and NumericTypes

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Kent Yao 2
• Created: 21/Nov/19 03:02
• Updated: 22/Nov/19 11:55
• Resolved: 21/Nov/19 03:36

Description

Here is a case, let see how it goes in different SQL engines.

select cast('1 ' as int) as v1, '1 ' = 1 as v2

spark 1.6

NULL	true

spark 2.1

NULL	true

spark 2.2

NULL	NULL

spark 2.3

NULL	NULL

spark 2.4

NULL	NULL

hive

NULL	true

PostgreSQL

postgres=# select cast('1 ' as int) as v1, '1 ' = 1 as v2;
 v1 | v2
----+----
  1 | t
(1 row)

presto

presto> select cast('1 ' as int) as v1, '1 ' = 1 as v2;
Query 20191120_060530_00002_f5kcs failed: line 1:38: '=' cannot be applied to varchar(2), integer
select cast('1 ' as int) as v1, '1 ' = 1 as v2

presto> select cast('1 ' as int) as v1, '1 ' = '1 ' as v2;
Query 20191120_060545_00003_f5kcs failed: Cannot cast '1 ' to INT

Our behavior is unstable because type coercion changed since 2.2.
Personally, I think What PostgreSQL and Presto does here is more reasonable and consistent

Issue Links

• duplicates: SPARK-28023 Trim the string when cast string type to other types
• duplicates: SPARK-30000 Trim the string when cast string type to decimals
• links to: GitHub Pull Request #26618