Version

1.30.0

What happened?

Hello, the problem i stumbled on is the follwoing.

If i want to use sqlc for queries that are Joining Subqueries with left joins and the output is not generated as nullable.

If i now use this query

-- name: TEST :many
SELECT * FROM a AS table_a
LEFT JOIN (SELECT * FROM b WHERE b.name is not null) si ON si.a_id = table_a.id;

The output is this:

type TESTRow struct {
	ID     uuid.UUID
	Name   pgtype.Text
	ID_2   uuid.UUID
	AID    uuid.UUID
	Name_2 pgtype.Text
}

As you can see. The Properties (IDs) from table b are uuid.UUID and not pointers or other nullable values. This is not working and will produce errors during scanning.

If the LEFT JOIN is done without a subquery it works as expected:

The following query:

-- name: TEST2 :many
SELECT * FROM a AS table_a
LEFT JOIN b AS table_b  ON table_b.a_id = table_a.id
WHERE table_b.name is not null;

Is generting this output:

type TEST2Row struct {
	ID     uuid.UUID
	Name   pgtype.Text
	ID_2   *uuid.UUID
	AID    *uuid.UUID
	Name_2 pgtype.Text
}

I assume this Problem can also be reproduced with right joins.

Relevant log output

Database schema

Schema:

CREATE TABLE a (
    id uuid PRIMARY KEY,
    name TEXT
);

CREATE TABLE b (
    id uuid PRIMARY KEY,
    a_id uuid NOT NULL REFERENCES a (id),
    name TEXT
);

SQL queries

Configuration

version: "2"
sql:
  - schema: "../../../../migrations"
    queries: "."
    engine: "postgresql"
    gen:
      go:
        package: internal
        out: "../"
        sql_package: "pgx/v5"
        overrides:
          - db_type: "uuid"
            nullable: false
            go_type:
              import: "github.com/google/uuid"
              type: "UUID"
          - db_type: "uuid"
            nullable: true
            go_type:
              import: "github.com/google/uuid"
              type: "UUID"
              pointer: true

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go