Why SQLite returning first row as `NULL`s?
-- name: RetrieveDistinctReposAndPackages :many
select
distinct
platform as _plat,
organisation as _org,
repo as _repo,
renovate.package_name as _package_name,
renovate.version as _version,
current_version as _current_version,
renovate.package_manager as _package_manager,
dep_types as _dep_types,
package_file_path as _package_file_path,
group_concat(distinct
(case
when external_licenses.license IS NOT NULL then external_licenses.license
when depsdev_licenses.license IS NOT NULL then depsdev_licenses.license
else '' -- this shouldn't trigger due to the WHERE clause below
end)
) as licenses
from
renovate
-- TODO
left join external_licenses
on renovate.package_name = external_licenses.package_name
and (
case
when renovate.current_version is not null then renovate.current_version = external_licenses.version
else renovate.version = external_licenses.version
end
)
and (
renovate.package_manager = external_licenses.package_manager
OR
renovate.datasource = external_licenses.package_manager
)
left join depsdev_licenses
on renovate.package_name = depsdev_licenses.package_name
and (
case
when renovate.current_version is not null then renovate.current_version = depsdev_licenses.version
else renovate.version = depsdev_licenses.version
end
)
where
(
external_licenses.license IS NOT NULL
OR
depsdev_licenses.license IS NOT NULL
)
union
select
distinct
platform as _plat,
organisation as _org,
repo as _repo,
sboms.package_name as _package_name,
sboms.version as _version,
current_version as _current_version,
package_type as _package_manager,
-- as SBOMs don't make this available, default to an empty array
'[]' as _dep_types,
-- as SBOMs don't make this available, default to an empty string
'' as _package_file_path,
group_concat(distinct
(case
when external_licenses.license IS NOT NULL then external_licenses.license
when depsdev_licenses.license IS NOT NULL then depsdev_licenses.license
else '' -- this shouldn't trigger due to the WHERE clause below
end)
) as licenses
from
sboms
left join external_licenses
on sboms.package_name = external_licenses.package_name
and (
case
when sboms.current_version is not null then sboms.current_version = external_licenses.version
else sboms.version = external_licenses.version
end
)
and sboms.package_type = external_licenses.package_manager
left join depsdev_licenses
on sboms.package_name = depsdev_licenses.package_name
and (
case
when sboms.current_version is not null then sboms.current_version = depsdev_licenses.version
else sboms.version = depsdev_licenses.version
end
)
where
(
sboms.version is not null
or
sboms.current_version is not null
)
AND
(
external_licenses.license IS NOT NULL
OR
depsdev_licenses.license IS NOT NULL
)
-- group by _plat, _org, _repo, _package_name, _version, _current_version, _dep_types, _package_file_path
group by _plat, _org, _repo
order by _plat, _org, _repo
;
Because the group by
was missing from the first query