Chunhui Liu created PHOENIX-2319: ------------------------------------ Summary: SELECT failed on secondary index when table's columns are ALL primary key Key: PHOENIX-2319 URL: https://issues.apache.org/jira/browse/PHOENIX-2319 Project: Phoenix Issue Type: Bug Affects Versions: 4.5.2 Environment: cdh5.3.6 Reporter: Chunhui Liu Priority: Minor
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; {code:sql} --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; {code} Then, I create a table with extra column(KV), SELECT is ok. {code:sql} 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 ); {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)