sql: support tuple comparisons #6206
Description
Postgres supports comparing between tuples (semantically it's a lexicographical ordering):
SELECT * FROM t;
k | a | b | c
---+---+---+---
1 | 1 | 2 | 3
2 | 1 | 2 | 4
3 | 1 | 3 | 0
4 | 2 | 1 | 0
(4 rows)
SELECT * FROM t WHERE (a,b,c) > (1,2,3);
k | a | b | c
---+---+---+---
2 | 1 | 2 | 4
3 | 1 | 3 | 0
4 | 2 | 1 | 0
(3 rows)This can be useful for example when reading parts of a multi-column index. We don't support this (pq: unsupported comparison operator: <tuple> < <tuple>). To get the same behavior, the expression needs to be tediously rewritten:
SELECT * FROM t WHERE a > 1 OR (a = 1 AND (b > 2 OR (b = 2 AND c > 3)));
+---+---+---+---+
| k | a | b | c |
+---+---+---+---+
| 2 | 1 | 2 | 4 |
| 3 | 1 | 3 | 0 |
| 4 | 2 | 1 | 0 |
+---+---+---+---+Note that we do support tuple equality/inequality.