Column reference is ambiguous

[vunhatchuong]

Version

1.25.0

What happened?

When I generate the code, it returns an "ambiguous column" error, I tested this in MySQL directly and it works. The culprit is (SELECT MAX(id) FROM snapshot_log)

Relevant log output

# package db
db/queries/query.sql:48:10: column reference "person_id" is ambiguous

Database schema

CREATE TABLE snapshot_log (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    person_id VARCHAR(255) NOT NULL;
);

SQL queries

-- name: SearchSnapshotLogPrev :many
SELECT * FROM (
    SELECT * FROM snapshot_log
    WHERE 1=1
    AND (person_id = sqlc.narg('person_id') OR sqlc.narg('person_id') IS NULL)
	GROUP BY id
    HAVING
    CASE WHEN COALESCE(sqlc.narg('last_value'), 0) = (SELECT MAX(id) FROM snapshot_log) THEN id > 0
         ELSE id > sqlc.narg('last_value') END
    ORDER BY id ASC
) AS subquery
ORDER BY id DESC
LIMIT ?;

Configuration

version: '2'
sql:
  - engine: mysql
    schema: db/migrations
    queries: db/queries
    gen:
      go:
        package: db
        out: db
        emit_db_tags: true
        overrides:
          - db_type: "datetime"
            go_type:
              import: face-recog-log/dto/mqtt-response/customtypes
              type: CreateTime

Playground URL

https://play.sqlc.dev/p/2be88380a5e8f7db34f5c3cb5c1aec7b3f551bf0e543a3c1bfbbce7a0f4e6135

What operating system are you using?

Linux

What database engines are you using?

MySQL

What type of code are you generating?

Go

[EmpathyZeroed]

try explicitly use snapshot_log.person_id instead of person_id in the query. in my case it helped. also they have enhanced analyzer feature now which is created precisely for problems with static analyze - https://docs.sqlc.dev/en/latest/howto/generate.html#enhanced-analysis-using-your-own-database