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 <[email protected]> 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 <[email protected]> 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. > > >
