[ https://issues.apache.org/jira/browse/IMPALA-8386?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16809987#comment-16809987 ]
Csaba Ringhofer edited comment on IMPALA-8386 at 4/4/19 4:09 PM: ----------------------------------------------------------------- I could slightly simplify the query, the aggregates + group by are actually not needed, a single field with two aliases is enough + the sub query for (select a_id from a) can be also replaced with "a": {code} select count(1) from ( select t2.a_id,t2.amount1,t2.amount2 from a left outer join ( select c.a_id, amount as amount1, amount as amount2 from b join c on b.b_id = c.b_id) t2 on a.a_id = t2.a_id ) t; -- returns 1 {code} was (Author: csringhofer): I could slightly simplify the query, the aggregates + group by are actually not needed, a single field with two aliases is enough: {code} select count(1) from ( select t2.a_id,t2.amount1,t2.amount2 from( select a_id from a) t1 left outer join ( select c.a_id, amount as amount1, amount as amount2 from b join c on b.b_id = c.b_id) t2 on t1.a_id = t2.a_id ) t; -- returns 1 {code} > Incorrect predicate in a left outer join query > ---------------------------------------------- > > Key: IMPALA-8386 > URL: https://issues.apache.org/jira/browse/IMPALA-8386 > Project: IMPALA > Issue Type: Bug > Components: Frontend > Reporter: Quanlong Huang > Assignee: Quanlong Huang > Priority: Critical > Labels: correctness > > skyyws <sky...@163.com> reported a bug [in the mailing > list|https://lists.apache.org/thread.html/0bdbbaa6bb35b552f050ae30587b7d75b78a72ec60007a8bc0a4a8a9@%3Cdev.impala.apache.org%3E] > on the following data set: > {code:java} > table A > +------+ > | a_id | > +------+ > | 1 | > | 2 | > +------+ > table B > +------+--------+ > | b_id | amount | > +------+--------+ > | 1 | 10 | > | 1 | 20 | > | 2 | NULL | > +------+--------+ > table C > +------+------+ > | a_id | b_id | > +------+------+ > | 1 | 1 | > | 2 | 2 | > +------+------+{code} > The following query returns a wrong result "1": > {code:java} > select count(1) from ( > select t2.a_id,t2.amount1,t2.amount2 > from( select a_id from a) t1 > left outer join ( > select c.a_id,sum(amount) as amount1,sum(amount) as amount2 > from b join c on b.b_id = c.b_id group by c.a_id) t2 > on t1.a_id = t2.a_id > ) t; > {code} > Removing "t2.amount2" can get the right result "2": > {code:java} > select count(1) from ( > select t2.a_id,t2.amount1 > from( select a_id from a) t1 > left outer join ( > select c.a_id,sum(amount) as amount1,sum(amount) as amount2 > from b join c on b.b_id = c.b_id group by c.a_id) t2 > on t1.a_id = t2.a_id > ) t; > {code} -- This message was sent by Atlassian JIRA (v7.6.3#76005) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-all-unsubscr...@impala.apache.org For additional commands, e-mail: issues-all-h...@impala.apache.org