Skip to content

Resolving references to CTE Columns fails #3585

Open

Description

@simonklee

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

Activity

  1. michaelgrijalva-resim commented on Nov 19, 2024

    @michaelgrijalva-resim

    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.

  2. mem commented on Dec 20, 2024

    @mem

    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.

  3. ednmechrock commented on Mar 24, 2025

    @ednmechrock

    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

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions