Bug When Using Table Aliases: table alias "all_users" does not exist

[AvicennaJr]

Version

1.25.0

What happened?

Sqlc encounters issues when attempting to recognize table aliases specified within the WHERE clause, particularly when filtering selections using the syntax alias_table.field = 'something'.

I've provided a simple example to replicate the issue

Relevant log output

sqlc generate failed.

# package 
query.sql:8:39: table alias "all_users" does not exist

Database schema

-- Create users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create posts table
CREATE TABLE admins (
    id SERIAL PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SQL queries

-- name: GetAllUsers :many
WITH all_users AS (
    SELECT id, email FROM users
    UNION
    SELECT id, email FROM admins
)
SELECT id, email FROM all_users where all_users.email=$1;

Configuration

version: "2"
sql:
- schema: "./db/migrations/"
  queries: "./pkg/postgres/queries/"
  engine: "postgresql"
  gen:
    go: 
      package: "postgres"
      out: "./pkg/postgres/sqlc"
      emit_json_tags: true
      emit_prepared_queries: false
      emit_interface: true
      emit_exact_table_names: false
      emit_empty_slices: true

Playground URL

https://play.sqlc.dev/p/388ae1f5e3a9ab6c0185bdfce9ead7acbbb6ece3d6a52d828cd40184b109caf3

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[matteobassan]

Same thing here: when i set my where condition to a constant it works, but when the condition is dynamic, i get error:

WHERE models.make_id = 47; this works
WHERE models.make_id = @in_make_id; this on does not
WHERE models.make_id = (sqlc.arg(in_make_id)); this one neither

[Jille]

I expect #3220 to fix this

[Dmdv]

I have the same, table aliases are not recognaized

[Dmdv]

Hi everyone
This error persist since 2022.
Can someone ping the pull request #3220
How one can possibly work without CTE or joins?
Is SQLC only for primitive cases?

Please, pay attention to issues:
#2400
#3169
#1385

[frbrno]

Same thing here: when i set my where condition to a constant it works, but when the condition is dynamic, i get error:

WHERE models.make_id = 47; this works WHERE models.make_id = @in_make_id; this on does not WHERE models.make_id = (sqlc.arg(in_make_id)); this one neither

same issue here with sqlite
this seems to work for me:
WHERE models.make_id = cast(sqlc.arg(in_make_id) as integer);

[dkrieger]

@kyleconroy a vanilla use case in probably the most popular non-embedded database, postgres (and seemingly also the most popular embedded database, sqlite), is broken, with a bad error message to boot. Can we get a TTR for this bug, guidance on acceptable PRs, or anything at all?

[rob2244]

+1 having this same issue, super annoying makes where clauses based on CTE's basically unusable

[brojonat]

same issue here with sqlite this seems to work for me: WHERE models.make_id = cast(sqlc.arg(in_make_id) as integer);

Yeah, casting seems to remedy this, for whatever reason. WHERE models.make_id = @in_make_id::INTEGER; works for me with postgres.