[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16682015#comment-16682015
]
Lars Hofhansl commented on PHOENIX-4845:
----------------------------------------
Sigh. Agreed!
The problem (or let me say challenge) with CURSOR is that it's stateful, now we
need to keep track of the state somewhere - unless we say the CURSOR is client
specific and pass the needed information back to the server each time.
I suppose in all cases the problem comes back to: How exactly do we mark a
specific location in a table from which we can continue.
So unless you want to create a snapshot each time and have some kind of index
based access you'd have to solve the same problem.
INVERT does invert the bits and it's clearly defined. But it is only
tangentially related to the sort order (and in fact right now we really just
invert the bits).
Perhaps [~tdsilva]'s OFFSET idea fits best in the end. We simply somehow need
to tell HBase to "start here". It's brain-dead simple in HBase (in fact it's
how HBase skips from region to region), we just do not have a good SQL
construct for this. Once we have that, then we can easily wrap into a CURSOR
type construct.
> 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)
