LET clause

Hi

ne 3. 1. 2021 v 13:13 odesílatel Joel Jacobson <joel@compiler.org> napsal:

Hi hackers,

I just learned about a feature called "LET clause".

It's not part of the SQL standard, but it's supported by Oracle
[1], Couchbase [2] and AsterixDB [3].

This is not SQL language - it uses EQL language

It looks like this is only available in one Oracle's product - Oracle®
EndecaServer. In this environment the PL/SQL is not available there, so
some procedural features are necessary, but I don't see a high benefit of
this feature in environments with procedural languages - PL/SQL or
PL/pgSQL.

https://en.wikipedia.org/wiki/Endeca

Regards

Pavel

On 1/3/21 1:12 PM, Joel Jacobson wrote:

Hi hackers,

I just learned about a feature called "LET clause".

It's not part of the SQL standard, but it's supported by Oracle [1], Couchbase [2] and AsterixDB [3].

I searched the pgsql-hackers archives and couldn't find any matches on "LET clause",
so I thought I should share this with you in some people didn't know about it like me.

"LET clauses can be useful when a (complex) expression is used several times within a query, allowing it to be written once to make the query more concise." [3]

In the mentioned other databases you can do this with the LET keyword, which "creates a new variable and initializes it with the result of the expression you supply".

Without the LET clause, your complex queries would need to be divided into two separate queries:

* One query to get a particular value (or set of values), and
* One query to use the value (or values) from the first query.

The example below computes the Easter month and day for a given year:

Work-around using CROSS JOIN LATERAL:

CREATE FUNCTION compute_easter_day_for_year(year integer)
RETURNS date
LANGUAGE sql
AS $$
SELECT make_date(year, easter_month, easter_day)
FROM (VALUES (year % 19, year / 100)) AS step1(g,c)
CROSS JOIN LATERAL (VALUES ((c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30)) AS step2(h)
CROSS JOIN LATERAL (VALUES (h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)))) AS step3(i)
CROSS JOIN LATERAL (VALUES ((year + year/4 + i + 2 - c + c/4) % 7)) AS step4(j)
CROSS JOIN LATERAL (VALUES (i - j)) AS step5(p)
CROSS JOIN LATERAL (VALUES (3 + (p + 26)/30, 1 + (p + 27 + (p + 6)/40) % 31)) AS step6(easter_month, easter_day)
$$;

(Other possible work arounds: Use MATERIALIZED CTEs or sub-queries with OFFSET 0 to prevent sub-query flattening.)

If we instead would have LET clauses in PostgreSQL, we could do:

CREATE FUNCTION compute_easter_day_for_year(year integer)
RETURNS date
LANGUAGE sql
AS $$
SELECT make_date(year, easter_month, easter_day)
LET
g = year % 19,
c = year / 100,
h = (c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30,
i = h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)),
j = year + year/4 + i + 2 - c + c/4) % 7,
p = i - j,
easter_month = 3 + (p + 26)/30,
easter_day = 1 + (p + 27 + (p + 6)/40) % 31
$$;

Without LET clauses, SQL isn't terribly well suited to execute fundamentally stepwise imperative algorithms like this one.

The work-around is to either sacrifice performance and conciseness and use a hack (CROSS JOIN LATERAL or CTE),
or, leave the SQL realm and use a PL like plpgsql to get good performance and conciseness.

I agree on the conciseness, but I'm wondering what performance problem
you think there is with the CROSS JOIN LATERAL VALUES technique. Have
you tried running an EXPLAIN (ANALYZE, VERBOSE) on that?

I have no opinion if this is something for PostgreSQL,
since I have no idea on how complicated this would be to implement,
which means I can't estimate if the increased complication of an implementation
would outweigh the possible added convenience/performance/conciseness gains.

I just wanted to share this in case this idea was unknown to some people here.

[1] https://docs.oracle.com/cd/E93962_01/bigData.Doc/eql_onPrem/src/reql_statement_let.html
[2] https://docs.couchbase.com/server/current/n1ql/n1ql-language-reference/let.html
[3] https://asterixdb.apache.org/docs/0.9.3/sqlpp/manual.html#Let_clauses

Kind regards,

Joel

--
Vik Fearing

On Sat, Jan 16, 2021, at 17:21, Vik Fearing wrote:

I agree on the conciseness, but I'm wondering what performance problem
you think there is with the CROSS JOIN LATERAL VALUES technique. Have
you tried running an EXPLAIN (ANALYZE, VERBOSE) on that?

Yes, I've tried, it results in the same problem due to flattening:

PREPARE calc_easter_day_for_year AS
SELECT make_date($1::integer, easter_month, easter_day)
FROM (VALUES ($1::integer % 19, $1::integer / 100)) AS step1(g,c)
CROSS JOIN LATERAL (VALUES ((c - c/4 - (8*c + 13)/25 + 19*g + 15) % 30)) AS step2(h)
CROSS JOIN LATERAL (VALUES (h - (h/28)*(1 - (h/28)*(29/(h + 1))*((21 - g)/11)))) AS step3(i)
CROSS JOIN LATERAL (VALUES (($1::integer + $1::integer/4 + i + 2 - c + c/4) % 7)) AS step4(j)
CROSS JOIN LATERAL (VALUES (i - j)) AS step5(p)
CROSS JOIN LATERAL (VALUES (3 + (p + 26)/30, 1 + (p + 27 + (p + 6)/40) % 31)) AS step6(easter_month, easter_day)
;

SET plan_cache_mode = 'force_generic_plan';

EXPLAIN (ANALYZE, VERBOSE) EXECUTE calc_easter_day_for_year(2021);

Result (cost=0.00..1.14 rows=1 width=4) (actual time=1.612..1.616 rows=1 loops=1)
Output: make_date($1, (3 + (((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 + ($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 26) / 30)), (1 + ((((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 + ($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 27) + (((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11))))) - (((((($1 + ($1 / 4)) + ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) - (((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (1 - ((((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) / 28) * (29 / ((((((($1 / 100) - (($1 / 100) / 4)) - (((8 * ($1 / 100)) + 13) / 25)) + (19 * ($1 % 19))) + 15) % 30) + 1))) * ((21 - ($1 % 19)) / 11)))))) + 2) - ($1 / 100)) + (($1 / 100) / 4)) % 7)) + 6) / 40)) % 31)))
Planning Time: 6.132 ms
Execution Time: 2.094 ms
(4 rows)