[ https://issues.apache.org/jira/browse/PHOENIX-2319?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Chunhui Liu updated PHOENIX-2319: --------------------------------- Attachment: it-test-table-only-has-primary-key-index.patch integration-test, Secondary Index on table only has primary key. > 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 > Attachments: it-test-table-only-has-primary-key-index.patch > > > 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)