Details
-
Bug
-
Status: Closed
-
Minor
-
Resolution: Fixed
-
4.5.2
-
None
-
None
-
cdh5.3.6
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;
--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;
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 );