[PHOENIX-2327] Row value constructors failed on the index, when len(table's pks) > 2 and table's 1st pk is index's last pk

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.5.2
• Fix Version/s: 4.6.0

Environment

cdh5.3.6

• Assignee: James R. Taylor
• Reporter: Chunhui Liu
• Created: 16/Oct/15 00:49
• Updated: 21/Nov/15 02:16
• Resolved: 18/Oct/15 00:45

Description

1. Table has more than 2 primary keys;
2. Table's 1st pk as index's last pk; eg. table's pks are (pk1, pk2, pk3), the failed index's pks are (pk2, pk3, pk1); table's pks are (1, 2, 3, 4), failed index's pks are (2, 3, 4, 5, 1);
3. Use row value constructors on index with another condition that use one pks(not the table's 1st pk);
4. You will get "DEGENERATE SCAN OVER TABLE_NAME"

Here is the Test SQL

DROP TABLE IF EXISTS T;

CREATE TABLE IF NOT EXISTS T (
   PK1 VARCHAR not null,
   PK2 VARCHAR not null,
   PK3 VARCHAR not null,
   V1  VARCHAR,
   CONSTRAINT PK PRIMARY KEY (PK1, PK2, PK3)
);

CREATE INDEX IDX_T ON T
(
   PK2, PK3, PK1
);

UPSERT INTO T VALUES('100', '200', '300', 'V');
UPSERT INTO T VALUES('101', '201', '301', 'V');
UPSERT INTO T VALUES('102', '202', '302', 'V');
UPSERT INTO T VALUES('103', '203', '303', 'V');
UPSERT INTO T VALUES('104', '204', '304', 'V');

SELECT * FROM T;

EXPLAIN
SELECT PK1, PK2, PK3 FROM T WHERE 
(PK2, PK3, PK1) >= ('202', '302', '102') 
AND PK2 < '204'
LIMIT 10;

I've tried 3 primary key, here is the results.
1. table's pks are (pk1, pk2, pk3);
2. 132 means (pk1, pk3, pk2);

index's pks order	result
132	correct
213	correct
231	fail
312	correct
321	correct

I've also test this on table with 4, 5 pks

len(pks)	failed order
3	231
4	2341
5	23451