phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Chunhui Liu <leeyc...@gmail.com>
Subject Row value constructors failed on the index, when len(table's pks) > 2 and table's 1st pk is index's last pk
Date Thu, 15 Oct 2015 05:15:39 GMT
Hi team,

When I try to use paged query on secondary index, I found a issue.

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                                |


Thanks,
Chunhui

Mime
View raw message