phoenix-dev mailing list archives

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

Thanks for your response.

Here is the count of records in second table with and without filter. In
this case, whether the filter is in where clause (or) on clause should get
the same number of records. Initially, I tried to debug the data, but could
not find any issue with data.

0: jdbc:phoenix:172.31.45.176:2181:/hbase> select count(*) from
"inboundnumbercalllog" where "dbname" ='lmguaranteedrate';
+------------------------------------------+
|                 COUNT(1)                 |
+------------------------------------------+
| 82054                                    |
+------------------------------------------+
1 row selected (0.795 seconds)
0: jdbc:phoenix:172.31.45.176:2181:/hbase> select count(*) from
"inboundnumbercalllog";
+------------------------------------------+
|                 COUNT(1)                 |
+------------------------------------------+
| 82054                                    |
+------------------------------------------+
1 row selected (0.212 seconds)
0: jdbc:phoenix:172.31.45.176:2181:/hbase> select count(*) from
"inboundnumbercalllog" where "dbname" !='lmguaranteedrate';
+------------------------------------------+
|                 COUNT(1)                 |
+------------------------------------------+
| 0                                        |
+------------------------------------------+

Thanks,
Siva.



On Tue, Jun 2, 2015 at 11:35 AM, Maryann Xue <maryann.xue@gmail.com> wrote:

> 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