If LATERAL is optional for table-valued functions, then why does this query error without it?

Question

Setup

CREATE TABLE persons
(
  person_id int not null,
  name TEXT 
);
INSERT INTO persons VALUES
(1, 'Adam'),
(2, 'Paul'),
(3, 'Tye'),
(4, 'Sarah');
CREATE TABLE json_to_parse
(
  person_id int not null,
  block json
);
INSERT INTO json_to_parse VALUES
  (1, '{"size": "small", "love": "x"}'),
  (2, '{"size": "medium", "love": "xx"}'),
  (3, '{"size": "big", "love": "xxx"}');

The Error

This runs without issue

SELECT
  *
FROM
  json_to_parse
CROSS JOIN LATERAL
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id;

but this does not

SELECT
  *
FROM
  json_to_parse,
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id;

and I get the error "invalid reference to FROM-clause entry for table "json_to_parse""

Why does this second query error? The docs make it quite clear that LATERAL is optional for table-valued functions

Table functions appearing in FROM can also be preceded by the key word LATERAL, but for functions the key word is optional; the function's arguments can contain references to columns provided by preceding FROM items in any case.

dbfiddle

Answer 1

The key word LATERAL is always optional for functions in the FROM list. That's not the issue in your query.

Explicit JOIN binds stronger than a comma in the FROM list. That's the issue. See:

• What does [FROM x, y] mean in Postgres?

Your second query is logically wrong. json_to_record(json_to_parse.block) binds to persons before the result of both would be joined with json_to_parse, which contradicts the early reference to json_to_parse.block. Hence the error.

There is nothing wrong with commas in the FROM list per se. It's just typically easier to read and maintain when you put the main joining condition between two tables in the ON or USING clause of a JOIN where feasible. (And it makes a functional difference for OUTER JOIN!). Use the comma freely where correct and appropriate.

While we are giving opinions, this is how I would write your query:

SELECT *  -- really all columns from all tables?
FROM   persons p
JOIN   json_to_parse jp USING (person_id)
     , json_to_record(jp.block) AS bl(size text, love text);

Or, if you'd rather spell it out:

...
CROSS  JOIN LATERAL json_to_record(jp.block) AS bl(size text, love text);

Answer 2

TL;DR; Don't mix comma-joins with explicit joins. In fact, don't use comma-joins at all.

You have a comma-join in the second query. Comma-joins are interpreted as CROSS JOIN so you seem to have expected the query to be interpreted as follows:

FROM
  json_to_parse
CROSS JOIN
  json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
INNER JOIN
  persons
ON
  persons.person_id = json_to_parse.person_id

which indeed works, as the LATERAL is optional.

But because comma-joins have a lower precedence than explicit join syntax, what actually* happens is this:

FROM
  json_to_parse
CROSS JOIN (
    json_to_record(json_to_parse.block) AS my_json(size TEXT, love TEXT)
    INNER JOIN
      persons
    ON
      persons.person_id = json_to_parse.person_id
)

_{* I know Postgres doesn't support this syntax, but other DBMSs do, and you can also use a subquery}

Since you now have a kind of subquery, LATERAL is not optional. Now it should be obvious why it doesn't work: the implicit parenthesis are pushing the INNER JOIN persons... ON... into a separate scope.

From the docs:

A JOIN clause combines two FROM items, which for convenience we will refer to as “tables”, though in reality they can be any type of FROM item. Use parentheses if necessary to determine the order of nesting. In the absence of parentheses, JOINs nest left-to-right. In any case JOIN binds more tightly than the commas separating FROM-list items.

---

Comma-joins are old-hat. Explicit joins were introduced in SQL-92, and there are very few cases where it's more readable to use comma-joins. Favour explicit join syntax always.