[jira] [Comment Edited] (IMPALA-8386) Incorrect predicate in a left outer join query
[
https://issues.apache.org/jira/browse/IMPALA-8386?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=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 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
[jira] [Comment Edited] (IMPALA-8386) Incorrect predicate in a left outer join query
[
https://issues.apache.org/jira/browse/IMPALA-8386?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16809987#comment-16809987
]
Csaba Ringhofer edited comment on IMPALA-8386 at 4/4/19 3:38 PM:
-
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}
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:
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
> 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
>Reporter: Quanlong Huang
>Assignee: Quanlong Huang
>Priority: Critical
>
> skyyws 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
