[SPARK-25039] Binary comparison behavior should refer to Teradata

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Yuming Wang
• Created: 07/Aug/18 04:42
• Updated: 09/Jan/21 08:09

Description

The main difference is:

1. When comparing a StringType value with a NumericType value, Spark converts the StringType data to a NumericType value. But Teradata converts the StringType data to a DoubleType value.
2. When comparing a StringType value with a DateType value, Spark converts the DateType data to a StringType value. But Teradata converts the StringType data to a DateType value.
    

More details:
https://github.com/apache/spark/blob/65a4bc143ab5dc2ced589dc107bbafa8a7290931/sql/catalyst/src/main/scala/org/apache/spark/sql/catalyst/analysis/TypeCoercion.scala#L120-L149
https://www.info.teradata.com/HTMLPubs/DB_TTU_16_00/index.html#page/SQL_Reference/B035-1145-160K/lrn1472241011038.html

Issue Links

• is related to: SPARK-17913 Filter/join expressions can return incorrect results when comparing strings to longs
• is related to: SPARK-19145 Timestamp to String casting is slowing the query significantly
• relates to: SPARK-33459 Commonly used Teradata extension syntax