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 Wed, 03 Jun 2015 03:12:36 GMT
Hi Siva,

Not sure if this guess is correct, but it's likely that the LEFT OUTER JOIN
produces some rows that has the fields from the second table as null
values, and in that case the WHERE condition matters. Since the WHERE
clause is always true for the second table, the first query should be
equivalent to an inner join. To verify this, you scan try running your
first join query with LEFT OUTER JOIN replaced with INNER JOIN and with or
without the WHERE clause (which does not matter here according to your
data).


Thanks,
Maryann

On Tue, Jun 2, 2015 at 6:45 PM, Siva <sbhavanari@gmail.com> wrote:

> 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