[PHOENIX-3787] RVC For Paged Queries not working as expected when PK leads with column defined as 'DATE DESC'

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 4.9.0, 4.10.0
• Assignee: Unassigned
• Reporter: Jan Fernando
• Created: 14/Apr/17 00:28
• Updated: 13/Oct/18 07:41
• Resolved: 01/May/17 20:24

Description

Our application supports paged queries that utilize RVCs. We have a table where the PK leads with a column that is a DATE data type. The PK specifies the sort order of the DATE PK column as DESC.

The first query doesn't utilize an RVC but uses a limit to restrict the number of records for first page:

SELECT TO_CHAR(EVENTDATE, 'yyyy-MM-DD HH:mm:ss.SSS'), EVENTID, EVENTNAME FROM TEST.EVENT_MT_VIEW ORDER BY EVENTDATE DESC, EVENTID LIMIT 3;

We record the PK values of the last record returned and then issue a query with an RVC to get the next page and so on:

SELECT TO_CHAR(EVENTDATE, 'yyyy-MM-DD HH:mm:ss.SSS'), EVENTID, EVENTNAME
FROM TEST.EVENT_MT_VIEW
WHERE (EVENTDATE, EVENTID) > (TO_DATE('2017-01-28 20:38:00.000', 'yyyy-MM-dd HH:mm:ss.SS'), 'event3')
ORDER BY EVENTDATE DESC, EVENTID;

We expect that the RVC clause will honor the sort order of the Date column and page through the data in descending order. However this does not happen. The greater than operator appears to be applied literally. and we return the same set of data again, minus the last record.

This breaks our query paging application and users can't page through their data.

We utilize a Multi-tenant connection and specify the phoenix.query.force.rowkeyorder=true attribute on the connection.

You can repro this behavior with as follows:
1) Create table with Date Desc data type in PK
----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS TEST.EVENT (
ORGANIZATION_ID CHAR(15) NOT NULL,
EVENTDATE DATE NOT NULL,
EVENTID CHAR(15) NOT NULL,
EVENTNAME VARCHAR,
CONSTRAINT PK PRIMARY KEY
(
ORGANIZATION_ID,
EVENTDATE DESC,
EVENTID
)
) VERSIONS=1,MULTI_TENANT=true

2) Insert data into the table
------------------------------------
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 23:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event1', 'eventname1');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 22:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event2', 'eventname2');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 20:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event3', 'eventname3');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 19:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event4', 'eventname4');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 18:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event5', 'eventname5');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 17:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event6', 'eventname6');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 16:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event7', 'eventname7');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 15:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event8', 'eventname8');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 14:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event9', 'eventname9');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 13:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event10', 'eventname10');
UPSERT INTO TEST.EVENT VALUES ('Tenant1', TO_DATE('2017-01-28 12:38:00.000', 'yyyy-MM-dd HH:mm:ss.SSS'), 'event11', 'eventname11');

3) Create a Multi-tenant view using an MT connection with TenantId=Tenant1
-------------------------
CREATE VIEW TEST.EVENT_MT_VIEW AS SELECT * FROM TEST.EVENT;

4) Execute initial query - 3 rows returned
--------------------------------------------------------
SELECT TO_CHAR(EVENTDATE, 'yyyy-MM-DD HH:mm:ss.SSS'), EVENTID, EVENTNAME FROM TEST.EVENT_MT_VIEW ORDER BY EVENTDATE DESC, EVENTID LIMIT 3;

5) Execute RVC query - expect remaining 8 rows returned, by first 2 are returned again
---------------------
SELECT TO_CHAR(EVENTDATE, 'yyyy-MM-DD HH:mm:ss.SSS'), EVENTID, EVENTNAME
FROM TEST.EVENT_MT_VIEW
WHERE (EVENTDATE, EVENTID) > (TO_DATE('2017-01-28 20:38:00.000', 'yyyy-MM-dd HH:mm:ss.SS'), 'eventname3')
ORDER BY EVENTDATE DESC, EVENTID;

Issue Links

• is related to: PHOENIX-3383 Comparison between descending row keys used in RVC is reverse