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.

Sign up for GitHub

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

Jump to bottom

Resolving reference to CTE column fails #3219

Closed
simonklee opened this issue Feb 24, 2024 · 4 comments · Fixed by simonklee/sqlc#1 · May be fixed by #3220
Closed

Resolving reference to CTE column fails #3219

simonklee opened this issue Feb 24, 2024 · 4 comments · Fixed by simonklee/sqlc#1 · May be fixed by #3220
Labels
bug Something isn't working triage New issues that hasn't been reviewed

Comments

@simonklee
Copy link
Contributor

Version

1.25.0

What happened?

Resolving references to multiple CTE's with arguments fails.

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 bug Something isn't working triage New issues that hasn't been reviewed labels Feb 24, 2024
simonklee added a commit to simonklee/sqlc that referenced this issue Feb 25, 2024
@simonklee
fix(resolve): fix resolving reference to CTEs
17133c4 
Fixed resolving refs to CTEs by adding CTEs to the aliasMap and indexing
its columns when resolving catalog references.

Fix sqlc-dev#3219
simonklee added a commit to simonklee/sqlc that referenced this issue Feb 25, 2024
@simonklee
fix(resolve): fix resolving reference to CTEs
e477f77 
Fixed resolving refs to CTEs by adding CTEs to the aliasMap and indexing
its columns when resolving catalog references.

Fix sqlc-dev#3219
simonklee added a commit to simonklee/sqlc that referenced this issue Feb 25, 2024
@simonklee
fix(resolve): fix resolving reference to CTEs
87aa1a9 
Fixed resolving refs to CTEs by adding CTEs to the aliasMap and indexing
its columns when resolving catalog references.

Fix sqlc-dev#3219
@simonklee simonklee mentioned this issue Feb 25, 2024
Open
@mbesida
Copy link

mbesida commented Mar 17, 2024

Does this also relate to following problem? I'm trying to do conditional insert

WITH 
    data (id, foo, bar) AS ( VALUES ($1, $2, $3) ), 
    insert_data AS (
        SELECT 
          d.id, 
          u.id as userId, 
          d.bar
        FROM data d JOIN users u ON d.foo = u.someField
    )
INSERT INTO myTable(id, userId, bar)
SELECT * FROM insert_data
RETURNING *;

sqlc generate gives error: column "id" does not exist pointing to line d.id in insert_data's select

@simonklee
Copy link
Contributor Author

I've been using a patched version of sqlc with my PR applied for a month and I haven't experienced any issues with it.

@simonklee
Copy link
Contributor Author

Does this also relate to following problem? I'm trying to do conditional insert

I think this is the same issue. Should be fairly easy to add the use-case as a regression to the test suite.

@simonklee
Copy link
Contributor Author

This should be re-opened given: 3f129e7

@simonklee simonklee mentioned this issue Mar 28, 2024
Merged
Jille pushed a commit to Jille/sqlc that referenced this issue Apr 3, 2024
@simonklee @Jille
fix(resolve): fix resolving reference to CTEs
65aa1dd 
Fixed resolving refs to CTEs by adding CTEs to the aliasMap and indexing
its columns when resolving catalog references.

Fix sqlc-dev#3219
kyleconroy pushed a commit that referenced this issue Apr 4, 2024
@Jille @simonklee
feat(compiler): Support subqueries in the FROM clause (second coming) (…
3a3e387 
…#3310)

* fix(resolve): fix resolving reference to CTEs

Fixed resolving refs to CTEs by adding CTEs to the aliasMap and indexing
its columns when resolving catalog references.

Fix #3219

* feat(compiler): Support subqueries in the FROM clause

issue #2989, #2400 and probably others

* chore(endtoend): Bump version in cte_resolve_ref to 1.26.0 to appease CI

---------

Co-authored-by: Simon Klee <hello@simonklee.dk>
Jille pushed a commit to Jille/sqlc that referenced this issue Apr 4, 2024
@simonklee @Jille
fix(resolve): fix resolving reference to CTEs
6e3b65c 
Fixed resolving refs to CTEs by adding CTEs to the aliasMap and indexing
its columns when resolving catalog references.

Fix sqlc-dev#3219
simonklee added a commit to simonklee/sqlc that referenced this issue Apr 5, 2024
@simonklee
fix(resolve): fix resolving reference to CTEs
1898589 
Fixed resolving refs to CTEs by adding CTEs to the aliasMap and indexing
its columns when resolving catalog references.

Fix sqlc-dev#3219
@simonklee simonklee mentioned this issue Apr 9, 2024
Merged
simonklee added a commit to simonklee/sqlc that referenced this issue Apr 9, 2024
@simonklee @Jille
fix(resolve): fix resolving reference to CTEs (#1)
403080b 
* fix(resolve): fix resolving reference to CTEs

Fixed resolving refs to CTEs by adding CTEs to the aliasMap and indexing
its columns when resolving catalog references.

Fix sqlc-dev#3219

Co-authored-by: Jille Timmermans <jille@quis.cx>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
bug Something isn't working triage New issues that hasn't been reviewed
Projects
None yet
Milestone
No milestone
2 participants
@simonklee @mbesida