René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"\"Column does not exist\" when selecting a subquery column","articleBody":"### Version\n\n1.23.0\n\n### What happened?\n\nThe column(s) in the `SELECT` statement is present in the subquery from which I'm selecting, but `sqlc` tells me otherwhise.\r\n\r\nPS: 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.\n\n### Relevant log output\n\n```shell\nsqlc generate -f platform/db/config/sqlc.yaml\r\n# package dbout\r\nqueries/device-associations.sql:3:2: column \"serialnumber\" does not exist\n```\n\n\n### Database schema\n\n```sql\n-- Foreign keys omitted\r\n\r\nCREATE TABLE `DeviceAssociation` (\r\n  `serialNumber` varchar(191) NOT NULL,\r\n  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),\r\n  `currentSerialNumber` varchar(191) DEFAULT NULL,\r\n  `previousSerialNumber` varchar(191) DEFAULT NULL,\r\n  PRIMARY KEY (`serialNumber`,`createdAt`),\r\n  KEY `DeviceAssociation_previousSerialNumber_idx` (`previousSerialNumber`),\r\n  KEY `DeviceAssociation_currentSerialNumber_idx` (`currentSerialNumber`),\r\n  KEY `DeviceAssociation_createdAt_idx` (`createdAt` DESC)\r\n);\n```\n\n\n### SQL queries\n\n```sql\n-- name: DeviceAssociations :many\r\nSELECT DISTINCT\r\n\tA.serialNumber,\r\n\tA.currentSerialNumber\r\nFROM (\r\n\t\tSELECT\r\n\t\t\tDeviceAssociation.serialNumber AS serialNumber,\r\n\t\t\tDeviceAssociation.currentSerialNumber AS currentSerialNumber,\r\n\t\t\tMAX(DeviceAssociation.createdAt) AS createdAt\r\n\t\tFROM\r\n\t\t\tDeviceAssociation\r\n\t\tWHERE\r\n\t\t\tDeviceAssociation.serialNumber = sqlc.arg(serialNumber)\r\n\t\t\tAND DeviceAssociation.currentSerialNumber IS NOT NULL\r\n\t\tGROUP BY\r\n\t\t\tserialNumber,\r\n\t\t\tcurrentSerialNumber\r\n\t) AS A\r\nLEFT JOIN (\r\n\t\tSELECT\r\n\t\t\tDeviceAssociation.serialNumber AS serialNumber,\r\n\t\t\tDeviceAssociation.previousSerialNumber AS previousSerialNumber,\r\n\t\t\tMAX(DeviceAssociation.createdAt) AS createdAt\r\n\t\tFROM\r\n\t\t\tDeviceAssociation\r\n\t\tWHERE\r\n\t\t\tDeviceAssociation.serialNumber = sqlc.arg(serialNumber)\r\n\t\t\tAND DeviceAssociation.previousSerialNumber IS NOT NULL\r\n\t\tGROUP BY\r\n\t\t\tserialNumber,\r\n\t\t\tpreviousSerialNumber\r\n\t) AS D ON\r\n\t\tA.serialNumber = D.serialNumber AND\r\n\t\tA.currentSerialNumber = D.previousSerialNumber\r\nWHERE A.createdAt \u003e D.createdAt;\n```\n\n\n### Configuration\n\n```yaml\nversion: \"2\"\r\nsql:\r\n  - engine: \"mysql\"\r\n    queries: \"queries\"\r\n    schema: \"schema.sql\"\r\n    gen:\r\n      go:\r\n        package: \"dbout\"\r\n        out: \"../../../app/dbout\"\r\n        emit_empty_slices: true\r\n    database:\r\n      uri: \"${DATABASE_URL}\"\r\n    rules:\r\n      - sqlc/db-prepare\n```\n\n\n### Playground URL\n\n_No response_\n\n### What operating system are you using?\n\nmacOS\n\n### What database engines are you using?\n\nMySQL\n\n### What type of code are you generating?\n\nGo","author":{"url":"https://github.com/taleeus","@type":"Person","name":"taleeus"},"datePublished":"2023-11-17T13:41:57.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":1},"url":"https://github.com/2989/sqlc/issues/2989"}