[HIVE-21087] Query with filter on Coalesce returns wrong data

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: Query Planning, Query Processor
• Assignee: Unassigned
• Reporter: Ravi Shetye
• Created: 04/Jan/19 22:27
• Updated: 04/Jan/19 22:27

Description

QUERY

SELECT
 t_12.city_c14 c_4,
 REGEXP_EXTRACT( factinternetsales_t5.product_info, '(^|,)color:([^,]+)', 2 ),  
 COALESCE( t_12.city_c14 = 'Sydney' AND REGEXP_EXTRACT( factinternetsales_t5.product_info, '(^|,)color:([^,]+)', 2 ) = 'Black', false)
FROM
 (select 1 customerkey, "color:Red" product_info union select 2 customerkey, "color:Black" product_info  union select 3 customerkey, "color:Red" product_info  union select 4 customerkey, "color:Black" product_info  union select 5 customerkey, "color:Red" product_info )factinternetsales_t5
 JOIN (
  SELECT 1 customerkey_c10, 1 geographykey_c9 UNION SELECT 2 customerkey_c10, 1 geographykey_c9 UNION SELECT 3 customerkey_c10, 1 geographykey_c9 UNION SELECT 4 customerkey_c10, 2 geographykey_c9 UNION SELECT 5 customerkey_c10, 2 geographykey_c9 UNION SELECT 1 customerkey_c10, 2 geographykey_c9
 ) t_8 ON factinternetsales_t5.customerkey = t_8.customerkey_c10
 JOIN ( SELECT 'Sydney' city_c14, 1 geographykey_c13 UNION SELECT 'Paris' city_c14, 2 geographykey_c13 
 ) t_12 ON t_8.geographykey_c9 = t_12.geographykey_c13
WHERE
 COALESCE( t_12.city_c14 = 'Sydney' AND REGEXP_EXTRACT( factinternetsales_t5.product_info, '(^|,)color:([^,]+)', 2 ) = 'Black', false)
GROUP BY
 t_12.city_c14,
 REGEXP_EXTRACT( factinternetsales_t5.product_info, '(^|,)color:([^,]+)', 2 ), 
 COALESCE( t_12.city_c14 = 'Sydney' AND REGEXP_EXTRACT( factinternetsales_t5.product_info, '(^|,)color:([^,]+)', 2 ) = 'Black', false);  

RESULT

+---------+--------+--------+
|   c_4   |  _c1   |  _c2   |
+---------+--------+--------+
| Paris   | Black  | false  |
| Paris   | Red    | false  |
| Sydney  | Black  | true   |
| Sydney  | Red    | false  |
+---------+--------+--------+

_c2 is the exact expression in where, so the rows which have entry false should not be present in the output