phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (PHOENIX-3787) RVC For Paged Queries not working as expected when PK leads with column defined as 'DATE DESC'
Date Mon, 01 May 2017 20:16:04 GMT

    [ https://issues.apache.org/jira/browse/PHOENIX-3787?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15991453#comment-15991453
] 

James Taylor commented on PHOENIX-3787:
---------------------------------------

FYI, [~jfernando_sfdc] - I've confirmed that this is a duplicate of PHOENIX-3383. Thank you
for the detailed repro steps - that makes things so much easier. The workaround that's in
use by other users is to reverse the > to a < in the query more query if the row key
has a descending column.

> 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)

Mime
View raw message