"Column does not exist" when selecting a subquery column

[taleeus]

Version

1.23.0

What happened?

The column(s) in the SELECT statement is present in the subquery from which I'm selecting, but sqlc tells me otherwhise.

PS: I had to add table names to the columns in the subquery to make other sqlc errors about ambiguity disappear, but the query works fine in MySQL even without them.

Relevant log output

sqlc generate -f platform/db/config/sqlc.yaml
# package dbout
queries/device-associations.sql:3:2: column "serialnumber" does not exist

Database schema

-- Foreign keys omitted

CREATE TABLE `DeviceAssociation` (
  `serialNumber` varchar(191) NOT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `currentSerialNumber` varchar(191) DEFAULT NULL,
  `previousSerialNumber` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`serialNumber`,`createdAt`),
  KEY `DeviceAssociation_previousSerialNumber_idx` (`previousSerialNumber`),
  KEY `DeviceAssociation_currentSerialNumber_idx` (`currentSerialNumber`),
  KEY `DeviceAssociation_createdAt_idx` (`createdAt` DESC)
);

SQL queries

-- name: DeviceAssociations :many
SELECT DISTINCT
	A.serialNumber,
	A.currentSerialNumber
FROM (
		SELECT
			DeviceAssociation.serialNumber AS serialNumber,
			DeviceAssociation.currentSerialNumber AS currentSerialNumber,
			MAX(DeviceAssociation.createdAt) AS createdAt
		FROM
			DeviceAssociation
		WHERE
			DeviceAssociation.serialNumber = sqlc.arg(serialNumber)
			AND DeviceAssociation.currentSerialNumber IS NOT NULL
		GROUP BY
			serialNumber,
			currentSerialNumber
	) AS A
LEFT JOIN (
		SELECT
			DeviceAssociation.serialNumber AS serialNumber,
			DeviceAssociation.previousSerialNumber AS previousSerialNumber,
			MAX(DeviceAssociation.createdAt) AS createdAt
		FROM
			DeviceAssociation
		WHERE
			DeviceAssociation.serialNumber = sqlc.arg(serialNumber)
			AND DeviceAssociation.previousSerialNumber IS NOT NULL
		GROUP BY
			serialNumber,
			previousSerialNumber
	) AS D ON
		A.serialNumber = D.serialNumber AND
		A.currentSerialNumber = D.previousSerialNumber
WHERE A.createdAt > D.createdAt;

Configuration

version: "2"
sql:
  - engine: "mysql"
    queries: "queries"
    schema: "schema.sql"
    gen:
      go:
        package: "dbout"
        out: "../../../app/dbout"
        emit_empty_slices: true
    database:
      uri: "${DATABASE_URL}"
    rules:
      - sqlc/db-prepare

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

MySQL

What type of code are you generating?

Go

[Jille]

Simplified test case: https://play.sqlc.dev/p/cefb6385989c2516f3d658e88e9f236f68973bcca0d63c60c4aa4d32a9221b46