Ravi Shetye created HIVE-21087:
----------------------------------
Summary: Query with filter on Coalesce returns wrong data
Key: HIVE-21087
URL: https://issues.apache.org/jira/browse/HIVE-21087
Project: Hive
Issue Type: Bug
Components: Query Planning, Query Processor
Affects Versions: 3.1.0
Reporter: Ravi Shetye
QUERY
{code:java}
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);
{code}
RESULT
{noformat}
+---------+--------+--------+
| c_4 | _c1 | _c2 |
+---------+--------+--------+
| Paris | Black | false |
| Paris | Red | false |
| Sydney | Black | true |
| Sydney | Red | false |
+---------+--------+--------+
{noformat}
_c2 is the exact expression in where, so the rows which have entry false should
not be present in the output
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
