"Column does not exist" when selecting a subquery column #2989
Description
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 existDatabase 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-preparePlayground 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
Reactions are currently unavailable