Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Unable to use set returning functions in from clause with ordinality #2731

Closed
Lay523 opened this issue Sep 14, 2023 · 4 comments
Closed

Unable to use set returning functions in from clause with ordinality #2731

Lay523 opened this issue Sep 14, 2023 · 4 comments

Comments

@Lay523
Copy link

Lay523 commented Sep 14, 2023

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 Lay523 added bug Something isn't working triage New issues that hasn't been reviewed labels Sep 14, 2023
@kyleconroy kyleconroy changed the title column does not exist Unable to use set returning functions in from clause with ordinality Sep 14, 2023
@kyleconroy kyleconroy added 📚 postgresql 🔧 golang and removed triage New issues that hasn't been reviewed labels Sep 14, 2023
@Lay523
Copy link
Author

Lay523 commented Sep 14, 2023

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
Copy link

arturo-villalpando commented Sep 19, 2023

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
Copy link
Collaborator

Similar to #1322

kyleconroy added a commit that referenced this issue Oct 18, 2023
kyleconroy added a commit that referenced this issue Oct 18, 2023
* test: Add case for #2132
* test: Add case for #2152
* test: Mark case for #2152
* test: Add case for #2187
* test: Add case for #2226
* test: Add case for #2364
* test: Add case for #2386
* test: Add case for #2538
* test: Add case for #2644
* test: Add case for #2731
@kyleconroy
Copy link
Collaborator

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants