[SPARK-29860] [SQL] Fix data type mismatch issue for inSubQuery

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.4.4
• Fix Version/s: 3.0.0
• Component/s: SQL
• Assignee: feiwang
• Reporter: feiwang
• Created: 12/Nov/19 15:42
• Updated: 05/Dec/19 08:01
• Resolved: 05/Dec/19 08:01

Description

The follow statement would throw an exception.

      sql("create table ta(id Decimal(18,0)) using parquet")
      sql("create table tb(id Decimal(19,0)) using parquet")
      sql("select * from ta where id in (select id from tb)").shown()

// Exception information
cannot resolve '(default.ta.`id` IN (listquery()))' due to data type mismatch: 
The data type of one or more elements in the left hand side of an IN subquery
is not compatible with the data type of the output of the subquery
Mismatched columns:
[(default.ta.`id`:decimal(18,0), default.tb.`id`:decimal(19,0))]
Left side:
[decimal(18,0)].
Right side:
[decimal(19,0)].;;
'Project [*]
+- 'Filter id#219 IN (list#218 [])
   :  +- Project [id#220]
   :     +- SubqueryAlias `default`.`tb`
   :        +- Relation[id#220] parquet
   +- SubqueryAlias `default`.`ta`
      +- Relation[id#219] parquet

Issue Links

• is duplicated by: SPARK-29667 implicitly convert mismatched datatypes on right side of "IN" operator
• links to: GitHub Pull Request #26485