Thomas D'Silva created PHOENIX-4845:
---------------------------------------
Summary: 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
RVCs along with the LIMIT clause are useful for efficiently paging through rows
(see [http://phoenix.apache.org/paged.html]).
However if the sorder order of the PK columns in a table varies we cannot use
RVCs.
For eg. if the PK of a table is (A DESC, B) we cannot use the following query
to page through the data
{code:java}
SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B LIMIT 20
{code}
Since the rows are sorted by A desc and then by B descending we need to use the
following query
{code:java}
SELECT * FROM TABLE WHERE (A < ? OR (A=? AND B>?)) ORDER BY A DESC, B LIMIT 20
{code}
If we supported using RVCs in the offset clause we could use the offset to set
the start row of the scan. Also clients would not have to generate a
complicated query.
{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)
