Trino | Trino's summer of grammar

Trino’s summer of grammar

Trino blog

News from the community of users and contributors

What a query engine runs, before anything else, is a language. And like any language, SQL<br>is defined by its grammar: the predicates, operators, and forms you’re allowed<br>to write down. Trino has always spoken SQL fluently, but the ISO 9075<br>standard is a big book, and there have<br>always been a few corners of it we hadn’t gotten around to implementing yet.

Trino 482 closes a remarkable number of those gaps in a single release. So many,<br>in fact, that we started calling it the summer of grammar. This post walks<br>through the new language features, and because reading SQL is never quite as<br>convincing as running it, every example below is live. Hit Run and watch<br>Trino 482 evaluate it for real.

BETWEEN, both ways #

Let’s start with an old friend. Everyone knows<br>x BETWEEN a AND b: it’s just<br>shorthand for a . The catch is that the order matters. If you<br>get the bounds backwards, the predicate is silently always false, because nothing<br>is simultaneously >= 10 and .

The SQL standard has a fix for this that Trino didn’t previously support: the<br>SYMMETRIC keyword. x BETWEEN SYMMETRIC a AND b treats the two bounds as an<br>unordered pair, so it’s true whenever x falls between the smaller and the<br>larger, regardless of which you wrote first. ASYMMETRIC (the default) spells<br>out the classic order-sensitive behavior.

SELECT 5 BETWEEN SYMMETRIC 10 AND 1 AS symmetric,<br>5 BETWEEN ASYMMETRIC 10 AND 1 AS asymmetric

This is genuinely useful when the bounds come from columns or parameters and you<br>can’t guarantee which one is larger.

Three-valued logic, made explicit #

No discussion of SQL is complete without an exploration of the semantics of<br>null. SQL uses three-valued<br>logic: a<br>boolean expression can be true, false, or unknown (represented by null).<br>That third value is where a lot of subtle bugs live, because NOT (a > b) is<br>not the same as a once null enters the picture.

The standard’s answer is the<br>IS [NOT] TRUE, IS [NOT] FALSE, and IS [NOT]<br>UNKNOWN predicates, and they now work<br>in Trino. Unlike =, these always return true or false, and never null. That<br>is exactly what you want when you need to collapse three-valued logic back down<br>to two.

SELECT (1 > 2) IS FALSE AS is_false,<br>(1

Looking inside subqueries #

Two new predicates let you ask questions about the shape of a subquery’s<br>results, not just its values.

The UNIQUE predicate is true when no two rows returned by a subquery are equal.<br>It’s the declarative way to assert “this subquery has no duplicates” without<br>contorting yourself into a GROUP BY ... HAVING count(*) > 1 and checking<br>whether it’s empty.

SELECT UNIQUE (SELECT x FROM (VALUES 1, 2, 3) t(x)) AS all_distinct,<br>UNIQUE (SELECT x FROM (VALUES 1, 2, 2) t(x)) AS has_duplicate

The MATCH predicate tests whether a row value appears in a subquery’s results.<br>Add the UNIQUE keyword and it’s true only when the row matches exactly one<br>row, a neat way to express “this value exists, and there’s only one of it.”

SELECT 2 MATCH (SELECT x FROM (VALUES 1, 2, 3) t(x)) AS found,<br>2 MATCH UNIQUE (SELECT x FROM (VALUES 1, 2, 2) t(x)) AS found_once

CASE gets some opinions #

A simple CASE expression<br>(CASE x WHEN 1 THEN ... WHEN 2 THEN ... END) traditionally only compares the<br>operand for equality against each WHEN value. If you wanted ranges or IS NULL<br>checks, you had to switch to a searched CASE and repeat the operand in every<br>branch.

No longer. The WHEN clauses of a simple CASE can now contain comparison<br>operators, BETWEEN, and IS NULL, so you write the operand once and let each<br>branch apply its own predicate to it.

SELECT x,<br>CASE x<br>WHEN

Time, locally #

Trino has long supported<br>AT TIME ZONE to render a timestamp in<br>a specific zone. The standard also defines AT LOCAL, which converts a value to<br>the session’s own time zone without you having to name it explicitly. It’s the<br>difference between “show me this in America/Los_Angeles” and “show me this<br>wherever I happen to be.”

SELECT TIMESTAMP '2026-06-21 14:00:00 America/Los_Angeles' AT LOCAL AS in_my_zone

The result above is rendered in the session’s time zone. Change the session zone<br>and the same expression follows you there.

Calling functions with named arguments #

When a function takes more than two or three arguments, positional calls become a<br>guessing game: which argument was the fourth one, again? Trino 482 adds the<br>standard name => value syntax for passing arguments by name, in any order.

This is especially handy for table<br>functions, which often have several optional<br>parameters, but it works for any function whose parameters are named, including<br>the user-defined functions you write yourself. Notice<br>that the call below supplies the arguments in the opposite order from the<br>declaration, and gets the right answer anyway:

WITH FUNCTION add_tax(price double, rate double)<br>RETURNS double<br>RETURN price * (1 + rate)<br>SELECT add_tax(rate => 0.20, price => 100.0) AS...