Jan Fernando created PHOENIX-3787:
-------------------------------------
Summary: RVC For Paged Queries not working as expected when PK
leads with column defined as 'DATE DESC'
Key: PHOENIX-3787
URL: https://issues.apache.org/jira/browse/PHOENIX-3787
Project: Phoenix
Issue Type: Bug
Affects Versions: 4.9.0, 4.10.0
Reporter: Jan Fernando
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;
--
This message was sent by Atlassian JIRA
(v6.3.15#6346)
