You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by Chunhui Liu <le...@gmail.com> on 2015/10/14 08:04:05 UTC
SELECT failed on secondary index when table's columns are ALL primary key
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.
Re: SELECT failed on secondary index when table's columns are ALL
primary key
Posted by Chunhui Liu <le...@gmail.com>.
Hi James,
https://issues.apache.org/jira/browse/PHOENIX-2319
Thanks,
Chunhui Liu
2015-10-14 14:07 GMT+08:00 James Taylor <ja...@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 <le...@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.
>>
>
>
Re: SELECT failed on secondary index when table's columns are ALL
primary key
Posted by James Taylor <ja...@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 <le...@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.
>