[
https://issues.apache.org/jira/browse/PHOENIX-2319?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Chunhui Liu updated PHOENIX-2319:
---------------------------------
Description:
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}
was:
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}
> 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';
> --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)
