Unable to use set returning functions in from clause with ordinality

[Lay523]

Version

1.21.0

What happened?

column does not exist

Relevant log output

column "item_object" does not exist

Database schema

No response

SQL queries

SELECT code,arr.item_object->> 'code' as resource_code
FROM sys_actions,
 jsonb_array_elements(resources) with ordinality arr(item_object, resource)
 WHERE item_object->>'resource' = sqlc.arg('resource')
 LIMIT 1;

Configuration

sql:
 - schema: "./sql/schema/"
 queries: "./sql/query/"
 engine: "postgresql"
 gen:
 go:
 package: "queries"
 out: "internal/data/queries"
 sql_package: "pgx/v5"
 emit_json_tags: true
 emit_empty_slices: false
 emit_result_struct_pointers: true
 emit_exported_queries: true
 emit_methods_with_db_argument: false
 emit_pointers_for_null_types: true

Playground URL

https://play.sqlc.dev/p/4b891b0542344db335f87e02995f28a3d2d98a0b6821747938ceecf73fc38a46

What operating system are you using?

No response

What database engines are you using?

No response

What type of code are you generating?

No response

[Lay523]

SELECT sa.code as menu_code,record.code as resource_code
FROM sys_actions as sa,jsonb_to_recordset(sa.resources) AS record(code VARCHAR,resource VARCHAR)
WHERE record.resource = $1 LIMIT 1;

https://play.sqlc.dev/p/029d972cfbb5084f1da6c88c9c30f963cb0e1b24c4c6eaf8b76f8e909fef69d0

[arturo-villalpando]

I found the same error with the next query, trying to make queries in jsonb fields:

SELECT id, y.x->>'lang' AS lang, y.x->>'name' AS gender
FROM genders g, 
LATERAL (SELECT jsonb_array_elements(g.gender) AS x) AS y
WHERE g.id = $1 AND y.x->>'lang' = $2 LIMIT 1;

Table alias "y" does not exist, hope to help...

[kyleconroy]

Similar to #1322

[kyleconroy]

This is fixed in v1.23.0 by enabling the database-backed query analyzer. We added a test case for this issue so it won’t break in the future.

You can play around with the working example on the playground