[jira] [Updated] (HIVE-12216) WHERE on the FROM table not (always) working when JOIN are present

["Dmitry Tolpeko (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-12216?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dmitry Tolpeko updated HIVE-12216:
----------------------------------
    Component/s:     (was: hpl/sql)

> WHERE on the FROM table not (always) working when JOIN are present
> ------------------------------------------------------------------
>
>                 Key: HIVE-12216
>                 URL: https://issues.apache.org/jira/browse/HIVE-12216
>             Project: Hive
>          Issue Type: Bug
>          Components: Parser, Query Processor, SQL
>    Affects Versions: 1.1.0, 1.2.1
>         Environment: CDH 5.4.7 HDP2.3.2 MR TEZ
>            Reporter: Bolke de Bruin
>            Priority: Blocker
>
> In case we use a where clause in a state where also joins are present, the clauses are not (always) respected. We have been able to reproduce this issue consistently with Hive 1.1.0 on MR, Hive 1.2.1 on Tez (MR Fails here). 
> So fo the below query we *do* get results back like:
> 'gs.i_s_c = 23' (and this goes for all clauses!)
> CREATE TABLE tmp_hub_and_sats AS
> SELECT
>    f.dt,
>    f.t_c,
>    sum(f.transaction_amount) as amount,
>    sum(f.amount_euro) amount_euro,
>    IF(f.org_grid is null, f.org_cust, f.org_grid) as org,
>    IF(f.org_grid is null, 0, 1) as is_org_grid,
>    IF(f.org_up is null, if(f.org_grid is null, f.org_cust, f.org_grid), f.org_up) as org_up,
>    IF(f.to_grid is null, f.to_cust, f.to_grid) to,
>    IF(f.to_grid is null, 0, 1) as is_to_grid,
>    IF(f.to_up is null, if(f.to_grid is null, f.to_cust, f.to_grid), f.to_up) as to_up,
>    gh.i_g_c as customer_code_hub,
>    gs.i_g_c as customer_code_satellite
> from x_grid_orders f
> LEFT OUTER JOIN
>  grid.grid gh
>  ON f.org_grid = gh.hashed_gridid
> LEFT OUTER JOIN
>  grid.grid gs
>  ON f.to_grid = gs.hashed_gridid
> where
> IF(f.org_up is null, f.org_cust, f.org_up) <> IF(f.to_up is null, f.to_cust, f.to_up)
>  AND
>  (substring(gh.i_g_c, 1, 2) <> "06" or gh.i_g_c is null)
>  AND
>  (substring(gs.i_g_c, 1, 2) <> "06" or gs.i_g_c is null)
>  AND
>  (gh.i_s_c <> "23" or gh.i_s_c is null)
>  AND
>  (gs.i_s_c <> "23" or gs.i_s_c is null)
> group by
>  f.dt,
>  f.t_c,
>  IF(f.org_grid is null, f.org_cust, f.org_grid),
>  IF(f.org_grid is null, 0, 1),
>  IF(f.org_up is null, if(f.org_grid is null, f.org_cust, f.org_grid), f.org_up),
>  IF(f.to_grid is null, f.to_cust, f.to_grid),
>  IF(f.to_grid is null, 0, 1),
>  IF(f.to_up is null, if(f.to_grid is null, f.to_cust, f.to_grid), f.to_up),
>  gh.i_g_c,
>  gs.i_g_c



--
This message was sent by Atlassian JIRA
(v6.4.14#64029)