LEFT JOIN (SELECT ...) can cause incorrect type being generated.

[ignassew]

Version

1.27.0

What happened?

Related: #983

When doing a LEFT JOIN (SELECT ...) sqlc doesn't detect that the columns can be nullable.

Example (I've made this PoC available in the playground):

-- name: BadQueryRow :many
SELECT
    ad.name AS app_directory_name, -- ad.name is nullable
    ad.logo AS app_directory_logo,
    ad.uuid AS app_directory_uuid
FROM applications AS a
LEFT JOIN ( -- instead of a normal left join
    SELECT name, logo, uuid
    FROM application_directory.applications
    WHERE archived_at IS NULL
) ad ON ad.uuid = a.app_directory_id
GROUP BY
    ad.name,
    ad.logo,
    ad.uuid

This will return a Row with strings:

type BadQueryRow struct {
	AppDirectoryName string -- incorrect
	AppDirectoryLogo string
	AppDirectoryUuid uuid.UUID
}

If we use a basic LEFT JOIN:

-- name: GoodQuery :many
SELECT
	ad.name AS app_directory_name,
    ad.logo AS app_directory_logo,
    ad.uuid AS app_directory_uuid
FROM applications AS a
LEFT JOIN application_directory.applications ad ON ad.uuid = a.app_directory_id
GROUP BY
    ad.name,
    ad.logo,
    ad.uuid;

We will get a correct struct:

type GoodQueryRow struct {
	AppDirectoryName sql.NullString
	AppDirectoryLogo sql.NullString
	AppDirectoryUuid uuid.NullUUID
}

Expected behavior is for BadQueryRow to be identical to GoodQueryRow.

Relevant log output

No response

Database schema

No response

SQL queries

No response

Configuration

No response

Playground URL

https://play.sqlc.dev/p/0f833be306744cb1854171be2660d45f96905012a1f0e3db421b56b4c761f333

What operating system are you using?

Linux

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[ahmed-com]

I get that same behavior even in the good query when I'm joining a table with itself