phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Maryann Xue <maryann....@gmail.com>
Subject Re: Is there a bug on Left join in Phoenix
Date Tue, 02 Jun 2015 18:35:01 GMT
Hi Siva,

This is an expected behavior and therefore is not a bug.

For outer joins, condition specified in the ON clause would have different
effects than specified in the WHERE clause. If specified in the WHERE
clause, the join operation happens first and the condition is applied on
the join results, which in your case filters all rows that do not satisfy
cl."dbname"='lmguaranteedrate'. But if condition appears in the ON clause,
it is interpreted as part of the join conditions, which affects the join
behavior itself. The rows that do not satisfy the condition will not be
"joined" but will still be taken to the output. And that's why you get a
larger count of the rows than the previous case.

For inner joins though, the two are equivalent and can be treated the same
way.

You can refer to
http://www.tech-recipes.com/rx/47637/inner-and-left-outer-join-with-where-clause-vs-on-clause/
for some more examples.


Thanks,
Maryann

On Fri, May 29, 2015 at 2:17 PM, Siva <sbhavanari@gmail.com> wrote:

> Hi Everyone,
>
> Are there any known bugs on Left joins in Phoenix
>
> Here are the two queries with same conditions resulting different set of
> records.
>
>  0: jdbc:phoenix:172.31.45.176:2181:/hbase> select count(*) from
> . . . . . . . . . . . . . . . . . . . . .> (select rowkey, "typeid" as
> typeid, to_number("duration") as duration, "issoftphoneinit" as
> issoftphoneinit, "callsid" as callsid, "dbname" as dbname, "agentid" as
> agentid
> . . . . . . . . . . . . . . . . . . . . .> from "leaddialerleglog"
> . . . . . . . . . . . . . . . . . . . . .> where "logdate" >= '2015-01-01'
>  and "logdate" <=  '2015-05-01'
> . . . . . . . . . . . . . . . . . . . . .> and "dbname" ='lmguaranteedrate'
> . . . . . . . . . . . . . . . . . . . . .> and rowkey like
> 'lmguaranteedrate%'
> . . . . . . . . . . . . . . . . . . . . .> ) ldll
> . . . . . . . . . . . . . . . . . . . . .> left outer join
> "inboundnumbercalllog" cl on ldll.callsid = cl."callsid"
> . . . . . . . . . . . . . . . . . . . . .> where  cl."dbname"
> ='lmguaranteedrate';
>
> +------------------------------------------+
> |                 COUNT(1)                 |
> +------------------------------------------+
> | 28896                                    |
> +------------------------------------------+
> 1 row selected (26.949 seconds)
>
> 0: jdbc:phoenix:172.31.45.176:2181:/hbase> select count(*) from
> . . . . . . . . . . . . . . . . . . . . .> (select rowkey, "typeid" as
> typeid, to_number("duration") as duration, "issoftphoneinit" as
> issoftphoneinit, "callsid" as callsid, "dbname" as dbname, "agentid" as
> agentid
> . . . . . . . . . . . . . . . . . . . . .> from "leaddialerleglog"
> . . . . . . . . . . . . . . . . . . . . .> where "logdate" >= '2015-01-01'
>  and "logdate" <=  '2015-05-01'
> . . . . . . . . . . . . . . . . . . . . .> and "dbname" ='lmguaranteedrate'
> . . . . . . . . . . . . . . . . . . . . .> and rowkey like
> 'lmguaranteedrate%'
> . . . . . . . . . . . . . . . . . . . . .> ) ldll
> . . . . . . . . . . . . . . . . . . . . .> left outer join
> "inboundnumbercalllog" cl on (ldll.callsid = cl."callsid" and cl."dbname"
> ='lmguaranteedrate' );
> +------------------------------------------+
> |                 COUNT(1)                 |
> +------------------------------------------+
> | 426461                                   |
> +------------------------------------------+
> 1 row selected (27.205 seconds)
>
> Expected result is 426461.
>
> Thanks,
> Siva.
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message