[SOLVED] Postgresql lateral join on condition evaluation order

Postgresql lateral join on condition evaluation order

Hello I am having problem with postgresql lateral join. My query is this:

SELECT *
FROM foo
LEFT JOIN LATERAL (SELECT tsrange(now() at time zone 'utc', expiration_date) as time_range)
                   ON (expiration_date > (now() at time zone 'utc') AND type = 'FOO')

It fails with "[22000] ERROR: range lower bound must be less than or equal to range upper bound" as if the result of the lateral join was evaluated before the ON condition.

I am trying to get all results and for those with expiration date > now() calculate the range so filtering in the main query is not an option.

I tried to rewrite the query with WHERE condition in the lateral join but it didnt help.

I would expect that the "lateral join" would be evaluated only for the rows matching the conditions so the error should never happen since expiration date > now().

Any idea how to fix this?

Postgresql version 16.3

Solution

It fails as if the result of the lateral join was evaluated before the ON condition.

Yes, of course it is. Typically the join condition is evaluated against the content of the row!

I think the solution you are looking for is

SELECT
  *,
  (CASE WHEN expiration_date > (now() at time zone 'utc') AND type = 'FOO' THEN
    tsrange(now() at time zone 'utc', expiration_date)
  END) AS time_range
FROM foo;