[
https://issues.apache.org/jira/browse/PHOENIX-3787?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15989522#comment-15989522
]
James Taylor commented on PHOENIX-3787:
---------------------------------------
This might be a duplicate of PHOENIX-3383. The (ugly) workaround for that was
to reverse the > operator to be <. FWIW, if this is the same issue, then when
PHOENIX-3383 is fixed, the operator would need to be switched back.
> 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)
