Comparison predicates with row value constructors

[glebm]

SQL '92 standard, Predicates 209, defines row values comparison predicates as:

Let Rx and Ry be the two row value constructors of the comparison predicate and let RXi and RYi be the i-th row value constructor elements of Rx and Ry, respectively. "Rx comp op Ry" is true, false, or unknown as follows:

a) "x = Ry" is true if and only if RXi = RYi for all i.
b) "x <> Ry" is true if and only if RXi <> RYi for some i.
c) "x < Ry" is true if and only if RXi = RYi for all i < n and RXn < RYn for some n.
d) "x > Ry" is true if and only if RXi = RYi for all i < n and RXn > RYn for some n.

Example:

SELECT *
  FROM sales
 WHERE (sale_date, sale_id) < (?, ?)
 ORDER BY sale_date DESC, sale_id DESC

They are only on supported on certain databases (postgres yes, mysql no), and additional fiddling would probably be required to deal with array attributes (priority: high medium low) and differences in order direction between sort attributes.

I won't work on this in the near future, but this would be great to do on databases that support it.

[fatalmind]

Hi!

Besides the row values, there is also a performance problem with the logic as shown in the README:

x0 OR 
y0 AND (x1 OR
        y1 AND (x2 OR
                y2 AND ...))

The problem is that this kind of where-clause cannot use and index on these columns, even if it exists. No matter which database.

See here: http://use-the-index-luke.com/sql/partial-results/fetch-next-page#sb-equivalent-logic

To use an index, you must not have an OR on the top level comparison.

Let me know if you have any questions.

-Markus Winand