SQLC does not infer nullability for subqueries in view SELECT #4262

Activity

1. lookeey

   added

   bugSomething isn't workingSomething isn't working

   on Jan 14, 2026
2. dosubot

   added

   📚 postgresql

   💻 linux

   🔧 golang

   on Jan 14, 2026
3. ken-talltree-io commented on Oct 24, 2026

   

   ken-talltree-io

   on Oct 24, 2026

   More actions

   This also happens outside views, and with CASE whose ELSE is a typed null (CASE WHEN … THEN s.label ELSE NULL::text END, which is how pg_dump writes every CASE without ELSE). On 1.31.1 and main (3ad8b20), the legacy analyzer marks both non-null. With SQLCEXPERIMENT=coreanalyzer both are nullable, as they should be, but left-joined columns become non-null there instead.

   Minimal repro:

   -- schema.sql
   create table authors (id int primary key, name text not null);
   create table series (id int primary key, name text not null);
   create table books (id int primary key, title text not null, author_id int not null references authors, series_id int);
   create table reviews (id int primary key, book_id int not null);
   create table shelves (id int primary key, label text not null, book_id int);
   create view book_view as
     select b.id, (select r.id from reviews r where r.book_id = b.id) review_id,
       case when b.id > 0 then s.label else null::text end shelf,
       (select count(*) from reviews r where r.book_id = b.id) reviews
     from books b left join shelves s on s.book_id = b.id;

   -- query.sql
   -- name: LeftJoin :many
   select b.title, s.name from books b left join series s on s.id = b.series_id;
   -- name: ThroughView :many
   select id, review_id, shelf, reviews from book_view;
   -- name: Inline :many
   select (select r.id from reviews r where r.book_id = b.id) review_id,
     case when b.id > 0 then b.title else null::text end maybe_title
   from books b;

   Nullability in the codegen request (? = nullable):

   	legacy	coreanalyzer
   LeftJoin.name	text? ✓	text ✗
   ThroughView.review_id (scalar subquery)	int4 ✗	integer? ✓
   ThroughView.shelf (ELSE NULL::text)	text ✗	text? ✓
   ThroughView.reviews (count(*) subquery)	bigint ✓	bigint? (always one row, so could be non-null)
   Inline.review_id	int4 ✗	integer? ✓
   Inline.maybe_title	text ✗	text? ✓

   I'd like to send a fix to internal/compiler/output_columns.go for the legacy path: scalar subqueries and ELSE NULL::type nullable, (SELECT count(*) …) kept non-null. Would you take that, or would you rather fixes go to the core analyzer?