phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chunhui Liu <leeyc...@gmail.com>
Subject Re: SELECT failed on secondary index when table's columns are ALL primary key
Date Thu, 15 Oct 2015 02:49:29 GMT
Hi James,

https://issues.apache.org/jira/browse/PHOENIX-2319

Thanks,
Chunhui Liu

2015-10-14 14:07 GMT+08:00 James Taylor <jamestaylor@apache.org>:

> Hi Chunhui,
> This sounds like a bug. What version of Phoenix are you using? Please file
> a JIRA and if you could include a simple unit test that demonstrates the
> issue, that would be much appreciated.
> Thanks,
> James
>
> On Tue, Oct 13, 2015 at 11:04 PM, Chunhui Liu <leeychee@gmail.com> wrote:
>
>> Hi Team,
>>
>> Recently, When I try to learn paged query on table with secondary index,
>> I got this.
>>
>> 1. create a table, and use all columns(eg. pk1, pk2) as constraint pk;
>> 2. create a secondary index on the table, use (pk2, pk1) as constraint pk;
>> 3. create some test data;
>> 4. select pk1, pk2 from t where pk2='202';
>> 5. no result;
>>
>> Test on
>> phoenix4.5.2-hbase-0.98, cdh 5.3.6
>>
>> -----------------------------------------------------------------
>>
>> --Create table, all columns are primary key.
>> CREATE TABLE IF NOT EXISTS T (
>>    PK1 VARCHAR not null,
>>    PK2 VARCHAR not null,
>>    CONSTRAINT PK PRIMARY KEY (PK1, PK2)
>> );
>>
>> --Create secondary index
>> CREATE INDEX IDX_T ON T
>> (
>>    PK2, PK1
>> );
>>
>> --Test data
>> UPSERT INTO T VALUES('100', '200');
>> UPSERT INTO T VALUES('101', '201');
>> UPSERT INTO T VALUES('102', '202');
>> UPSERT INTO T VALUES('103', '203');
>> UPSERT INTO T VALUES('104', '204');
>>
>> --make sure data was created correctly.
>> SELECT * FROM T;
>>
>> --success
>> SELECT PK1, PK2 FROM T WHERE PK1='102';
>>
>> --*no result* with conditions(pk2[=,>,<,>=,<=]'202')
>> EXPLAIN
>> SELECT PK1, PK2 FROM T WHERE PK2 = '202';
>>
>> --success
>> EXPLAIN
>> SELECT /*+ NO_INDEX*/ PK1, PK2 FROM T WHERE PK2 = '202';
>>
>> --cleanup
>> DROP TABLE IF EXISTS T;
>>
>> -----------------------------------------------------------------
>>
>> *Then*, I create a table with extra column(KV), SELECT is ok.
>>
>> -----------------------------------------------------------------
>> CREATE TABLE IF NOT EXISTS T (
>>    PK1 VARCHAR not null,
>>    PK2 VARCHAR not null,
>>    KV  VARCHAR,
>>    CONSTRAINT PK PRIMARY KEY (PK1, PK2)
>> );
>>
>> --Create secondary index
>> CREATE INDEX IDX_T ON T
>> (
>>    PK2, PK1
>> );
>> -----------------------------------------------------------------
>>
>> Is this a bug?
>>
>> Chunhui,
>> Thanks.
>>
>
>

Mime
View raw message