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

Laljo John Pullokkaran updated HIVE-10841:
------------------------------------------
    Affects Version/s: 1.3.0

> [WHERE col is not null] does not work sometimes for queries with many JOIN 
> statements
> -------------------------------------------------------------------------------------
>
>                 Key: HIVE-10841
>                 URL: https://issues.apache.org/jira/browse/HIVE-10841
>             Project: Hive
>          Issue Type: Bug
>          Components: Query Planning, Query Processor
>    Affects Versions: 0.13.0, 0.14.0, 0.13.1, 1.2.0, 1.3.0
>            Reporter: Alexander Pivovarov
>            Assignee: Alexander Pivovarov
>         Attachments: HIVE-10841.patch
>
>
> The result from the following SELECT query is 3 rows but it should be 1 row.
> I checked it in MySQL - it returned 1 row.
> To reproduce the issue in Hive
> 1. prepare tables
> {code}
> drop table if exists L;
> drop table if exists LA;
> drop table if exists FR;
> drop table if exists A;
> drop table if exists PI;
> drop table if exists acct;
> create table L as select 4436 id;
> create table LA as select 4436 loan_id, 4748 aid, 4415 pi_id;
> create table FR as select 4436 loan_id;
> create table A as select 4748 id;
> create table PI as select 4415 id;
> create table acct as select 4748 aid, 10 acc_n, 122 brn;
> insert into table acct values(4748, null, null);
> insert into table acct values(4748, null, null);
> {code}
> 2. run SELECT query
> {code}
> select
>   acct.ACC_N,
>   acct.brn
> FROM L
> JOIN LA ON L.id = LA.loan_id
> JOIN FR ON L.id = FR.loan_id
> JOIN A ON LA.aid = A.id
> JOIN PI ON PI.id = LA.pi_id
> JOIN acct ON A.id = acct.aid
> WHERE
>   L.id = 4436
>   and acct.brn is not null;
> {code}
> the result is 3 rows
> {code}
> 10    122
> NULL  NULL
> NULL  NULL
> {code}
> but it should be 1 row
> {code}
> 10    122
> {code}
> 2.1 "explain select ..." output for hive-1.3.0 MR
> {code}
> STAGE DEPENDENCIES:
>   Stage-12 is a root stage
>   Stage-9 depends on stages: Stage-12
>   Stage-0 depends on stages: Stage-9
> STAGE PLANS:
>   Stage: Stage-12
>     Map Reduce Local Work
>       Alias -> Map Local Tables:
>         a 
>           Fetch Operator
>             limit: -1
>         acct 
>           Fetch Operator
>             limit: -1
>         fr 
>           Fetch Operator
>             limit: -1
>         l 
>           Fetch Operator
>             limit: -1
>         pi 
>           Fetch Operator
>             limit: -1
>       Alias -> Map Local Operator Tree:
>         a 
>           TableScan
>             alias: a
>             Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE Column 
> stats: NONE
>             Filter Operator
>               predicate: id is not null (type: boolean)
>               Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE 
> Column stats: NONE
>               HashTable Sink Operator
>                 keys:
>                   0 _col5 (type: int)
>                   1 id (type: int)
>                   2 aid (type: int)
>         acct 
>           TableScan
>             alias: acct
>             Statistics: Num rows: 3 Data size: 31 Basic stats: COMPLETE 
> Column stats: NONE
>             Filter Operator
>               predicate: aid is not null (type: boolean)
>               Statistics: Num rows: 2 Data size: 20 Basic stats: COMPLETE 
> Column stats: NONE
>               HashTable Sink Operator
>                 keys:
>                   0 _col5 (type: int)
>                   1 id (type: int)
>                   2 aid (type: int)
>         fr 
>           TableScan
>             alias: fr
>             Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE Column 
> stats: NONE
>             Filter Operator
>               predicate: (loan_id = 4436) (type: boolean)
>               Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE 
> Column stats: NONE
>               HashTable Sink Operator
>                 keys:
>                   0 4436 (type: int)
>                   1 4436 (type: int)
>                   2 4436 (type: int)
>         l 
>           TableScan
>             alias: l
>             Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE Column 
> stats: NONE
>             Filter Operator
>               predicate: (id = 4436) (type: boolean)
>               Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE 
> Column stats: NONE
>               HashTable Sink Operator
>                 keys:
>                   0 4436 (type: int)
>                   1 4436 (type: int)
>                   2 4436 (type: int)
>         pi 
>           TableScan
>             alias: pi
>             Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE Column 
> stats: NONE
>             Filter Operator
>               predicate: id is not null (type: boolean)
>               Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE 
> Column stats: NONE
>               HashTable Sink Operator
>                 keys:
>                   0 _col6 (type: int)
>                   1 id (type: int)
>   Stage: Stage-9
>     Map Reduce
>       Map Operator Tree:
>           TableScan
>             alias: la
>             Statistics: Num rows: 1 Data size: 14 Basic stats: COMPLETE 
> Column stats: NONE
>             Filter Operator
>               predicate: (((loan_id is not null and aid is not null) and 
> pi_id is not null) and (loan_id = 4436)) (type: boolean)
>               Statistics: Num rows: 1 Data size: 14 Basic stats: COMPLETE 
> Column stats: NONE
>               Map Join Operator
>                 condition map:
>                      Inner Join 0 to 1
>                      Inner Join 0 to 2
>                 keys:
>                   0 4436 (type: int)
>                   1 4436 (type: int)
>                   2 4436 (type: int)
>                 outputColumnNames: _col5, _col6
>                 Statistics: Num rows: 2 Data size: 8 Basic stats: COMPLETE 
> Column stats: NONE
>                 Map Join Operator
>                   condition map:
>                        Inner Join 0 to 1
>                        Inner Join 1 to 2
>                   keys:
>                     0 _col5 (type: int)
>                     1 id (type: int)
>                     2 aid (type: int)
>                   outputColumnNames: _col6, _col19, _col20
>                   Statistics: Num rows: 4 Data size: 17 Basic stats: COMPLETE 
> Column stats: NONE
>                   Map Join Operator
>                     condition map:
>                          Inner Join 0 to 1
>                     keys:
>                       0 _col6 (type: int)
>                       1 id (type: int)
>                     outputColumnNames: _col19, _col20
>                     Statistics: Num rows: 4 Data size: 18 Basic stats: 
> COMPLETE Column stats: NONE
>                     Select Operator
>                       expressions: _col19 (type: int), _col20 (type: int)
>                       outputColumnNames: _col0, _col1
>                       Statistics: Num rows: 4 Data size: 18 Basic stats: 
> COMPLETE Column stats: NONE
>                       File Output Operator
>                         compressed: false
>                         Statistics: Num rows: 4 Data size: 18 Basic stats: 
> COMPLETE Column stats: NONE
>                         table:
>                             input format: 
> org.apache.hadoop.mapred.TextInputFormat
>                             output format: 
> org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
>                             serde: 
> org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
>       Local Work:
>         Map Reduce Local Work
>   Stage: Stage-0
>     Fetch Operator
>       limit: -1
>       Processor Tree:
>         ListSink
> Time taken: 0.57 seconds, Fetched: 142 row(s)
> {code}
> 2.2. "explain select..." output for hive-0.13.1 Tez
> {code}
> STAGE DEPENDENCIES:
>   Stage-1 is a root stage
>   Stage-0 is a root stage
> STAGE PLANS:
>   Stage: Stage-1
>     Tez
>       Edges:
>         Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 4 (SIMPLE_EDGE), Reducer 6 
> (SIMPLE_EDGE)
>         Reducer 3 <- Reducer 2 (SIMPLE_EDGE), Map 9 (SIMPLE_EDGE)
>         Reducer 6 <- Map 5 (SIMPLE_EDGE), Map 7 (SIMPLE_EDGE), Map 8 
> (SIMPLE_EDGE)
>       DagName: lcapp_20150528111717_06c57a5b-8dc6-4ce9-bce7-b9e0a7818fe4:1
>       Vertices:
>         Map 1 
>             Map Operator Tree:
>                 TableScan
>                   alias: acct
>                   Statistics: Num rows: 1 Data size: 4 Basic stats: COMPLETE 
> Column stats: NONE
>                   Reduce Output Operator
>                     key expressions: aid (type: int)
>                     sort order: +
>                     Map-reduce partition columns: aid (type: int)
>                     Statistics: Num rows: 1 Data size: 4 Basic stats: 
> COMPLETE Column stats: NONE
>                     value expressions: acc_n (type: int), brn (type: int)
>         Map 4 
>             Map Operator Tree:
>                 TableScan
>                   alias: a
>                   Statistics: Num rows: 46 Data size: 187 Basic stats: 
> COMPLETE Column stats: NONE
>                   Reduce Output Operator
>                     key expressions: id (type: int)
>                     sort order: +
>                     Map-reduce partition columns: id (type: int)
>                     Statistics: Num rows: 46 Data size: 187 Basic stats: 
> COMPLETE Column stats: NONE
>         Map 5 
>             Map Operator Tree:
>                 TableScan
>                   alias: la
>                   Statistics: Num rows: 28 Data size: 347 Basic stats: 
> COMPLETE Column stats: NONE
>                   Filter Operator
>                     predicate: (loan_id = 4436) (type: boolean)
>                     Statistics: Num rows: 14 Data size: 173 Basic stats: 
> COMPLETE Column stats: NONE
>                     Reduce Output Operator
>                       key expressions: loan_id (type: int)
>                       sort order: +
>                       Map-reduce partition columns: loan_id (type: int)
>                       Statistics: Num rows: 14 Data size: 173 Basic stats: 
> COMPLETE Column stats: NONE
>                       value expressions: aid (type: int), pi_id (type: int)
>         Map 7 
>             Map Operator Tree:
>                 TableScan
>                   alias: fr
>                   Statistics: Num rows: 46 Data size: 187 Basic stats: 
> COMPLETE Column stats: NONE
>                   Filter Operator
>                     predicate: (loan_id = 4436) (type: boolean)
>                     Statistics: Num rows: 23 Data size: 93 Basic stats: 
> COMPLETE Column stats: NONE
>                     Reduce Output Operator
>                       key expressions: loan_id (type: int)
>                       sort order: +
>                       Map-reduce partition columns: loan_id (type: int)
>                       Statistics: Num rows: 23 Data size: 93 Basic stats: 
> COMPLETE Column stats: NONE
>         Map 8 
>             Map Operator Tree:
>                 TableScan
>                   alias: l
>                   Statistics: Num rows: 46 Data size: 187 Basic stats: 
> COMPLETE Column stats: NONE
>                   Filter Operator
>                     predicate: (id = 4436) (type: boolean)
>                     Statistics: Num rows: 23 Data size: 93 Basic stats: 
> COMPLETE Column stats: NONE
>                     Reduce Output Operator
>                       key expressions: id (type: int)
>                       sort order: +
>                       Map-reduce partition columns: id (type: int)
>                       Statistics: Num rows: 23 Data size: 93 Basic stats: 
> COMPLETE Column stats: NONE
>         Map 9 
>             Map Operator Tree:
>                 TableScan
>                   alias: pi
>                   Statistics: Num rows: 46 Data size: 187 Basic stats: 
> COMPLETE Column stats: NONE
>                   Reduce Output Operator
>                     key expressions: id (type: int)
>                     sort order: +
>                     Map-reduce partition columns: id (type: int)
>                     Statistics: Num rows: 46 Data size: 187 Basic stats: 
> COMPLETE Column stats: NONE
>         Reducer 2 
>             Reduce Operator Tree:
>               Join Operator
>                 condition map:
>                      Inner Join 0 to 1
>                      Inner Join 1 to 2
>                 condition expressions:
>                   0 {VALUE._col2}
>                   1 
>                   2 {VALUE._col1} {VALUE._col2}
>                 outputColumnNames: _col2, _col15, _col16
>                 Statistics: Num rows: 110 Data size: 448 Basic stats: 
> COMPLETE Column stats: NONE
>                 Reduce Output Operator
>                   key expressions: _col2 (type: int)
>                   sort order: +
>                   Map-reduce partition columns: _col2 (type: int)
>                   Statistics: Num rows: 110 Data size: 448 Basic stats: 
> COMPLETE Column stats: NONE
>                   value expressions: _col15 (type: int), _col16 (type: int)
>         Reducer 3 
>             Reduce Operator Tree:
>               Join Operator
>                 condition map:
>                      Inner Join 0 to 1
>                 condition expressions:
>                   0 {VALUE._col1} {VALUE._col2}
>                   1 
>                 outputColumnNames: _col1, _col2
>                 Statistics: Num rows: 121 Data size: 492 Basic stats: 
> COMPLETE Column stats: NONE
>                 Select Operator
>                   expressions: _col1 (type: int), _col2 (type: int)
>                   outputColumnNames: _col0, _col1
>                   Statistics: Num rows: 121 Data size: 492 Basic stats: 
> COMPLETE Column stats: NONE
>                   File Output Operator
>                     compressed: false
>                     Statistics: Num rows: 121 Data size: 492 Basic stats: 
> COMPLETE Column stats: NONE
>                     table:
>                         input format: org.apache.hadoop.mapred.TextInputFormat
>                         output format: 
> org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
>                         serde: 
> org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
>         Reducer 6 
>             Reduce Operator Tree:
>               Join Operator
>                 condition map:
>                      Inner Join 0 to 1
>                      Inner Join 0 to 2
>                 condition expressions:
>                   0 
>                   1 {VALUE._col1} {VALUE._col2}
>                   2 
>                 outputColumnNames: _col4, _col5
>                 Statistics: Num rows: 50 Data size: 204 Basic stats: COMPLETE 
> Column stats: NONE
>                 Reduce Output Operator
>                   key expressions: _col4 (type: int)
>                   sort order: +
>                   Map-reduce partition columns: _col4 (type: int)
>                   Statistics: Num rows: 50 Data size: 204 Basic stats: 
> COMPLETE Column stats: NONE
>                   value expressions: _col5 (type: int)
>   Stage: Stage-0
>     Fetch Operator
>       limit: -1
> Time taken: 1.377 seconds, Fetched: 146 row(s)
> {code}
> 3. The workaround is to put "acct.brn is not null" to join condition
> {code}
> select
>   acct.ACC_N,
>   acct.brn
> FROM L
> JOIN LA ON L.id = LA.loan_id
> JOIN FR ON L.id = FR.loan_id
> JOIN A ON LA.aid = A.id
> JOIN PI ON PI.id = LA.pi_id
> JOIN acct ON A.id = acct.aid and acct.brn is not null
> WHERE
>   L.id = 4436;
> OK
> 10    122
> Time taken: 23.479 seconds, Fetched: 1 row(s)
> {code}
> I tried it on hive-1.3.0 (MR) and hive-0.13.1 (MR and Tez) - all combinations 
> have the issue



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Reply via email to