[
https://issues.apache.org/jira/browse/PHOENIX-172?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16650979#comment-16650979
]
Lars Hofhansl commented on PHOENIX-172:
---------------------------------------
Found this for SQL Server. Addresses exactly the pagination problem in the end:
The operators less than (<), greater than (>), less than or equal to (<=), and
greater than or equal to (>=) follow the rules for ordering operations in the
standard. Use of these operators with vector expressions can significantly
reduce the length and complexity of predicates, as well as lends itself to good
optimization. Following are examples of predicates and their logical
equivalents that SQL Server does support (as of SQL Server 2008 R2).
Less than operator:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) < (@p1, @p2, @p3);
Logically equivalent to:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 < @p1)
OR (c1 = @p1 AND c2 < @p2)
OR (c1 = @p1 AND c2 = @p2 AND c3 < @p3);
Greater than operator:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) > (@p1, @p2, @p3);
Logically equivalent to:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 > @p1)
OR (c1 = @p1 AND c2 > @p2)
OR (c1 = @p1 AND c2 = @p2 AND c3 > @p3);
Less than or equal to operator:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) <= (@p1, @p2, @p3);
Logically equivalent to:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 = @p1 AND c2 = @p2 AND c3 <= @p3)
OR (c1 = @p1 AND c2 < @p2)
OR (c1 < @p1);
Greater than or equal to operator:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) >= (@p1, @p2, @p3);
Logically equivalent to:
SELECT keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1 = @p1 AND c2 = @p2 AND c3 >= @p3)
OR (c1 = @p1 AND c2 > @p2)
OR (c1 > @p1);
You can clearly see the benefits in the support for row value constructors by
these operators in terms of reducing the length of code and complexity compared
with the logically equivalent options. The benefits of the equals and not equal
to operators are easy to see in business cases. As for the other operators,
such as greater than, an example for a business case is paging. Suppose that
you’re implementing a paging solution that involves multiple sort columns
(e.g., c1, c2, c3). For the paging solution to be deterministic, you need to
ensure that together, the ordering elements uniquely identify a row. If not,
you can add the primary key as a tiebreaker. At any rate, whenever the user
asks for the next page, the application invokes a procedure, passing the values
of the ordering elements from the last row in the last page retrieved. The
procedure can then invoke a query that filters all rows greater than the passed
anchor row, like so:
SELECT TOP(@pagesize) keycol, c1, c2, c3
FROM dbo.T1
WHERE (c1, c2, c3) > (@p1, @p2, @p3)
ORDER BY c1, c2, c3;
This code is clear and concise, and it lends itself to good optimization
provided an index is defined on (c1, c2, c3).
It’s important to note that row value constructors don’t address a couple of
needs related to this paging idea. First, the ordering direction of all
elements is the same (ascending when using > and descending when using <).
Second, treatment of three-valued logic in terms of NULLs is different than
with ordering in the sense that a comparison with a NULL yields UNKNOWN and the
row is filtered out. But this feature could be very useful as long as all
ordering elements are defined as NOT NULL and you do need them all to be in the
same direction.
> Support the ability for a client to do query more
> -------------------------------------------------
>
> Key: PHOENIX-172
> URL: https://issues.apache.org/jira/browse/PHOENIX-172
> Project: Phoenix
> Issue Type: Task
> Affects Versions: 2.1.0
> Reporter: James Taylor
> Assignee: Samarth Jain
> Labels: enhancement
>
> Clients often need the ability to "page" through query results that have more
> row results than are displayable on a single screen. SQL provides LIMIT and
> OFFSET which is often used for this purpose, however supporting OFFSET in a
> performant manner is not possible with HBase. Instead, an alternate way of
> supporting query-more functionality (suggested by @larsh) is through [row
> values
> constructors](http://publib.boulder.ibm.com/infocenter/soliddb/v6r3/index.jsp?topic=/com.ibm.swg.im.soliddb.sql.doc/doc/row.value.constructors.html)
> which has been in the SQL spec since SQL-92. It has PostGres support as
> well with the benefits explained well
> [here](https://wiki.postgresql.org/images/3/35/Pagination_Done_the_PostgreSQL_Way.pdf)
> .
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
