"Column does not exist" when selecting a subquery column #2989

-- 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)
);

-- 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;