Overrides with `nullable: true` aren't used for nullable columns when performing a `LEFT JOIN`

[alecthomas]

trafficstars

Version

1.20.0

What happened?

I have the following example overrides:

        overrides:
          - column: "c.c"
            go_type: "string"
          - column: "c.c"
            nullable: true
            go_type: "sql.NullString"
          - column: "a.a"
            nullable: true
            go_type: "sql.NullString"
          - column: "a.a"
            go_type: "string"

The columns themselves are NOT NULL, but during a LEFT JOIN I expected the null overrides would be used. Instead, regardless of the null state of the column in the select, the first override is always used.

Related, I was surprised to see a nullable: true override being used for a non-null column just because it happened to be the first . It seems like there might be another bug there, or maybe it's the same one?

Relevant log output

N/A

Database schema

CREATE TABLE a (
  id   BIGSERIAL PRIMARY KEY,
  a TEXT NOT NULL
);

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

CREATE TABLE c (
  id BIGSERIAL PRIMARY KEY,
  c TEXT NOT NULL,
  a_id BIGINT NOT NULL REFERENCES a (id)
);

SQL queries

-- name: TestLeftInner :many
SELECT a.a, b.b, c.c
FROM b
LEFT JOIN c ON c.a_id = a.id
INNER JOIN a ON b.a_id = a.id;

-- name: TestInnerLeft :many
SELECT a.a, b.b, c.c
FROM b
INNER JOIN a ON b.a_id = a.id
LEFT JOIN c ON c.a_id = a.id;

Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: "query.sql"
    schema: "query.sql"
    gen:
      go:
        package: "sql"
        out: "."
        emit_interface: true
        query_parameter_limit: 3
        overrides:
          - column: "c.c"
            go_type: "string"
          - column: "c.c"
            nullable: true
            go_type: "sql.NullString"
          - column: "a.a"
            nullable: true
            go_type: "sql.NullString"
          - column: "a.a"
            go_type: "string"

Playground URL

https://play.sqlc.dev/p/208180b8a21232b5fb12b492fdc37500d7147d31081a3d592649b10f3b240eb1

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[andrewmbenton]

Thanks for the detailed test case. It does seem like there may be multiple bugs here.

I think it clearly shouldn't be true that sqlc's choice of override depends on the order of the definitions in your configuration in cases where the override definitions aren't exactly identical. But it seems like perhaps the nullable property only matters in the context of a db_type override. I'd have to dig a bit deeper to determine that, but it might also explain the behavior you're seeing for LEFT JOIN output columns.

That is, maybe in sqlc's view the following two overrides are actually identical and so in all cases where it encounters the column c.c it just applies the first override it finds:

        overrides:
          - column: "c.c"
            go_type: "foo"
          - column: "c.c"
            go_type: "foo"
            nullable: true

[fedorkanin]

I have encountered the same issue. I use custom types for several non-nullable columns, and sometimes I left-join them. It seems for now that when having overrides like in @andrewmbenton's message, the second one is not being taken into account by sqlc.

Have there been any workarounds yet?

[gevorg-tsat]

+1 to @fedorkanin