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