You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Sean Huo <se...@crunchyroll.com> on 2014/06/10 22:30:59 UTC
counting returns different results
These two queries returns vastly different results:
query1:
select count(1) from t where date > 'certain day' and device ='A';
query2:
select device, count(1) from t where date >'certain day' and device ='A'
group by device;
assuming table t has the composite primary key of
date timestamp, and device varchar.
What gives?
Re: counting returns different results
Posted by Gabriel Reid <ga...@gmail.com>.
Could you post the DDL statement for creating the table? And could you
also say which version of HBase and Phoenix are you running on?
On Wed, Jun 11, 2014 at 1:58 AM, Sean Huo <se...@crunchyroll.com> wrote:
> Forget about the above two queries, it seems that phoenix count() function
> has a real problem dealing with large data size.
>
> this 30 day query returns a count of 36076
>
> select count(1) from table t where device ='A' and date >
> to_date('2014-05-09','yyyy-MM-dd') and date <
> to_date('2014-06-09','yyyy-MM-dd');
>
> while the following query for a smaller day range returns the correct
> 1303743
>
> select count(1) from table t where device ='A' and date >
> to_date('2014-06-01','yyyy-MM-dd') and date <
> to_date('2014-06-09','yyyy-MM-dd');
>
> The table has about 70 million rows per day.
>
>
> On Tue, Jun 10, 2014 at 2:43 PM, Sean Huo <se...@crunchyroll.com> wrote:
>>
>> The two queries seems to yield the same results for relatively small query
>> size, but diverge when the query size is really big (30*70 millions rows)
>>
>>
>> On Tue, Jun 10, 2014 at 2:07 PM, Gabriel Reid <ga...@gmail.com>
>> wrote:
>>>
>>> Hi Sean,
>>>
>>> That doesn't sound right -- any idea which of the queries (if either)
>>> is returning the correct results?
>>>
>>> Any chance you could try this out on a small test table and then post
>>> the relevant DDL and literal queries here?
>>>
>>> - Gabriel
>>>
>>>
>>> On Tue, Jun 10, 2014 at 10:30 PM, Sean Huo <se...@crunchyroll.com> wrote:
>>> > These two queries returns vastly different results:
>>> >
>>> > query1:
>>> > select count(1) from t where date > 'certain day' and device ='A';
>>> >
>>> > query2:
>>> >
>>> > select device, count(1) from t where date >'certain day' and device
>>> > ='A'
>>> > group by device;
>>> >
>>> > assuming table t has the composite primary key of
>>> > date timestamp, and device varchar.
>>> >
>>> > What gives?
>>> >
>>
>>
>
Re: counting returns different results
Posted by Sean Huo <se...@crunchyroll.com>.
Forget about the above two queries, it seems that phoenix count() function
has a real problem dealing with large data size.
this 30 day query returns a count of 36076
select count(1) from table t where device ='A' and date >
to_date('2014-05-09','yyyy-MM-dd') and date <
to_date('2014-06-09','yyyy-MM-dd');
while the following query for a smaller day range returns the correct
1303743
select count(1) from table t where device ='A' and date >
to_date('2014-06-01','yyyy-MM-dd') and date <
to_date('2014-06-09','yyyy-MM-dd');
The table has about 70 million rows per day.
On Tue, Jun 10, 2014 at 2:43 PM, Sean Huo <se...@crunchyroll.com> wrote:
> The two queries seems to yield the same results for relatively small query
> size, but diverge when the query size is really big (30*70 millions rows)
>
>
> On Tue, Jun 10, 2014 at 2:07 PM, Gabriel Reid <ga...@gmail.com>
> wrote:
>
>> Hi Sean,
>>
>> That doesn't sound right -- any idea which of the queries (if either)
>> is returning the correct results?
>>
>> Any chance you could try this out on a small test table and then post
>> the relevant DDL and literal queries here?
>>
>> - Gabriel
>>
>>
>> On Tue, Jun 10, 2014 at 10:30 PM, Sean Huo <se...@crunchyroll.com> wrote:
>> > These two queries returns vastly different results:
>> >
>> > query1:
>> > select count(1) from t where date > 'certain day' and device ='A';
>> >
>> > query2:
>> >
>> > select device, count(1) from t where date >'certain day' and device ='A'
>> > group by device;
>> >
>> > assuming table t has the composite primary key of
>> > date timestamp, and device varchar.
>> >
>> > What gives?
>> >
>>
>
>
Re: counting returns different results
Posted by Sean Huo <se...@crunchyroll.com>.
The two queries seems to yield the same results for relatively small query
size, but diverge when the query size is really big (30*70 millions rows)
On Tue, Jun 10, 2014 at 2:07 PM, Gabriel Reid <ga...@gmail.com>
wrote:
> Hi Sean,
>
> That doesn't sound right -- any idea which of the queries (if either)
> is returning the correct results?
>
> Any chance you could try this out on a small test table and then post
> the relevant DDL and literal queries here?
>
> - Gabriel
>
>
> On Tue, Jun 10, 2014 at 10:30 PM, Sean Huo <se...@crunchyroll.com> wrote:
> > These two queries returns vastly different results:
> >
> > query1:
> > select count(1) from t where date > 'certain day' and device ='A';
> >
> > query2:
> >
> > select device, count(1) from t where date >'certain day' and device ='A'
> > group by device;
> >
> > assuming table t has the composite primary key of
> > date timestamp, and device varchar.
> >
> > What gives?
> >
>
Re: counting returns different results
Posted by Gabriel Reid <ga...@gmail.com>.
Hi Sean,
That doesn't sound right -- any idea which of the queries (if either)
is returning the correct results?
Any chance you could try this out on a small test table and then post
the relevant DDL and literal queries here?
- Gabriel
On Tue, Jun 10, 2014 at 10:30 PM, Sean Huo <se...@crunchyroll.com> wrote:
> These two queries returns vastly different results:
>
> query1:
> select count(1) from t where date > 'certain day' and device ='A';
>
> query2:
>
> select device, count(1) from t where date >'certain day' and device ='A'
> group by device;
>
> assuming table t has the composite primary key of
> date timestamp, and device varchar.
>
> What gives?
>