[
https://issues.apache.org/jira/browse/PHOENIX-2319?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
James Taylor updated PHOENIX-2319:
----------------------------------
Attachment: PHOENIX-2319.patch
Here's a patch for the issue. Thanks again for the detail and the unit test.
Please let me know if it solves your issue. Your unit test and the other ones
pass now.
[~tdsilva] - would you mind reviewing?
> 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: PHOENIX-2319.patch,
> 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';
> --no result
> SELECT * FROM IDX_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)
