As my title states, I would like to know what we call types of queries with that type of condition in the WHERE clause, i.e.:
SELECT * FROM mytable
WHERE (col1, col2) < (1, 2);
In other words:
Give me all records where col1 is less than '1' or if it equals '1' then col2 must be less than '2' - and none of the values are NULL.
I really like this type of syntax, but don't know what the naming convention is on how to refer to this type of condition. It looks like a tuple conditional but that name is not giving me anything from my searches.
My question stems from needing to know what this syntax is called in order to research how to write this using Criteria API with Hibernate and JPA2 and Postgres.
I was able to write this using Criteria API using CriteriaBuilder's function() call:
//Our left expression (date, id)
Expression leftVal = criteriaBuilder.function("ROW", Tuple.class,
from.get("date").as(java.util.Date.class),
from.get("id").as(Long.class));
//Our right expression ex: ('2015-09-15', 32450)
ParameterExpression<Date> dateParam = criteriaBuilder.parameter(Date.class);
ParameterExpression<Long> idParam = criteriaBuilder.parameter(Long.class);
Expression rightVal = criteriaBuilder.function("ROW", Tuple.class, dateParam, idParam)
//build the first predicate using ROW expressions
Predicate predicate = criteriaBuilder.greaterThan(leftVal, rightVal);
//more query building happens
...
//construct final query and add parameters to our param expressions
TypedQuery<MyEntity> typedQuery = em.createQuery(criteriaQuery);
typedQuery.setParameter(dateParam, current.getDate());
typedQuery.setParameter(idParam, current.getId());
current in this case is the record I retrieve as the row we want to get records BEFORE or AFTER. In this example I do after as noted by the greaterThan function call.
Common terms for the operation you demonstrate are "Row values", "Row value comparison", "Row constructor comparison" or "Row-wise comparison".
Commonly used in "keyset pagination".
The feature has been in the SQL standard since SQL-92 (!). Postgres is currently the only major RDBMS that supports it in all aspects - in particular also with full index support.
The expression (col1, col2) < (1, 2) is short syntax for ROW(col1, col2) < ROW(1, 2) in Postgres.
The expression ROW(col1, col2) is a "row constructor", like ARRAY[col1, col2] is an "array constructor".
Row-wise comparison is conveniently short for the more verbose, equivalent expression:
col1 < 1 OR (col1 = 1 AND col2 < 2)
Postgres can use a multicolumn index on (col1, col2) or (col1 DESC, col2 DESC) for this. But not an index with mixed sort direction like (col1 ASC, col2 DESC)!
The expression is notably distinct from: (!)
col1 < 1 AND col2 < 2
Consider the example row value: (1,1) ...
Here is a presentation by Markus Winand that discusses the feature for pagination in detail:
Row value comparison starts on page 20. The support matrix I have been referring to is on page 45.
I am in no way affiliated to Markus Winand.