sqlite: `column "id" does not exist` for left-joined query

[diamondburned]

Version

1.17.2 (also happens with 1.18.0)

What happened?

With the query posted below, sqlc will return the following error:

sqlite/queries.sql:3:2: column "id" does not exist

Relevant log output

No response

Database schema

CREATE TABLE IF NOT EXISTS users (
	id TEXT PRIMARY KEY, -- github user ID
	username TEXT NOT NULL, -- login
	email TEXT NOT NULL,
	real_name TEXT NOT NULL,
	pronouns TEXT NOT NULL,
	avatar_url TEXT NOT NULL,
	joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	is_owner BOOLEAN NOT NULL DEFAULT FALSE,
	preferences BLOB NOT NULL DEFAULT '{}'
);

CREATE TABLE IF NOT EXISTS tiers (
	id TEXT PRIMARY KEY, -- github tier ID
	name TEXT NOT NULL,
	price INTEGER NOT NULL, -- cents
	description TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS user_tiers (
	user_id TEXT UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
	tier_id TEXT REFERENCES tiers(id) ON DELETE SET NULL,
	price INTEGER NOT NULL, -- cents
	is_one_time BOOLEAN NOT NULL DEFAULT FALSE,
	is_custom_amount BOOLEAN NOT NULL DEFAULT FALSE,
	started_at TIMESTAMP NOT NULL,
	renewed_at TIMESTAMP NOT NULL
);

SQL queries

-- name: User :one
SELECT
	users.id, users.username, users.email, users.real_name, users.pronouns, users.avatar_url, users.joined_at,
	user_tiers.started_at AS tier_started_at,
	user_tiers.renewed_at AS tier_renewed_at,
	tiers.id AS tier_id,
	tiers.name AS tier_name,
	tiers.price AS tier_price,
	tiers.description AS tier_description
FROM users
LEFT JOIN user_tiers ON users.id = user_tiers.user_id
LEFT JOIN tiers ON user_tiers.tier_id = tiers.id
WHERE users.id = ?;

Configuration

{
  "version": 2,
  "sql": [
    {
      "engine": "sqlite",
      "schema": "sqlite/schema.sql",
      "queries": "sqlite/queries.sql",
      "gen": {
        "go": {
          "package": "sqlite",
          "out": "sqlite",
          "output_files_suffix": ".gen",
          "output_db_file_name": "db.gen.go",
          "output_models_file_name": "models.gen.go"
        }
      }
    }
  ]
}

Playground URL

https://play.sqlc.dev/p/96a33a86cc6f0f8bf7ba2260d9341aa8ea88d6ad36747d5da92ea56e411d6efe

What operating system are you using?

Linux

What database engines are you using?

SQLite

What type of code are you generating?

Go

[diamondburned]

If I alias the users table, then everything seems to work:

-- name: User :one
SELECT
	u.id, u.username, u.email, u.real_name, u.pronouns, u.avatar_url, u.joined_at,
	user_tiers.started_at AS tier_started_at,
	user_tiers.renewed_at AS tier_renewed_at,
	tiers.id AS tier_id,
	tiers.name AS tier_name,
	tiers.price AS tier_price,
	tiers.description AS tier_description
FROM users AS u
LEFT JOIN user_tiers ON users.id = user_tiers.user_id
LEFT JOIN tiers ON user_tiers.tier_id = tiers.id
WHERE users.id = ?;

Doing FROM users users also works with the original query.

[skurhse]

here's a distilled example:

-- name: User :one
SELECT users.id
FROM users
LEFT JOIN tiers ON tiers.id == users.id
WHERE users.id = ?;

It appears that the join statement causes the column search to fail in resolveCatalogRefs. I suspect the join statement causes the sqlite parser to appends a near-duplicate to the tables list, overwriting the correct typeMap entry.

[orisano]

This is a bug in SQLiteParser.g4

The cause of this issue is that LEFT in LEFT JOIN is consumed as table_alias.