[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16681918#comment-16681918
]
Daniel Wong commented on PHOENIX-4845:
--------------------------------------
All this back and forth makes me still internally prefer using SQL standard
cursor type syntax if I as a user of Phoenix want to page through my data.
Consider as a user I have a table TABLE1 and I want to page through some
projection of it.
SELECT col1, col2 FROM TABLE1 WHERE <Cond> ORDER BY <Ordering Expression>.
--This is the query I want to page through.
With the proposed changes that act on a column level whether its DESC or INVERT
or something else I now have to do this workflow.
1) Investigate all the columns in the base schema as well as the indexes to
see if <Ordering Expression> matches.
2a) If they match add INVERT/DESC to all the columns where the Index or Base
table has INVERT/DESC
2b) If they do not match, assume base table will be used and then sorted so
and add INVERT/DESC to match base table.
3a) Store the index or base table user declared key values for requery.
3b) Store the base tables user declared key values for requery.
This is in my opinion a poor user experience for something that should be
relatively speaking straight forward.
1) CREATE CURSOR cursor1 FROM SELECT col1, col2 FROM TABLE1 WHERE <Cond>
ORDER BY <Ordering Expression>
2) FETCH NEXT 1000 FROM cursor1 AS col1, col2
If not cursor I would prefer at least something to page that requires less
investigation on all the possible table and index schemas.
If this is too much work, is it possible to pick a work around that is less
schema specific?
> Support using Row Value Constructors in OFFSET clause to support paging in
> tables where the sort order of PK columns varies
> ---------------------------------------------------------------------------------------------------------------------------
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
> Reporter: Thomas D'Silva
> Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns.
>
> FYI [~jfernando_sfdc]
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
