sql: CTE source not available as function argument in JOIN target

[nvanbenschoten]

NOTE: the underlying issue here may be misdiagnosed. Feel free to rename.

When passing the Prefer: return=headers-only or Prefer: return=representation HTTP header on a POST request to request a newly inserted row's primary key, PostgREST issues the following query:

  WITH pgrst_source AS (
                             WITH pgrst_payload AS (SELECT $1::JSONB AS json_data),
                                  pgrst_body AS (SELECT CASE WHEN json_typeof(json_data) = 'array' THEN json_data ELSE json_build_array(json_data) END AS val FROM pgrst_payload)
                           INSERT
                             INTO public.promo_codes (code)
                           SELECT code
                             FROM json_populate_recordset(NULL::public.promo_codes, (SELECT val FROM pgrst_body)) AS _
                        RETURNING public.promo_codes.*
                    )
SELECT '' AS total_result_set,
       pg_catalog.count(_postgrest_t) AS page_total,
       CASE
       WHEN pg_catalog.count(_postgrest_t) = 1
       THEN COALESCE(
        (
              WITH data AS (SELECT row_to_json(_) AS row FROM pgrst_source AS _ LIMIT 1)
            SELECT array_agg(json_data.key || '=' || COALESCE('eq.' || json_data.value, 'is.null'))
              FROM data CROSS JOIN json_each_text(data.row) AS json_data
             WHERE json_data.key IN ('code',)
        ),
        ARRAY[]::STRING[]
       )
       ELSE ARRAY[]::STRING[]
       END AS header,
       '' AS body,
       NULLIF(current_setting('response.headers', true), '') AS response_headers,
       NULLIF(current_setting('response.status', true), '') AS response_status
  FROM (SELECT * FROM pgrst_source) AS _postgrest_t;

Currently, this returns the following error:

ERROR: no data source matches prefix: data in this context
SQLSTATE: 42P01

I've simplified this down to the following incompatibility with Postgres:

CockroachDB:

root@127.0.0.1:26257/movr> CREATE TABLE t (i INT PRIMARY KEY);
CREATE TABLE

root@127.0.0.1:26257/movr> WITH data AS (SELECT row_to_json(t) AS row FROM t)
SELECT count(*)
FROM data CROSS JOIN json_each_text(data.row);
ERROR: no data source matches prefix: data in this context
SQLSTATE: 42P01

Postgres:

nathan=# CREATE TABLE t (i INT PRIMARY KEY);
CREATE TABLE

nathan=# WITH data AS (SELECT row_to_json(t) AS row FROM t)
SELECT count(*)
FROM data CROSS JOIN json_each_text(data.row);
 count
-------
     0
(1 row)

It appears that Cockroach does not correctly propagate the CTE source into the JOIN target's scope, so it cannot find data in json_each_text(data.row).

Epic CRDB-10300

[rafiss]

Looks like #59154 but this issue has a nice write up, so I'll close that one.

[RaduBerinde]

How can the right hand side of a non-lateral join refer to the left-hand side?

Using LATERAL works:

WITH data AS (SELECT row_to_json(t) AS row FROM t)
SELECT count(*)
FROM data CROSS JOIN LATERAL json_each_text(data.row);
  count
---------
      0
(1 row)

My guess is that it just happens to work in postgres for whatever reason. I didn't find anything in the pg documentation that would suggest it should.

[RaduBerinde]

Maybe we should contribute a change to add the LATERAL keyword to the query? It shouldn't hurt anything when run against postgres.

[RaduBerinde]

Ok, I found this in PG docs:

Function Calls in FROM
PostgreSQL allows a function call to be written directly as a member of the FROM list. In the SQL standard it would be necessary to wrap such a function call in a sub-SELECT; that is, the syntax FROM func(...) alias is approximately equivalent to FROM LATERAL (SELECT func(...)) alias. Note that LATERAL is considered to be implicit; this is because the standard requires LATERAL semantics for an UNNEST() item in FROM. PostgreSQL treats UNNEST() the same as other set-returning functions.