Left joining same table as FROM statement results in unexpected string type rather than a nullable string type

[bradenrayhorn]

Version

1.25.0

What happened?

Using the schema and query below, I would expect the manager_name column to be nullable because the manager_id field is nullable. However, when sqlc generates types for this query the ManagerName field is not of a nullable type, it is a normal string.

Relevant log output

No response

Database schema

CREATE TABLE employees (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  manager_id BIGSERIAL REFERENCES employees(id)
);

SQL queries

-- name: GetEmployees :many
SELECT employees.id, managers.name as manager_name
FROM employees
LEFT JOIN employees as managers ON managers.id = employees.manager_id;

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "postgresql",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/d731813577e3c62ea17d31608ffef113dfe47303ad5c5c6618824802d74bff0b

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go

[seonixx]

Did you manage to work this out?

[bradenrayhorn]

@seonixx I have not found a solution, other than avoiding sqlc for this query.

[hungtcs]

+1 hope can solve it soon.

@bradenrayhorn I now use COALESCE to solve this error

-- name: GetEmployees :many
SELECT employees.id, COALESCE(managers.name, '')::text as manager_name
FROM employees
LEFT JOIN employees as managers ON managers.id = employees.manager_id;