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 <sbhavan...@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. >
