SQL syntax term for 'WHERE (col1, col2) < (val1, val2)'

Question

As my question 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.

EDIT

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.

Fresno

Manual docs.jboss.org/hibernate/core/3.3/reference/en/html/… This is called Row Value Constructor – Thorman

I took the liberty to adjust the definition of what the expression does. The original "Give me all records where either col1 is less than '1' or if not, where col2 is less than '2'" would not capture it precisely. – Nonagon

Answer 1

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".

That 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  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:

"Pagination done the PostgreSQL way" on use-the-index-luke.com.

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.}

Nonagon

Very nice find, this pagination example/slides was the EXACT way I wanted to do this (using row values for efficient queries with indexes on date and id) – Fresno

Answer 2

WHERE (col1, col2) < (val1, val2)

Above syntax is called Row Value Constructor/tuple syntax/Row Subquery.

From doc

ANSI SQL row value constructor syntax, sometimes referred to AS tuple syntax, even though the underlying database may not support that notion. Here, we are generally referring to multi-valued comparisons, typically associated with components

Alternatively it can be called Row Subqueries

Thorman

@Patrick For broader horizonts. In pure ANSI Standard it is called RVC. – Thorman

Answer 3

Expression Lists

Use of Row Value Constructors (RVC) in Comparison Predicates - quite a long term

RVCs are usually seen in INSERT statements, but rarely as part of WHERE clauses.

I doubt that this syntax has a direct support in JPA or Hibernate Criteria API, but there is always a workaround to achieve the same logic.

Foreignism

Well, nothing wrong with that. PostGreSQL and Oracle uses the same terms anyway. postgresql.org/docs/9.4/static/functions-comparisons.html RVC is included in ANSI – Foreignism

There is indeed a workaround to get the same logic ( (col1 > val1) OR (col1 = val1 AND col2 > val2)) but this actually has worse performance than using Row Value Constructors – Fresno

Apparently, it is still in the works for JPA: java.net/jira/browse/JPA_SPEC-90 – Foreignism

@Foreignism view the above edit i was able to do the ROW query :) – Fresno