sqlite: fails with NULL AS select query #4048
Description
Version
1.29.0
What happened?
sqlc generate failed when given a rather complex but valid sqlite query involving NULL AS columns and CASE statements to query from two similar tables and get a unified output. This query has been used in production for several years but I was interested in converting our sql usage to sqlc and found it choked on this one query.
Workaround
Splitting into two queries works, but I would prefer to keep the same structure as before.
-- name: DebPackages :many
-- Gets installed DEB packages on Linux systems
SELECT arch, name, size, version, mount_namespace_id, pid_with_namespace,
revision, section, maintainer FROM deb_packages;
-- name: RpmPackages :many
-- Gets installed RPM packages on Linux systems
SELECT arch, name, size, version, install_time, mount_namespace_id,
pid_with_namespace, package_group, vendor FROM rpm_packages;Relevant log output
line 9:43 no viable alternative at input '(\n\t\tSELECT arch, name, size, version, NULL AS install_time, mount_namespace_id,\n\t\t\tpid_with_namespace, NULL AS release, revision, NULL AS package_group, section, maintainer,\n\t\t\tNULL AS vendor, "deb" AS package_format FROM'
line 8:31 missing {IDENTIFIER, STRING_LITERAL} at 'release'
line 8:38 extraneous input ',' expecting {'(', ABORT_, ACTION_, ADD_, AFTER_, ALL_, ALTER_, ANALYZE_, AND_, AS_, ASC_, ATTACH_, AUTOINCREMENT_, BEFORE_, BEGIN_, BETWEEN_, BY_, CASCADE_, CASE_, CAST_, CHECK_, COLLATE_, COLUMN_, COMMIT_, CONFLICT_, CONSTRAINT_, CREATE_, CROSS_, CURRENT_DATE_, CURRENT_TIME_, CURRENT_TIMESTAMP_, DATABASE_, DEFAULT_, DEFERRABLE_, DEFERRED_, DELETE_, DESC_, DETACH_, DISTINCT_, DROP_, EACH_, ELSE_, END_, ESCAPE_, EXCEPT_, EXCLUSIVE_, EXISTS_, EXPLAIN_, FAIL_, FOR_, FOREIGN_, FROM_, FULL_, GLOB_, GROUP_, HAVING_, IF_, IGNORE_, IMMEDIATE_, IN_, INDEX_, INDEXED_, INITIALLY_, INNER_, INSERT_, INSTEAD_, INTERSECT_, INTO_, IS_, ISNULL_, JOIN_, KEY_, LEFT_, LIKE_, LIMIT_, MATCH_, NATURAL_, NO_, NOT_, NOTNULL_, NULL_, OF_, OFFSET_, ON_, OR_, ORDER_, OUTER_, PLAN_, PRAGMA_, PRIMARY_, QUERY_, RAISE_, RECURSIVE_, REFERENCES_, REGEXP_, REINDEX_, RELEASE_, RENAME_, REPLACE_, RESTRICT_, RETURNING_, RIGHT_, ROLLBACK_, ROW_, ROWS_, SAVEPOINT_, SELECT_, SET_, STRICT_, TABLE_, TEMP_, TEMPORARY_, THEN_, TO_, TRANSACTION_, TRIGGER_, UNION_, UNIQUE_, UPDATE_, USING_, VACUUM_, VALUES_, VIEW_, VIRTUAL_, WHEN_, WHERE_, WITH_, WITHOUT_, FIRST_VALUE_, OVER_, PARTITION_, RANGE_, PRECEDING_, UNBOUNDED_, CURRENT_, FOLLOWING_, CUME_DIST_, DENSE_RANK_, LAG_, LAST_VALUE_, LEAD_, NTH_VALUE_, NTILE_, PERCENT_RANK_, RANK_, ROW_NUMBER_, GENERATED_, ALWAYS_, STORED_, TRUE_, FALSE_, WINDOW_, NULLS_, FIRST_, LAST_, FILTER_, GROUPS_, EXCLUDE_, IDENTIFIER, STRING_LITERAL}
line 8:48 extraneous input ',' expecting {<EOF>, ';', ALTER_, ANALYZE_, ATTACH_, BEGIN_, COMMIT_, CREATE_, DEFAULT_, DELETE_, DETACH_, DROP_, END_, EXPLAIN_, INSERT_, PRAGMA_, REINDEX_, RELEASE_, REPLACE_, ROLLBACK_, SAVEPOINT_, SELECT_, UPDATE_, VACUUM_, VALUES_, WITH_}
line 13:77 no viable alternative at input 'rpm_packages'
# package
query.sql:1:1: no viable alternative at input 'rpm_packages'Database schema
CREATE TABLE deb_packages(
name TEXT,
version TEXT,
source TEXT,
size BIGINT,
arch TEXT,
revision TEXT,
status TEXT,
maintainer TEXT,
section TEXT,
priority TEXT,
admindir TEXT,
pid_with_namespace INTEGER HIDDEN,
mount_namespace_id TEXT HIDDEN,
PRIMARY KEY (
name,
version,
source,
size,
arch,
revision,
status,
maintainer,
section,
priority,
admindir,
pid_with_namespace,
mount_namespace_id
)
) WITHOUT ROWID;
CREATE TABLE rpm_packages(
name TEXT,
version TEXT,
release TEXT,
source TEXT,
size BIGINT,
sha1 TEXT,
arch TEXT,
epoch INTEGER,
install_time INTEGER,
vendor TEXT,
package_group TEXT,
pid_with_namespace INTEGER HIDDEN,
mount_namespace_id TEXT HIDDEN,
PRIMARY KEY (
name,
version,
release,
arch,
epoch,
pid_with_namespace
)
) WITHOUT ROWID;SQL queries
-- name: LinuxPackages :many
-- Gets installed Linux packages (DEB and RPM)
SELECT arch, name, size, version, install_time, mount_namespace_id, pid_with_namespace,
CASE WHEN revision IS NULL THEN release ELSE revision END AS release_or_revision,
CASE WHEN section IS NULL THEN package_group ELSE section END AS package_group_or_section,
CASE WHEN vendor IS NULL THEN maintainer ELSE vendor END AS maintainer_or_vendor,
package_format
FROM (
SELECT arch, name, size, version, NULL AS install_time, mount_namespace_id,
pid_with_namespace, NULL AS release, revision, NULL AS package_group, section, maintainer,
NULL AS vendor, "deb" AS package_format FROM deb_packages
UNION ALL
SELECT arch, name, size, version, install_time, mount_namespace_id,
pid_with_namespace, release, NULL AS revision, package_group,
NULL AS section, NULL AS maintainer, vendor, "rpm" AS package_format FROM rpm_packages
);Configuration
{
"version": "2",
"sql": [{
"schema": "schema.sql",
"queries": "query.sql",
"engine": "sqlite",
"gen": {
"go": {
"out": "db"
}
}
}]
}Playground URL
https://play.sqlc.dev/p/36f1c896c37700a7430f507cbe7c43c8e534896b184e2a20fbc70ced8cd8aa2a
What operating system are you using?
Linux
What database engines are you using?
SQLite
What type of code are you generating?
Go