Non-valid subquery generation

[TobiasGleiter]

Version

1.30.0

What happened?

SQLC generates Go code for a non-valid subquery.

Relevant log output

SQL logic error: no such column: locations.project_id (1)

Database schema

CREATE TABLE IF NOT EXISTS organization_roles (
    id INTEGER PRIMARY KEY,
    name TEXT UNIQUE NOT NULL
) STRICT;

CREATE TABLE IF NOT EXISTS organizations (
    id INTEGER PRIMARY KEY,
    public_id TEXT UNIQUE NOT NULL,
    display_name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    phone TEXT,
    contact_email TEXT,
    country_code TEXT DEFAULT 'DE',
    address_line_1 TEXT,
    address_line_2 TEXT,
    postal_code INTEGER,
    city TEXT,
    state_province TEXT,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
) STRICT;

CREATE TABLE IF NOT EXISTS organization_members (
    id INTEGER PRIMARY KEY,
    public_id TEXT UNIQUE NOT NULL,
    account_id INTEGER NOT NULL,
    organization_id INTEGER NOT NULL,
    role_id INTEGER NOT NULL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch()),
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES organization_roles(id) ON DELETE RESTRICT,
    UNIQUE(account_id, organization_id)
) STRICT;

CREATE TABLE IF NOT EXISTS projects (
    id INTEGER PRIMARY KEY,
    public_id TEXT NOT NULL UNIQUE,
    organization_id INTEGER NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    created_by_account_id INTEGER REFERENCES accounts(id) ON DELETE SET NULL,
    name TEXT NOT NULL,
    description TEXT,
    show_organization_info INTEGER NOT NULL DEFAULT 1,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
) STRICT;

CREATE TABLE IF NOT EXISTS locations (
    id INTEGER PRIMARY KEY,
    public_id TEXT UNIQUE NOT NULL,
    project_id INTEGER NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    notes TEXT,
    latitude REAL,
    longitude REAL,
    created_at INTEGER NOT NULL DEFAULT (unixepoch()),
    updated_at INTEGER NOT NULL DEFAULT (unixepoch())
) STRICT;

SQL queries

-- name: GetByPublicID :one
-- Invalid subquery with location.project_id
SELECT *
FROM locations l
WHERE l.public_id = ?
AND EXISTS (
    SELECT 1
    FROM projects p
    JOIN organization_members om ON p.organization_id = om.organization_id
    WHERE 
        p.id = location.project_id
        AND om.account_id = ?
);

-- name: GetByPublicID :one
-- Valid with subquery with l.project_id
SELECT *
FROM locations l
WHERE l.public_id = ?
AND EXISTS (
    SELECT 1
    FROM projects p
    JOIN organization_members om ON p.organization_id = om.organization_id
    WHERE 
        p.id = l.project_id
        AND om.account_id = ?
);

Configuration

- engine: "sqlite"
    queries: "locations.sql"
    schema:
      - "db/migrations/*project*.sql"
      - "db/migrations/*location*.sql"
      - "db/migrations/*organization*.sql"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[Boris-creator]

This issue is actual not only for SQLite.
My case with Postgres:

Database migration:

CREATE TABLE IF NOT EXISTS t1 (
    id      UUID PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS t2 (
    name   VARCHAR(255),
    t1_id  UUID  REFERENCES t1(id) ON DELETE CASCADE
);

Query:

-- name: GetT1 :many
SELECT * FROM t1
WHERE id IN (
    SELECT t1_unknown_column
    FROM t2
    WHERE name = $1
);

Configuration:

version: "2"
sql:
  - engine: "postgresql"
    schema: "../migrations"

Has no sense, but compiles successfully.