[SPARK-21646] Add new type coercion rules to compatible with Hive

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 2.2.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Yuming Wang
• Created: 05/Aug/17 09:08
• Updated: 13/Dec/21 04:51
• Resolved: 18/Sep/19 02:39

Description

How to reproduce:
hive:

$ hive -S
hive> create table spark_21646(c1 string, c2 string);
hive> insert into spark_21646 values('92233720368547758071', 'a');
hive> insert into spark_21646 values('21474836471', 'b');
hive> insert into spark_21646 values('10', 'c');
hive> select * from spark_21646 where c1 > 0;
92233720368547758071	a
10	c
21474836471	b
hive>

spark-sql:

$ spark-sql -S
spark-sql> select * from spark_21646 where c1 > 0;
10      c                                                                       
spark-sql> select * from spark_21646 where c1 > 0L;
21474836471	b
10	c
spark-sql> explain select * from spark_21646 where c1 > 0;
== Physical Plan ==
*Project [c1#14, c2#15]
+- *Filter (isnotnull(c1#14) && (cast(c1#14 as int) > 0))
   +- *FileScan parquet spark_21646[c1#14,c2#15] Batched: true, Format: Parquet, Location: InMemoryFileIndex[viewfs://cluster4/user/hive/warehouse/spark_21646], PartitionFilters: [], PushedFilters: [IsNotNull(c1)], ReadSchema: struct<c1:string,c2:string>
spark-sql> 

As you can see, spark auto cast c1 to int type, if this value out of integer range, the result is different from Hive.

Issue Links

• is blocked by: SPARK-22722 Test Coverage for Type Coercion Compatibility
• is duplicated by: SPARK-30471 Fix issue when compare string and IntegerType
• is duplicated by: SPARK-23175 Type conversion does not make sense under case like select ’0.1’ = 0
• is duplicated by: SPARK-23498 Accuracy problem in comparison with string and integer
• is duplicated by: SPARK-22722 Test Coverage for Type Coercion Compatibility
• is related to: SPARK-21774 The rule PromoteStrings cast string to a wrong data type
• relates to: SPARK-17913 Filter/join expressions can return incorrect results when comparing strings to longs
• relates to: SPARK-22469 Accuracy problem in comparison with string and numeric
• links to: [Github] Pull Request #18853 (wangyum)
• links to: GitHub Pull Request #18853
• links to: GitHub Pull Request #23626