Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-21087

Query with filter on Coalesce returns wrong data

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 3.1.0
    • Fix Version/s: None
    • Labels:
      None

      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

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              rshetye Ravi Shetye
            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: