phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From James Taylor <jamestay...@apache.org>
Subject Re: SELECT failed on secondary index when table's columns are ALL primary key
Date Wed, 14 Oct 2015 06:07:48 GMT
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