Re: paged queries failed with index on 3.3.1

[Chunhui Liu <le...@gmail.com>]

Hello,
Phoenix 4.5.2 on CDH 5.3.6 still has the same issue.

Thanks,
Chunhui

2015-09-01 15:17 GMT+08:00 刘春珲 <le...@gmail.com>:

> Hello,
> We are now using CDH 4.5.0, so update HBase to 0.98+ may take a little
> more time.
>
> Thanks,
> Chunhui
>
> 2015-09-01 14:54 GMT+08:00 James Taylor <ja...@apache.org>:
>
>> Hello,
>> Both 2.2.3 and 3.3.1 are no longer supported. Would it be possible for
>> you to move on to our 4.x code line on top of HBase 0.98?
>> Thanks,
>> James
>>
>> On Mon, Aug 31, 2015 at 11:04 PM, 刘春珲 <le...@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> Recently, I'v updated phoenix from 2.2.3 to 3.3.1.  I am confused about
>>> paged queries with index.
>>>
>>> first, I create a table and a index
>>>
>>> ----------------------------------------------------------------------
>>> ----------
>>> CREATE TABLE IF NOT EXISTS BD_PASS_INFO_T (
>>>    LICENSE_NO VARCHAR not null,
>>>    PASS_TIME DATE not null,
>>>    PASS_PORT_SN VARCHAR not null,
>>>    SPEED decimal,
>>>    DATA_SOURCE VARCHAR
>>>    CONSTRAINT PK PRIMARY KEY (LICENSE_NO, PASS_TIME DESC, PASS_PORT_SN)
>>> );
>>>
>>> CREATE INDEX IDX_BD_PASS_INFO_T ON BD_PASS_INFO_T
>>> (
>>>    PASS_TIME DESC, PASS_PORT_SN, LICENSE_NO
>>> )
>>> INCLUDE
>>> (
>>>    SPEED, DATA_SOURCE
>>> )SALT_BUCKETS = 8;
>>> ----------------------------------------------------------------------
>>> ----------
>>>
>>> then, I run these paged queries with different conditions (each time
>>> with one
>>> -- comment condition, and the result is in the /**/)
>>> BTW, These paged queries works well on 2.2.3.
>>> ----------------------------------------------------------------------
>>> ----------
>>> EXPLAIN
>>> SELECT
>>> TO_CHAR( PASS_TIME) ,     PASS_PORT_SN,     LICENSE_NO
>>> FROM BD_PASS_INFO_T
>>> WHERE
>>> (PASS_TIME, PASS_PORT_SN, LICENSE_NO) <= (TO_DATE('2015-08-09
>>> 12:59:58'),'141207', 'AU4853')
>>> AND PASS_TIME > TO_DATE('2014-08-01 16:00:00')              /*
>>> DEGENERATE SCAN OVER BD_PASS_INFO_T */
>>> --AND PASS_TIME = TO_DATE('2014-08-07 16:00:00')            /*
>>> DEGENERATE SCAN OVER BD_PASS_INFO_T */
>>> --AND PASS_PORT_SN = '141207'                               /*
>>> DEGENERATE SCAN OVER BD_PASS_INFO_T */
>>> --AND LICENSE_NO='AU4853'                                   /* Exec */
>>> --AND DATA_SOURCE='CY'                                      /* Exec */
>>> --AND SPEED>0                                               /* Exec */
>>> ORDER BY PASS_TIME DESC
>>> LIMIT 10;
>>> ----------------------------------------------------------------------
>>> ----------
>>>
>>> Then, I tried the following paged queries. All queries have results;
>>> ----------------------------------------------------------------------
>>> ----------
>>> EXPLAIN
>>> SELECT
>>> TO_CHAR( PASS_TIME) ,     PASS_PORT_SN,     LICENSE_NO
>>> FROM BD_PASS_INFO_T
>>> WHERE
>>> (LICENSE_NO, PASS_TIME, PASS_PORT_SN) <= ('AU4853', TO_DATE('2015-08-09
>>> 12:59:58'),'141207')
>>> AND PASS_TIME > TO_DATE('2014-08-01 16:00:00')
>>> --AND PASS_TIME = TO_DATE('2014-08-07 16:00:00')
>>> --AND PASS_PORT_SN = '141207'
>>> --AND LICENSE_NO='AU4853'
>>> --AND DATA_SOURCE='CY'
>>> --AND SPEED>0
>>> ORDER BY PASS_TIME DESC
>>> LIMIT 10;
>>> ----------------------------------------------------------------------
>>> ----------
>>>
>>> Any help?
>>>
>>> Chunhui Liu
>>> Thanks.
>>>
>>
>>
>