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

Resolving references to CTE Columns fails #3585

Open
simonklee opened this issue Sep 6, 2024 · 3 comments
Open

Resolving references to CTE Columns fails #3585

simonklee opened this issue Sep 6, 2024 · 3 comments

Comments

@simonklee
Copy link
Contributor

simonklee commented Sep 6, 2024

Version

1.27.0

What happened?

Resolving references to multiple CTE's with arguments fails.

This is a duplicate of #3219, but 3219 has been closed even thought the issue still persists and the relevant PR is not merged.

PR to fix this issue: #3220

Relevant log output

sqlc generate failed.
# package 
query.sql:5:28: table alias "t1_ids" does not exist

Database schema

CREATE TABLE t1
(
    id SERIAL NOT NULL PRIMARY KEY
);

SQL queries

-- name: CTERef :one
WITH t1_ids AS (
    SELECT id FROM t1
)
SELECT * FROM t1_ids WHERE t1_ids.id = sqlc.arg('id');

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/9048b486f822c300301650867612e42bef8f8d512dfd12d84ad3a4cd210f4ef7

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

@simonklee simonklee added the bug Something isn't working label Sep 6, 2024
@michaelgrijalva-resim
Copy link

michaelgrijalva-resim commented Nov 19, 2024

If anyone else runs into this issue and lands on this page, I found it usually works if you explicitly annotate the appropriate type on the sqlc argument:

-- name: CTERef :one
WITH t1_ids AS (
    SELECT id FROM t1
)
SELECT * FROM t1_ids WHERE t1_ids.id = sqlc.arg('id')::integer;
--                                                   ^^^^^^^^^

This makes sqlc happy for now, until the above fix is merged.

@mem
Copy link

mem commented Dec 20, 2024

I have a similar issue, but the workaround suggested in this case does not work.

https://play.sqlc.dev/p/3df2d3f54eff7ef74e32ffca38be69ddd9a3dc7058cbe7ae9274007e05909236

The SQL in the link is silly, as I have reduced the case as much as possible.

The actual query is trying to retrieve a single record with some conditions and insert a modified version of the same record using input provided by the user.

@ednmechrock
Copy link

Still getting this issue on 24/3/2025 using sqlc v1.28.0
Annotation as above (#3585 (comment)) also still working as a temp fix.

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

4 participants