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

Navis reassigned HIVE-7346:
---------------------------

    Assignee: Navis

> Wrong results caused by hive ppd under specific join condition
> --------------------------------------------------------------
>
>                 Key: HIVE-7346
>                 URL: https://issues.apache.org/jira/browse/HIVE-7346
>             Project: Hive
>          Issue Type: Bug
>    Affects Versions: 0.12.0
>            Reporter: dima machlin
>            Assignee: Navis
>
> Assuming two tables :
> {code:sql} t1(id1 string,id2 string) , t2 (id string,d int) {code}
> t1 contains 1 row : 'a','a'
> t2 contains 1 row : 'a',2
> The following query : 
> {code:sql} select a.*,b.d d1,c.d d2
> from t1 a join t2 b on (a.id1=b.id)
> join t2 c on (a.id2=b.id)
> where b.d <=1 and c.d<=1 {code}
> Returns 0 rows as expected because t2.d = 2
> Wrapping this query, like so : 
> {code:sql} select * from (
> select a.*,b.d d1,c.d d2
> from t1 a join t2 b on (a.id1=b.id)
> join t2 c on (a.id2=b.id)
> where b.d <=1 and c.d<=1
> ) z where d1>1 or d2>1 {code}
> Where another filter was add on the columns causes the plan to lack the 
> filter of the "<=1" and return a single row - *Wrong Results*.
> The plan is : 
> {code:sql}
> ABSTRACT SYNTAX TREE:
>   (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_JOIN 
> (TOK_TABREF (TOK_TABNAME t1) a) (TOK_TABREF (TOK_TABNAME t2) b) (= (. 
> (TOK_TABLE_OR_COL a) id1) (. (TOK_TABLE_OR_COL b) id))) (TOK_TABREF 
> (TOK_TABNAME t2) c) (= (. (TOK_TABLE_OR_COL a) id2) (. (TOK_TABLE_OR_COL b) 
> id)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT 
> (TOK_SELEXPR (TOK_ALLCOLREF (TOK_TABNAME a))) (TOK_SELEXPR (. 
> (TOK_TABLE_OR_COL b) d) d1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) d) d2)) 
> (TOK_WHERE (and (<= (. (TOK_TABLE_OR_COL b) d) 1) (<= (. (TOK_TABLE_OR_COL c) 
> d) 1))))) z)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) 
> (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (or (> (TOK_TABLE_OR_COL 
> d1) 1) (> (TOK_TABLE_OR_COL d2) 1)))))
> STAGE DEPENDENCIES:
>   Stage-7 is a root stage
>   Stage-5 depends on stages: Stage-7
>   Stage-0 is a root stage
> STAGE PLANS:
>   Stage: Stage-7
>     Map Reduce Local Work
>       Alias -> Map Local Tables:
>         z:b 
>           Fetch Operator
>             limit: -1
>         z:c 
>           Fetch Operator
>             limit: -1
>       Alias -> Map Local Operator Tree:
>         z:b 
>           TableScan
>             alias: b
>             HashTable Sink Operator
>               condition expressions:
>                 0 {id1} {id2}
>                 1 {id} {d}
>               handleSkewJoin: false
>               keys:
>                 0 [Column[id1]]
>                 1 [Column[id]]
>               Position of Big Table: 0
>         z:c 
>           TableScan
>             alias: c
>             HashTable Sink Operator
>               condition expressions:
>                 0 {_col5} {_col0} {_col1}
>                 1 {d}
>               handleSkewJoin: false
>               keys:
>                 0 []
>                 1 []
>               Position of Big Table: 0
>   Stage: Stage-5
>     Map Reduce
>       Alias -> Map Operator Tree:
>         z:a 
>           TableScan
>             alias: a
>             Map Join Operator
>               condition map:
>                    Inner Join 0 to 1
>               condition expressions:
>                 0 {id1} {id2}
>                 1 {id} {d}
>               handleSkewJoin: false
>               keys:
>                 0 [Column[id1]]
>                 1 [Column[id]]
>               outputColumnNames: _col0, _col1, _col4, _col5
>               Position of Big Table: 0
>               Filter Operator
>                 predicate:
>                     expr: (_col1 = _col4)
>                     type: boolean
>                 Map Join Operator
>                   condition map:
>                        Inner Join 0 to 1
>                   condition expressions:
>                     0 {_col5} {_col0} {_col1}
>                     1 {d}
>                   handleSkewJoin: false
>                   keys:
>                     0 []
>                     1 []
>                   outputColumnNames: _col1, _col4, _col5, _col9
>                   Position of Big Table: 0
>                   Filter Operator
>                     predicate:
>                         expr: ((_col1 > 1) or (_col9 > 1))
>                         type: boolean
>                     Select Operator
>                       expressions:
>                             expr: _col4
>                             type: string
>                             expr: _col5
>                             type: string
>                             expr: _col1
>                             type: int
>                             expr: _col9
>                             type: int
>                       outputColumnNames: _col0, _col1, _col2, _col3
>                       File Output Operator
>                         compressed: false
>                         GlobalTableId: 0
>                         table:
>                             input format: 
> org.apache.hadoop.mapred.TextInputFormat
>                             output format: 
> org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
>       Local Work:
>         Map Reduce Local Work
>   Stage: Stage-0
>     Fetch Operator
>       limit: -1
> {code}
> Setting : {code:sql} hive.optimize.ppd=false {code}
> Results in the following *correct* plan : {code:sql} ABSTRACT SYNTAX TREE:
>   (TOK_QUERY (TOK_FROM (TOK_SUBQUERY (TOK_QUERY (TOK_FROM (TOK_JOIN (TOK_JOIN 
> (TOK_TABREF (TOK_TABNAME t1) a) (TOK_TABREF (TOK_TABNAME t2) b) (= (. 
> (TOK_TABLE_OR_COL a) id1) (. (TOK_TABLE_OR_COL b) id))) (TOK_TABREF 
> (TOK_TABNAME t2) c) (= (. (TOK_TABLE_OR_COL a) id2) (. (TOK_TABLE_OR_COL b) 
> id)))) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) (TOK_SELECT 
> (TOK_SELEXPR (TOK_ALLCOLREF (TOK_TABNAME a))) (TOK_SELEXPR (. 
> (TOK_TABLE_OR_COL b) d) d1) (TOK_SELEXPR (. (TOK_TABLE_OR_COL c) d) d2)) 
> (TOK_WHERE (and (<= (. (TOK_TABLE_OR_COL b) d) 1) (<= (. (TOK_TABLE_OR_COL c) 
> d) 1))))) z)) (TOK_INSERT (TOK_DESTINATION (TOK_DIR TOK_TMP_FILE)) 
> (TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF)) (TOK_WHERE (or (> (TOK_TABLE_OR_COL 
> d1) 1) (> (TOK_TABLE_OR_COL d2) 1)))))
> STAGE DEPENDENCIES:
>   Stage-7 is a root stage
>   Stage-5 depends on stages: Stage-7
>   Stage-0 is a root stage
> STAGE PLANS:
>   Stage: Stage-7
>     Map Reduce Local Work
>       Alias -> Map Local Tables:
>         z:b 
>           Fetch Operator
>             limit: -1
>         z:c 
>           Fetch Operator
>             limit: -1
>       Alias -> Map Local Operator Tree:
>         z:b 
>           TableScan
>             alias: b
>             HashTable Sink Operator
>               condition expressions:
>                 0 {id1} {id2}
>                 1 {id} {d}
>               handleSkewJoin: false
>               keys:
>                 0 [Column[id1]]
>                 1 [Column[id]]
>               Position of Big Table: 0
>         z:c 
>           TableScan
>             alias: c
>             HashTable Sink Operator
>               condition expressions:
>                 0 {_col5} {_col0} {_col1}
>                 1 {d}
>               handleSkewJoin: false
>               keys:
>                 0 []
>                 1 []
>               Position of Big Table: 0
>   Stage: Stage-5
>     Map Reduce
>       Alias -> Map Operator Tree:
>         z:a 
>           TableScan
>             alias: a
>             Map Join Operator
>               condition map:
>                    Inner Join 0 to 1
>               condition expressions:
>                 0 {id1} {id2}
>                 1 {id} {d}
>               handleSkewJoin: false
>               keys:
>                 0 [Column[id1]]
>                 1 [Column[id]]
>               outputColumnNames: _col0, _col1, _col4, _col5
>               Position of Big Table: 0
>               Filter Operator
>                 predicate:
>                     expr: (_col1 = _col4)
>                     type: boolean
>                 Map Join Operator
>                   condition map:
>                        Inner Join 0 to 1
>                   condition expressions:
>                     0 {_col5} {_col0} {_col1}
>                     1 {d}
>                   handleSkewJoin: false
>                   keys:
>                     0 []
>                     1 []
>                   outputColumnNames: _col1, _col4, _col5, _col9
>                   Position of Big Table: 0
>                   Filter Operator
>                     predicate:
>                         expr: ((_col1 <= 1) and (_col9 <= 1))
>                         type: boolean
>                     Select Operator
>                       expressions:
>                             expr: _col4
>                             type: string
>                             expr: _col5
>                             type: string
>                             expr: _col1
>                             type: int
>                             expr: _col9
>                             type: int
>                       outputColumnNames: _col0, _col1, _col2, _col3
>                       Filter Operator
>                         predicate:
>                             expr: ((_col2 > 1) or (_col3 > 1))
>                             type: boolean
>                         Select Operator
>                           expressions:
>                                 expr: _col0
>                                 type: string
>                                 expr: _col1
>                                 type: string
>                                 expr: _col2
>                                 type: int
>                                 expr: _col3
>                                 type: int
>                           outputColumnNames: _col0, _col1, _col2, _col3
>                           File Output Operator
>                             compressed: false
>                             GlobalTableId: 0
>                             table:
>                                 input format: 
> org.apache.hadoop.mapred.TextInputFormat
>                                 output format: 
> org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
>       Local Work:
>         Map Reduce Local Work
>   Stage: Stage-0
>     Fetch Operator
>       limit: -1 {code}



--
This message was sent by Atlassian JIRA
(v6.2#6252)

Reply via email to