[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16870055#comment-16870055
]
Lars Hofhansl edited comment on PHOENIX-4845 at 6/22/19 4:53 AM:
-----------------------------------------------------------------
So meaning you just rewrite the query as such to get the right scan boundaries,
not to actually execute the query that way?
And then the task become to optimize
{{SELECT * FROM TABLE WHERE (A > 1 OR (A = 1 AND (B < 2 OR (B = 2 AND (C >=
3)))))}} in setting the start row on a set of scans?
In that case, why not just leave this as the syntax in the first place?
Or in other words... If we can teach Phoenix how to optimize {{SELECT * FROM
TABLE WHERE (A > 1 OR (A = 1 AND (B < 2 OR (B = 2 AND (C >= 3)))))}} OFFSET is
just (unnecessary?) syntactic sugar.
was (Author: lhofhansl):
So meaning you just rewrite the query as such to get the right scan boundaries,
not to actually execute the query that way?
And then the task become to optimize
{{SELECT * FROM TABLE WHERE (A > 1 OR (A = 1 AND (B < 2 OR (B = 2 AND (C >=
3)))))}} in setting the start row on a set of scans?
In that case, why not just leave this as the syntax in the first place?
> Support using Row Value Constructors in OFFSET clause for 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
> Assignee: Daniel Wong
> 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)
