[SPARK-22413] Type coercion for IN is not coherent between Literals and subquery

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Incomplete
• Affects Version/s: 2.3.0
• Component/s: SQL
• Labels: bulk-closed
• Assignee: Unassigned
• Reporter: Marco Gaido
• Created: 01/Nov/17 20:45
• Updated: 25/May/21 01:54
• Resolved: 25/May/21 01:44

Description

The IN statement can have a different behavior according to whether there is a list of literals or a subquery, because the type coercion works differently in the two cases.

This means, that these two equivalent queries return different results:

select 1 from (select 1) a where 1 in ('01'); // this returns an empty dataset
select 1 from (select 1) a where 1 in (select '01' from (select 1)); // returns 1 row

Issue Links

• is duplicated by: SPARK-22398 Partition directories with leading 0s cause wrong results
• is duplicated by: SPARK-24916 Fix type coercion for IN expression with subquery
• relates to: SPARK-22398 Partition directories with leading 0s cause wrong results
• links to: [Github] Pull Request #19635 (mgaido91)
• links to: GitHub Pull Request #19635