Nullability through joins, COALESCE and aggregates

On this page

On ClickHouse:

  • Columns are NOT NULL unless declared Nullable(T), which maps to *T
  • A LEFT JOIN fills an unmatched row with default values ("" and 0) unless join_use_nulls = 1, so in ListSightings Place is a plain string, empty for the gull never seen and printed here as (empty)
  • Seagull assumes the server's default, join_use_nulls = 0, unless the query sets it in SETTINGS or the database's ClickHouse dialect options say join_use_nulls: true
  • With the default fill, count(column) counts the unmatched row; SummariseGulls sets join_use_nulls = 1 so count gives 0 and max gives NULL
  • count maps to uint64 and max(Date) to *time.Time
  • No UNIQUE constraint and no auto-increment, so each row's id is a parameter

A Go string cannot be NULL, so a field that receives a column which might be NULL has to be able to say so. Seagull makes such a field a pointer, where nil means NULL, and leaves every other field a plain value. The work is in knowing which is which. A column's own NOT NULL is where it starts, but a query can take that guarantee away, with an outer join or an aggregate over no rows, or give it back, with a fallback that is never NULL. Seagull follows the SQL to decide each field, so you do not have to remember which columns to check for nil.

This example rings gulls and records where they were seen. One gull has no nickname and another has never been seen, so both kinds of NULL turn up.

The schema

A ClickHouse column is never NULL unless its type says so. nickname is Nullable(String), so it may be NULL, and no other column can be.

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id       UInt64,
    ring     String,
    nickname Nullable(String)
) ENGINE = MergeTree() ORDER BY id;

CREATE TABLE sightings (
    id      UInt64,
    gull_id UInt64,
    seen_on Date,
    place   String
) ENGINE = MergeTree() ORDER BY id;

A nullable column

RingGull inserts a gull. Because nickname may be NULL, its parameter is a *string, and the program passes nil for the gull without one.

internal/store

type RingGullParams struct {
	ID       uint64
	Ring     string
	Nickname *string
}

func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error {
	_, err := queries.writer.ExecContext(ctx, ringGullSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_ring", sglClickHouseShapedValue(params.Ring, sglClickHouseShapes[2])), clickhouse.Named("p_nickname", sglClickHouseShapedValue(params.Nickname, sglClickHouseShapes[2])))
	return err
}

Through a LEFT JOIN

ListSightings lists every gull with each of its sightings. In SQL, a LEFT JOIN keeps a gull that has no sightings and gives it NULL for every sighting column, but ClickHouse does that only when the join_use_nulls setting is 1. With the server's default, 0, it fills an unmatched row with each column's default value instead, so a gull that was never seen gets an empty string for place.

db/queries/gulls.sql

-- sgl.query(ListSightings, many)
SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;

internal/store

type ListSightingsRow struct {
	Ring     string  `json:"ring"`
	Nickname *string `json:"nickname"`
	Place    string  `json:"place"`
}

func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listSightingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListSightingsRow
	for rows.Next() {
		var row ListSightingsRow
		if err := rows.Scan(&row.Ring, &row.Nickname, &row.Place); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Seagull assumes the server's default, so place keeps its own type through the join. It is a String, which is never NULL, so Place is a plain string, and the gull that was never seen gets an empty one, which the program prints as (empty). Nickname is a *string either way, because the column itself is Nullable(String).

SummariseGulls, below, sets join_use_nulls = 1 in its own SETTINGS, and Seagull types that query the same way. If your server or connection sets it for every query, say so with join_use_nulls: true in the database's dialect_options in sgl.yaml, and every outer join is typed with real NULLs.

COALESCE and aggregates

SummariseGulls gives one line per gull: what to call it, how often it was seen, and when it was last seen. Each of those three columns comes out differently.

db/queries/gulls.sql

-- sgl.query(SummariseGulls, many)
SELECT
    ring,
    coalesce(nickname, concat('ring ', ring)) AS known_as,
    count(seen_on) AS times_seen,
    max(seen_on) AS last_seen
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
GROUP BY gulls.id, ring, nickname
ORDER BY gulls.id
SETTINGS join_use_nulls = 1;
  • coalesce falls back to concat('ring ', ring), which is never NULL, so KnownAs is a plain string.
  • With join_use_nulls = 1, the unmatched row really is NULL. count(seen_on) skips it, so the gull that was never seen counts 0, and count is never NULL itself, so TimesSeen is a plain uint64.
  • max over no dates is NULL, so LastSeen is a pointer. It keeps the column's type, and seen_on is a Date, so it is a *time.Time.

internal/store

type SummariseGullsRow struct {
	Ring      string     `json:"ring"`
	KnownAs   string     `json:"known_as"`
	TimesSeen uint64     `json:"times_seen"`
	LastSeen  *time.Time `json:"last_seen"`
}

func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, summariseGullsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SummariseGullsRow
	for rows.Next() {
		var row SummariseGullsRow
		if err := rows.Scan(&row.Ring, &row.KnownAs, &row.TimesSeen, &row.LastSeen); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

Using it

The program reads every pointer through a small orNull helper, which prints NULL for nil. The types say which values can be missing: row.Nickname has to be dereferenced, and may be nil, while row.Place is a string to print as it is. A second helper, orEmpty, prints (empty) for an empty string, so the default value ClickHouse filled in can be seen.

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"time"

	"seagull.sh/seagull/examples/clickhouse/004_nullability/internal/store"
)

// run rings three gulls, records where two of them were seen, and lists them twice.
// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
// a pointer:
//
//   - gulls.nickname is Nullable(String), so Nickname is *string, as a parameter and a
//     column; a ClickHouse column is never NULL unless its type is Nullable(T);
//   - ListSightings leaves join_use_nulls at the server's default, 0, so its LEFT JOIN
//     fills an unmatched row with each column's default value instead of NULL. Place is a
//     String column, so it stays a string, and Gwen, who was never seen, gets an empty
//     one;
//   - SummariseGulls sets join_use_nulls = 1, so the unmatched row is NULL: count skips
//     it and gives 0, and max over no dates gives NULL, so TimesSeen is uint64 and
//     LastSeen is *time.Time. Without the setting, count would see Gwen's default row and
//     give 1;
//   - coalesce with a fallback that is never NULL is never NULL, so KnownAs is string.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives both listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.RingGullParams{
		{ID: 1, Ring: "GB1001", Nickname: new("Steven")},
		{ID: 2, Ring: "GB1002", Nickname: nil},
		{ID: 3, Ring: "GB1003", Nickname: new("Gwen")},
	}
	for _, gull := range gulls {
		if err := queries.RingGull(ctx, gull); err != nil {
			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
		}
	}
	sightings := []store.RecordSightingParams{
		{ID: 1, GullID: 1, SeenOn: day(1), Place: "the pier"},
		{ID: 2, GullID: 1, SeenOn: day(3), Place: "the chip shop"},
		{ID: 3, GullID: 2, SeenOn: day(2), Place: "the harbour wall"},
	}
	for _, sighting := range sightings {
		if err := queries.RecordSighting(ctx, sighting); err != nil {
			return fmt.Errorf("recording a sighting: %w", err)
		}
	}

	rows, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	fmt.Fprintln(output, "ring    nickname  place")
	for _, row := range rows {
		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orEmpty(row.Place))
	}

	summaries, err := queries.SummariseGulls(ctx)
	if err != nil {
		return fmt.Errorf("summarising gulls: %w", err)
	}
	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
	for _, summary := range summaries {
		lastSeen := "NULL"
		if summary.LastSeen != nil {
			lastSeen = summary.LastSeen.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
	}
	return nil
}

// day returns a date in May 2026, at midnight UTC. A Date parameter binds the calendar
// date a time has in its own location.
//
// Takes date (int) which is the day of the month.
//
// Returns time.Time which is the date.
func day(date int) time.Time {
	return time.Date(2026, time.May, date, 0, 0, 0, 0, time.UTC)
}

// orNull returns the text a nullable field holds, or NULL when the field is nil.
//
// Takes value (*string) which is a nullable field.
//
// Returns string which is the text to print.
func orNull(value *string) string {
	if value == nil {
		return "NULL"
	}
	return *value
}

// orEmpty returns a string field's text, or (empty) when it holds the empty string, the
// default value ClickHouse gives a String column in an unmatched outer-join row.
//
// Takes value (string) which is a String column.
//
// Returns string which is the text to print.
func orEmpty(value string) string {
	if value == "" {
		return "(empty)"
	}
	return value
}

What it prints

ring    nickname  place
GB1001  Steven    the pier
GB1001  Steven    the chip shop
GB1002  NULL      the harbour wall
GB1003  Gwen      (empty)

ring    known as     times seen  last seen
GB1001  Steven       2           2026-05-03
GB1002  ring GB1002  1           2026-05-02
GB1003  Gwen         0           NULL

Try it

Add SETTINGS join_use_nulls = 1 to the end of ListSightings and run sgl generate. Place becomes a *string, because the unmatched row is now NULL, and once the program prints it with orNull rather than orEmpty, the gull that was never seen shows NULL instead of (empty).

Inference follows the SQL, not your data. When you know a column cannot be NULL in practice, sgl.column(name, nullable: false) in the query's header overrides it for that query; Type overrides covers the directive.

Across engines

Only these Go types of SummariseGulls differ between engines; the rest are the same everywhere.

ClickHouseSQLitePostgreSQLMySQLDuckDB
times_seen columnuint64int64int64int64int64
last_seen column*time.Time*string*time.Time*time.Time*time.Time

CockroachDB and TimescaleDB run the PostgreSQL version unchanged.

MariaDB runs the MySQL version unchanged.

Compare the whole example side by side with SQLite, PostgreSQL, MySQL and DuckDB.

Run it locally

From the directory you cloned the Seagull repository into:

cd seagull/examples/clickhouse && go run ./004_nullability

Its test runs it too, and checks what it prints against output.txt:

cd seagull/examples/clickhouse && SGL_EXAMPLE_DSN=clickhouse://localhost:9000/default go test ./004_nullability

Needs a running ClickHouse server: it connects to clickhouse://localhost:9000/default, or pass -dsn with the address of an empty database.

The files

Every file of the example, as it is in the Seagull repository.

sgl.yaml

# Copyright 2026 PolitePixels Limited
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

# This project stands against fascism, authoritarianism, and all forms of
# oppression. We built this to empower people, not to enable those who would
# strip others of their rights and dignity.

version: 1

databases:
  - name: colony
    dialect: clickhouse
    migrations: db/migrations
    queries: db/queries
    go:
      package: store
      out: internal/store

db/migrations/0001_gulls.up.sql

CREATE TABLE gulls (
    id       UInt64,
    ring     String,
    nickname Nullable(String)
) ENGINE = MergeTree() ORDER BY id;

CREATE TABLE sightings (
    id      UInt64,
    gull_id UInt64,
    seen_on Date,
    place   String
) ENGINE = MergeTree() ORDER BY id;

db/migrations/0001_gulls.down.sql

DROP TABLE sightings;
DROP TABLE gulls;

db/queries/gulls.sql

-- sgl.query(RingGull, exec)
INSERT INTO gulls (id, ring, nickname) VALUES ({id:UInt64}, {ring:String}, {nickname:Nullable(String)});

-- sgl.query(RecordSighting, exec)
INSERT INTO sightings (id, gull_id, seen_on, place) VALUES ({id:UInt64}, {gull_id:UInt64}, {seen_on:Date}, {place:String});

-- sgl.query(ListSightings, many)
SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;

-- sgl.query(SummariseGulls, many)
SELECT
    ring,
    coalesce(nickname, concat('ring ', ring)) AS known_as,
    count(seen_on) AS times_seen,
    max(seen_on) AS last_seen
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
GROUP BY gulls.id, ring, nickname
ORDER BY gulls.id
SETTINGS join_use_nulls = 1;

example.go

package main

import (
	"context"
	"database/sql"
	"fmt"
	"io"
	"time"

	"seagull.sh/seagull/examples/clickhouse/004_nullability/internal/store"
)

// run rings three gulls, records where two of them were seen, and lists them twice.
// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
// a pointer:
//
//   - gulls.nickname is Nullable(String), so Nickname is *string, as a parameter and a
//     column; a ClickHouse column is never NULL unless its type is Nullable(T);
//   - ListSightings leaves join_use_nulls at the server's default, 0, so its LEFT JOIN
//     fills an unmatched row with each column's default value instead of NULL. Place is a
//     String column, so it stays a string, and Gwen, who was never seen, gets an empty
//     one;
//   - SummariseGulls sets join_use_nulls = 1, so the unmatched row is NULL: count skips
//     it and gives 0, and max over no dates gives NULL, so TimesSeen is uint64 and
//     LastSeen is *time.Time. Without the setting, count would see Gwen's default row and
//     give 1;
//   - coalesce with a fallback that is never NULL is never NULL, so KnownAs is string.
//
// Takes database (*sql.DB) which is a migrated database.
// Takes output (io.Writer) which receives both listings.
//
// Returns error when a query fails.
func run(ctx context.Context, database *sql.DB, output io.Writer) error {
	queries := store.New(database)

	gulls := []store.RingGullParams{
		{ID: 1, Ring: "GB1001", Nickname: new("Steven")},
		{ID: 2, Ring: "GB1002", Nickname: nil},
		{ID: 3, Ring: "GB1003", Nickname: new("Gwen")},
	}
	for _, gull := range gulls {
		if err := queries.RingGull(ctx, gull); err != nil {
			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
		}
	}
	sightings := []store.RecordSightingParams{
		{ID: 1, GullID: 1, SeenOn: day(1), Place: "the pier"},
		{ID: 2, GullID: 1, SeenOn: day(3), Place: "the chip shop"},
		{ID: 3, GullID: 2, SeenOn: day(2), Place: "the harbour wall"},
	}
	for _, sighting := range sightings {
		if err := queries.RecordSighting(ctx, sighting); err != nil {
			return fmt.Errorf("recording a sighting: %w", err)
		}
	}

	rows, err := queries.ListSightings(ctx)
	if err != nil {
		return fmt.Errorf("listing sightings: %w", err)
	}
	fmt.Fprintln(output, "ring    nickname  place")
	for _, row := range rows {
		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orEmpty(row.Place))
	}

	summaries, err := queries.SummariseGulls(ctx)
	if err != nil {
		return fmt.Errorf("summarising gulls: %w", err)
	}
	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
	for _, summary := range summaries {
		lastSeen := "NULL"
		if summary.LastSeen != nil {
			lastSeen = summary.LastSeen.Format(time.DateOnly)
		}
		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
	}
	return nil
}

// day returns a date in May 2026, at midnight UTC. A Date parameter binds the calendar
// date a time has in its own location.
//
// Takes date (int) which is the day of the month.
//
// Returns time.Time which is the date.
func day(date int) time.Time {
	return time.Date(2026, time.May, date, 0, 0, 0, 0, time.UTC)
}

// orNull returns the text a nullable field holds, or NULL when the field is nil.
//
// Takes value (*string) which is a nullable field.
//
// Returns string which is the text to print.
func orNull(value *string) string {
	if value == nil {
		return "NULL"
	}
	return *value
}

// orEmpty returns a string field's text, or (empty) when it holds the empty string, the
// default value ClickHouse gives a String column in an unmatched outer-join row.
//
// Takes value (string) which is a String column.
//
// Returns string which is the text to print.
func orEmpty(value string) string {
	if value == "" {
		return "(empty)"
	}
	return value
}

main.go

// Command 004_nullability applies its migrations to a ClickHouse database, then runs the
// example in example.go: ringed gulls and their sightings are listed through a LEFT JOIN,
// coalesce and aggregates, printing NULL where a pointer field is nil.
//
// Run it with `go run .` against a local server, or `go run . -dsn <dsn>` to choose the
// database; give it an empty one.
package main

import (
	"context"
	"database/sql"
	"embed"
	"flag"
	"fmt"
	"log"
	"os"

	_ "github.com/ClickHouse/clickhouse-go/v2"

	"seagull.sh/seagull/sdk/migrate"
	"seagull.sh/seagull/sdk/migrate/migrate_backend_clickhouse"
)

// migrations holds the migration files, compiled into the binary.
//
//go:embed db/migrations
var migrations embed.FS

func main() {
	dsn := flag.String("dsn", "clickhouse://localhost:9000/default", "ClickHouse DSN of an empty database")
	flag.Parse()
	if err := execute(context.Background(), *dsn); err != nil {
		log.Fatal(err)
	}
}

// execute opens and migrates the database the DSN names, and runs the example against it.
//
// Takes dsn (string) which names the ClickHouse database.
//
// Returns error when the database cannot be prepared or the example fails.
func execute(ctx context.Context, dsn string) error {
	database, err := sql.Open("clickhouse", dsn)
	if err != nil {
		return err
	}
	defer func() { _ = database.Close() }()

	migrator, err := migrate.NewMigrator(database, migrate_backend_clickhouse.New(), migrations, "db/migrations")
	if err != nil {
		return err
	}
	if _, err := migrator.Up(ctx); err != nil {
		return fmt.Errorf("migrating: %w", err)
	}
	return run(ctx, database, os.Stdout)
}

internal/store/clickhouse_values.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"errors"
	"fmt"
	"math"
	"net"
	"net/netip"
	"reflect"
	"slices"
	"strconv"
	"strings"
	"time"
)

const (
	sglClickHouseMaxDepth         = 32
	sglClickHouseNanosecondDigits = 9
	sglClickHouseDigitGroup       = 3
)

var (
	sglClickHouseTextEscaper    = strings.NewReplacer("\\", "\\\\", "\t", "\\t", "\n", "\\n", "\r", "\\r", "\x00", "\\0")
	sglClickHouseQuotedEscaper  = strings.NewReplacer("\\", "\\\\", "'", "\\'", "\t", "\\t", "\n", "\\n", "\r", "\\r", "\x00", "\\0")
	sglClickHouseRefusalCleaner = strings.NewReplacer("\t", " ", "\n", " ", "\r", " ")
	errSglClickHouseShape       = errors.New("sgl: malformed ClickHouse parameter shape")
	errSglClickHouseValue       = errors.New("sgl: cannot write the value of a ClickHouse parameter")
)

type sglClickHouseShape struct {
	names     []string
	elements  []sglClickHouseShape
	digits    int
	prepared  bool
	checked   bool
	driverMap bool
	kind      byte
}

var sglClickHouseShapes = []sglClickHouseShape{{kind: 110, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}, {kind: 100, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}, {kind: 113, digits: -1, prepared: true, checked: false, driverMap: false, names: []string{}, elements: []sglClickHouseShape{}}}

func sglClickHouseValue(value any, shape string) string {
	parsed, err := sglClickHouseParseShape(shape)
	if err == nil {
		return sglClickHouseShapedValue(value, parsed)
	}
	return sglClickHouseRefusalCleaner.Replace(err.Error()) + "\t"
}

func sglClickHouseShapedValue(value any, shape sglClickHouseShape) string {
	text, err := sglClickHouseWhole(reflect.ValueOf(value), shape)
	if err != nil {
		return sglClickHouseRefusalCleaner.Replace(err.Error()) + "\t"
	}
	return text
}

func sglClickHouseWhole(value reflect.Value, shape sglClickHouseShape) (string, error) {
	current, err := sglClickHouseFollow(value, 0)
	if err != nil {
		return "", err
	}
	if !current.IsValid() {
		return "\\N", nil
	}
	shape = sglClickHouseResolve(current, shape)
	switch shape.kind {
	case '[', '{', '(':
		return sglClickHouseComposite(current, shape, 0)
	}
	text, err := sglClickHouseScalar(current, shape)
	if err != nil {
		return "", err
	}
	return sglClickHouseTextEscaper.Replace(text), nil
}

func sglClickHouseAppendElement(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	current, err := sglClickHouseFollow(value, depth)
	if err != nil {
		return nil, err
	}
	if !current.IsValid() {
		return append(text, "NULL"...), nil
	}
	shape = sglClickHouseResolve(current, shape)
	switch shape.kind {
	case '[', '{', '(':
		return sglClickHouseAppendComposite(text, current, shape, depth)
	}
	if shape.kind == 'n' && sglClickHouseIsNumber(current) {
		return sglClickHouseAppendNumber(text, current), nil
	}
	scalar, err := sglClickHouseScalar(current, shape)
	if err != nil {
		return nil, err
	}
	if shape.kind == 'n' {
		return append(text, scalar...), nil
	}
	text = append(text, '\'')
	text = append(text, sglClickHouseQuotedEscaper.Replace(scalar)...)
	return append(text, '\''), nil
}

func sglClickHouseAppendNumber(text []byte, value reflect.Value) []byte {
	const decimalBase = 10
	switch value.Kind() {
	case reflect.Bool:
		return strconv.AppendBool(text, value.Bool())
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
		return strconv.AppendInt(text, value.Int(), decimalBase)
	case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr:
		return strconv.AppendUint(text, value.Uint(), decimalBase)
	}
	number := value.Float()
	switch {
	case math.IsNaN(number):
		return append(text, "nan"...)
	case math.IsInf(number, 1):
		return append(text, "inf"...)
	case math.IsInf(number, -1):
		return append(text, "-inf"...)
	}
	return strconv.AppendFloat(text, number, 'g', -1, value.Type().Bits())
}

func sglClickHouseFollow(value reflect.Value, depth int) (reflect.Value, error) {
	for value.IsValid() && (value.Kind() == reflect.Pointer || value.Kind() == reflect.Interface) {
		if value.IsNil() {
			return reflect.Value{}, nil
		}
		depth++
		if depth > sglClickHouseMaxDepth {
			return reflect.Value{}, fmt.Errorf("%w: it nests more than %d levels deep or refers to itself", errSglClickHouseValue, sglClickHouseMaxDepth)
		}
		value = value.Elem()
	}
	return value, nil
}

func sglClickHouseResolve(value reflect.Value, shape sglClickHouseShape) sglClickHouseShape {
	if shape.kind != 'a' {
		return shape
	}
	unknown := sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'a', checked: false, driverMap: false, prepared: false}
	if _, moment := sglClickHouseMoment(value); moment {
		return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 't', checked: false, driverMap: false, prepared: false}
	}
	if sglClickHouseIsNumber(value) {
		return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'n', checked: false, driverMap: false, prepared: false}
	}
	if sglClickHouseHasText(value) {
		return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'q', checked: false, driverMap: false, prepared: false}
	}
	switch value.Kind() {
	case reflect.Slice, reflect.Array:
		if value.Type().Elem().Kind() != reflect.Uint8 {
			return sglClickHouseShape{names: nil, elements: []sglClickHouseShape{unknown}, digits: -1, kind: '[', checked: false, driverMap: false, prepared: false}
		}
	case reflect.Map:
		return sglClickHouseShape{names: nil, elements: []sglClickHouseShape{unknown, unknown}, digits: -1, kind: '{', checked: false, driverMap: false, prepared: false}
	case reflect.Struct:
		fields := make([]sglClickHouseShape, value.NumField())
		for index := range fields {
			fields[index] = unknown
		}
		return sglClickHouseShape{names: nil, elements: fields, digits: -1, kind: '(', checked: false, driverMap: false, prepared: false}
	}
	return sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 'q', checked: false, driverMap: false, prepared: false}
}

func sglClickHouseComposite(value reflect.Value, shape sglClickHouseShape, depth int) (string, error) {
	const initialCapacity = 64
	text, err := sglClickHouseAppendComposite(make([]byte, 0, initialCapacity), value, shape, depth)
	return string(text), err
}

func sglClickHouseAppendComposite(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	if depth >= sglClickHouseMaxDepth {
		return nil, fmt.Errorf("%w: it nests more than %d levels deep or refers to itself", errSglClickHouseValue, sglClickHouseMaxDepth)
	}
	depth++
	switch shape.kind {
	case '[':
		return sglClickHouseAppendArray(text, value, shape, depth)
	case '{':
		return sglClickHouseAppendMap(text, value, shape, depth)
	default:
		return sglClickHouseAppendTuple(text, value, shape, depth)
	}
}

func sglClickHouseAppendArray(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	if value.Kind() != reflect.Slice && value.Kind() != reflect.Array {
		return nil, fmt.Errorf("%w: an array needs a slice or an array, not %s", errSglClickHouseValue, value.Type())
	}
	text = append(text, '[')
	for index := 0; index < value.Len(); index++ {
		if index > 0 {
			text = append(text, ',')
		}
		var err error
		text, err = sglClickHouseAppendElement(text, value.Index(index), shape.elements[0], depth)
		if err != nil {
			return nil, err
		}
	}
	return append(text, ']'), nil
}

func sglClickHouseAppendMap(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	if value.Kind() != reflect.Map {
		return nil, fmt.Errorf("%w: a map needs a Go map, not %s", errSglClickHouseValue, value.Type())
	}
	parts := make([]string, 0, value.Len())
	entries := value.MapRange()
	for entries.Next() {
		part, err := sglClickHouseAppendElement(nil, entries.Key(), shape.elements[0], depth)
		if err != nil {
			return nil, err
		}
		part = append(part, ':')
		part, err = sglClickHouseAppendElement(part, entries.Value(), shape.elements[1], depth)
		if err != nil {
			return nil, err
		}
		parts = append(parts, string(part))
	}
	slices.Sort(parts)
	text = append(text, '{')
	for index, part := range parts {
		if index > 0 {
			text = append(text, ',')
		}
		text = append(text, part...)
	}
	return append(text, '}'), nil
}

func sglClickHouseAppendTuple(text []byte, value reflect.Value, shape sglClickHouseShape, depth int) ([]byte, error) {
	elements, err := sglClickHouseTupleElements(value, shape)
	if err != nil {
		return nil, err
	}
	text = append(text, '(')
	for index, element := range elements {
		if index > 0 {
			text = append(text, ", "...)
		}
		text, err = sglClickHouseAppendElement(text, element, shape.elements[index], depth)
		if err != nil {
			return nil, err
		}
	}
	return append(text, ')'), nil
}

func sglClickHouseTupleElements(value reflect.Value, shape sglClickHouseShape) ([]reflect.Value, error) {
	var elements []reflect.Value
	switch value.Kind() {
	case reflect.Struct:
		for index := range value.NumField() {
			if !value.Type().Field(index).IsExported() {
				return nil, fmt.Errorf("%w: a tuple cannot be read from %s, which has unexported fields", errSglClickHouseValue, value.Type())
			}
			elements = append(elements, value.Field(index))
		}
	case reflect.Map:
		if value.Type().Key().Kind() != reflect.String || slices.Contains(shape.names, "") {
			return nil, fmt.Errorf("%w: a tuple is read from a map only by field names", errSglClickHouseValue)
		}
		for _, name := range shape.names {
			elements = append(elements, value.MapIndex(reflect.ValueOf(name).Convert(value.Type().Key())))
		}
	case reflect.Slice, reflect.Array:
		for index := range value.Len() {
			elements = append(elements, value.Index(index))
		}
	default:
		return nil, fmt.Errorf("%w: a tuple needs a struct, a map or a slice, not %s", errSglClickHouseValue, value.Type())
	}
	if len(elements) != len(shape.elements) {
		return nil, fmt.Errorf("%w: the tuple has %d fields but the value holds %d", errSglClickHouseValue, len(shape.elements), len(elements))
	}
	return elements, nil
}

func sglClickHouseScalar(value reflect.Value, shape sglClickHouseShape) (string, error) {
	if shape.kind == '4' || shape.kind == '6' {
		address, isAddress, failure := sglClickHouseAddress(value, shape.kind)
		if failure != "" {
			return "", fmt.Errorf("%w: %s", errSglClickHouseValue, failure)
		}
		if isAddress {
			return address.String(), nil
		}
	}
	if moment, ok := sglClickHouseMoment(value); ok {
		return sglClickHouseTime(moment, shape)
	}
	if shape.kind == 'n' && sglClickHouseIsNumber(value) {
		return sglClickHouseNumber(value), nil
	}
	text, err := sglClickHouseText(value)
	if err == nil && shape.kind == 'n' && !sglClickHouseIsNumberText(text) {
		err = fmt.Errorf("%w: %q is not a number", errSglClickHouseValue, text)
	}
	return text, err
}

func sglClickHouseAddress(value reflect.Value, kind byte) (netip.Addr, bool, string) {
	var address netip.Addr
	switch value.Type() {
	case reflect.TypeFor[netip.Addr]():
		reflect.ValueOf(&address).Elem().Set(value)
		if !address.IsValid() {
			return address, true, "the zero netip.Addr holds no address"
		}
	case reflect.TypeFor[net.IP]():
		bytes := value.Bytes()
		if len(bytes) == 0 {
			return address, true, "an empty net.IP holds no address"
		}
		parsed, valid := netip.AddrFromSlice(bytes)
		if !valid {
			return address, true, fmt.Sprintf("a net.IP of %d bytes is not an IP address", len(bytes))
		}
		address = parsed
	default:
		return address, false, ""
	}
	address = address.WithZone("")
	if kind != '4' {
		return address, true, ""
	}
	if address.Is6() && !address.Is4In6() {
		return address, true, address.String() + " is an IPv6 address, which an IPv4 value cannot hold"
	}
	return address.Unmap(), true, ""
}

func sglClickHouseMoment(value reflect.Value) (time.Time, bool) {
	return reflect.TypeAssert[time.Time](value)
}

func sglClickHouseTime(moment time.Time, shape sglClickHouseShape) (string, error) {
	switch shape.kind {
	case 'd':
		return moment.Format("2006-01-02"), nil
	case 't':
		return sglClickHouseEpoch(moment, shape.digits), nil
	case 'q':
		return moment.Format(time.RFC3339Nano), nil
	default:
		return "", fmt.Errorf("%w: a time.Time cannot be written as a number", errSglClickHouseValue)
	}
}

func sglClickHouseEpoch(moment time.Time, digits int) string {
	const decimalBase = 10
	nanoseconds := int64(moment.Nanosecond())
	if digits < 0 {
		digits = sglClickHouseNanosecondDigits
		for digits > 0 && nanoseconds%int64(math.Pow10(sglClickHouseNanosecondDigits-digits+sglClickHouseDigitGroup)) == 0 {
			digits -= sglClickHouseDigitGroup
		}
	}
	seconds := moment.Unix()
	if digits == 0 {
		return strconv.FormatInt(seconds, decimalBase)
	}
	scale := int64(math.Pow10(digits))
	fraction := nanoseconds / int64(math.Pow10(sglClickHouseNanosecondDigits-digits))
	sign := ""
	if seconds < 0 {
		sign = "-"
		seconds = -seconds
		if fraction > 0 {
			seconds--
			fraction = scale - fraction
		}
	}
	return fmt.Sprintf("%s%d.%0*d", sign, seconds, digits, fraction)
}

func sglClickHouseIsNumber(value reflect.Value) bool {
	switch value.Kind() {
	case reflect.Bool, reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64,
		reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr,
		reflect.Float32, reflect.Float64:
		return true
	}
	return false
}

func sglClickHouseNumber(value reflect.Value) string {
	const decimalBase = 10
	switch value.Kind() {
	case reflect.Bool:
		return strconv.FormatBool(value.Bool())
	case reflect.Int, reflect.Int8, reflect.Int16, reflect.Int32, reflect.Int64:
		return strconv.FormatInt(value.Int(), decimalBase)
	case reflect.Uint, reflect.Uint8, reflect.Uint16, reflect.Uint32, reflect.Uint64, reflect.Uintptr:
		return strconv.FormatUint(value.Uint(), decimalBase)
	}
	number := value.Float()
	switch {
	case math.IsNaN(number):
		return "nan"
	case math.IsInf(number, 1):
		return "inf"
	case math.IsInf(number, -1):
		return "-inf"
	}
	return strconv.FormatFloat(number, 'g', -1, value.Type().Bits())
}

func sglClickHouseMethods(value reflect.Value) []any {
	if value.CanAddr() {
		return []any{value.Interface(), value.Addr().Interface()}
	}
	return []any{value.Interface()}
}

func sglClickHouseHasText(value reflect.Value) bool {
	for _, candidate := range sglClickHouseMethods(value) {
		switch candidate.(type) {
		case fmt.Stringer, interface{ String() (string, error) }, error:
			return true
		}
	}
	return false
}

func sglClickHouseText(value reflect.Value) (string, error) {
	for _, candidate := range sglClickHouseMethods(value) {
		switch typed := candidate.(type) {
		case fmt.Stringer:
			return typed.String(), nil
		case interface{ String() (string, error) }:
			text, err := typed.String()
			if err != nil {
				return "", fmt.Errorf("%w: its String method failed: %w", errSglClickHouseValue, err)
			}
			return text, nil
		case error:
			return "", fmt.Errorf("%w: an error is not a value", errSglClickHouseValue)
		}
	}
	switch value.Kind() {
	case reflect.String:
		return value.String(), nil
	case reflect.Slice, reflect.Array:
		if value.Type().Elem().Kind() == reflect.Uint8 {
			bytes := make([]byte, value.Len())
			reflect.Copy(reflect.ValueOf(bytes), value)
			return string(bytes), nil
		}
	}
	if sglClickHouseIsNumber(value) {
		return sglClickHouseNumber(value), nil
	}
	return "", fmt.Errorf("%w: a %s cannot be written as text", errSglClickHouseValue, value.Type())
}

func sglClickHouseIsNumberText(text string) bool {
	if text == "true" || text == "false" {
		return true
	}
	lower := strings.ToLower(sglClickHouseUnsigned(text))
	if lower == "nan" || lower == "inf" {
		return true
	}
	mantissa, exponent, scientific := strings.Cut(lower, "e")
	if scientific {
		exponent = sglClickHouseUnsigned(exponent)
		if exponent == "" || strings.Trim(exponent, "0123456789") != "" {
			return false
		}
	}
	whole, fraction, _ := strings.Cut(mantissa, ".")
	return whole+fraction != "" && strings.Trim(whole+fraction, "0123456789") == ""
}

func sglClickHouseUnsigned(text string) string {
	if strings.HasPrefix(text, "+") || strings.HasPrefix(text, "-") {
		return text[1:]
	}
	return text
}

func sglClickHouseParseShape(text string) (sglClickHouseShape, error) {
	shape, rest, err := sglClickHouseReadShape(text, 0)
	if err == nil && rest != "" {
		err = fmt.Errorf("%w: %q has %q left over", errSglClickHouseShape, text, rest)
	}
	return shape, err
}

func sglClickHouseReadShape(text string, depth int) (sglClickHouseShape, string, error) {
	shape := sglClickHouseShape{names: nil, elements: nil, digits: -1, kind: 0, checked: false, driverMap: false, prepared: false}
	if text == "" || depth > sglClickHouseMaxDepth {
		return shape, "", fmt.Errorf("%w: a shape is missing or nests too deeply", errSglClickHouseShape)
	}
	shape.kind, text = text[0], text[1:]
	switch shape.kind {
	case 'q', 'n', 'd', 'a', '4', '6':
		return shape, text, nil
	case 't':
		shape.digits = 0
		if text != "" && text[0] >= '0' && text[0] <= '9' {
			shape.digits, text = int(text[0]-'0'), text[1:]
		}
		return shape, text, nil
	case '[':
		return sglClickHouseReadElements(shape, text, depth, ']')
	case '{':
		return sglClickHouseReadElements(shape, text, depth, '}')
	case '(':
		return sglClickHouseReadElements(shape, text, depth, ')')
	}
	return shape, "", fmt.Errorf("%w: %q is not a shape", errSglClickHouseShape, shape.kind)
}

func sglClickHouseReadElements(shape sglClickHouseShape, text string, depth int, closing byte) (sglClickHouseShape, string, error) {
	for {
		var err error
		if shape, text, err = sglClickHouseReadElement(shape, text, depth); err != nil {
			return shape, "", err
		}
		if text == "" {
			return shape, "", fmt.Errorf("%w: %q is not closed", errSglClickHouseShape, string(shape.kind))
		}
		switch {
		case text[0] == closing && (shape.kind != '{' || len(shape.elements) == 2):
			return shape, text[1:], nil
		case shape.kind == '(' && text[0] == ',', shape.kind == '{' && text[0] == ':' && len(shape.elements) == 1:
			text = text[1:]
		default:
			return shape, "", fmt.Errorf("%w: %q is out of place", errSglClickHouseShape, text[:1])
		}
	}
}

func sglClickHouseReadElement(shape sglClickHouseShape, text string, depth int) (sglClickHouseShape, string, error) {
	name := ""
	if shape.kind == '(' && strings.HasPrefix(text, "'") {
		var err error
		if name, text, err = sglClickHouseReadName(text); err != nil {
			return shape, "", err
		}
	}
	element, rest, err := sglClickHouseReadShape(text, depth+1)
	if err != nil {
		return shape, "", err
	}
	shape.elements = append(shape.elements, element)
	shape.names = append(shape.names, name)
	return shape, rest, nil
}

func sglClickHouseReadName(text string) (name string, rest string, err error) {
	var written strings.Builder
	index := 1
	for index < len(text) {
		if text[index] != '\'' {
			written.WriteByte(text[index])
			index++
			continue
		}
		if index+1 < len(text) && text[index+1] == '\'' {
			written.WriteByte('\'')
			index += 2
			continue
		}
		if index+1 < len(text) && text[index+1] == '=' {
			return written.String(), text[index+2:], nil
		}
		break
	}
	return "", "", fmt.Errorf("%w: a tuple field's name is not closed by '=", errSglClickHouseShape)
}

internal/store/gulls.sql.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"time"

	"github.com/ClickHouse/clickhouse-go/v2"
)

const listSightingsSQL = `SELECT gulls.ring, gulls.nickname, sightings.place
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
ORDER BY gulls.id, sightings.seen_on;`

type ListSightingsRow struct {
	Ring     string  `json:"ring"`
	Nickname *string `json:"nickname"`
	Place    string  `json:"place"`
}

func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, listSightingsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []ListSightingsRow
	for rows.Next() {
		var row ListSightingsRow
		if err := rows.Scan(&row.Ring, &row.Nickname, &row.Place); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

const recordSightingSQL = `INSERT INTO sightings (id, gull_id, seen_on, place) VALUES ({p_id:UInt64}, {p_gull_id:UInt64}, {p_seen_on:Date}, {p_place:String});`

type RecordSightingParams struct {
	ID     uint64
	GullID uint64
	SeenOn time.Time
	Place  string
}

func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error {
	_, err := queries.writer.ExecContext(ctx, recordSightingSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_gull_id", sglClickHouseShapedValue(params.GullID, sglClickHouseShapes[0])), clickhouse.Named("p_seen_on", sglClickHouseShapedValue(params.SeenOn, sglClickHouseShapes[1])), clickhouse.Named("p_place", sglClickHouseShapedValue(params.Place, sglClickHouseShapes[2])))
	return err
}

const ringGullSQL = `INSERT INTO gulls (id, ring, nickname) VALUES ({p_id:UInt64}, {p_ring:String}, {p_nickname:Nullable(String)});`

type RingGullParams struct {
	ID       uint64
	Ring     string
	Nickname *string
}

func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error {
	_, err := queries.writer.ExecContext(ctx, ringGullSQL, clickhouse.Named("p_id", sglClickHouseShapedValue(params.ID, sglClickHouseShapes[0])), clickhouse.Named("p_ring", sglClickHouseShapedValue(params.Ring, sglClickHouseShapes[2])), clickhouse.Named("p_nickname", sglClickHouseShapedValue(params.Nickname, sglClickHouseShapes[2])))
	return err
}

const summariseGullsSQL = `SELECT
    ring,
    coalesce(nickname, concat('ring ', ring)) AS known_as,
    count(seen_on) AS times_seen,
    max(seen_on) AS last_seen
FROM gulls
LEFT JOIN sightings ON sightings.gull_id = gulls.id
GROUP BY gulls.id, ring, nickname
ORDER BY gulls.id
SETTINGS join_use_nulls = 1;`

type SummariseGullsRow struct {
	Ring      string     `json:"ring"`
	KnownAs   string     `json:"known_as"`
	TimesSeen uint64     `json:"times_seen"`
	LastSeen  *time.Time `json:"last_seen"`
}

func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error) {
	rows, err := queries.reader.QueryContext(ctx, summariseGullsSQL)
	if err != nil {
		return nil, err
	}
	defer rows.Close()
	var results []SummariseGullsRow
	for rows.Next() {
		var row SummariseGullsRow
		if err := rows.Scan(&row.Ring, &row.KnownAs, &row.TimesSeen, &row.LastSeen); err != nil {
			return nil, err
		}
		results = append(results, row)
	}
	if err := rows.Err(); err != nil {
		return nil, err
	}
	return results, nil
}

internal/store/models.go

// Code generated by sgl. DO NOT EDIT.

package store

import "time"

type Gulls struct {
	ID       uint64  `json:"id"`
	Ring     string  `json:"ring"`
	Nickname *string `json:"nickname"`
}

type Sightings struct {
	ID     uint64    `json:"id"`
	GullID uint64    `json:"gull_id"`
	SeenOn time.Time `json:"seen_on"`
	Place  string    `json:"place"`
}

internal/store/querier.go

// Code generated by sgl. DO NOT EDIT.

package store

import (
	"context"
	"database/sql"
)

type DBTX interface {
	ExecContext(ctx context.Context, query string, args ...any) (sql.Result, error)
	QueryContext(ctx context.Context, query string, args ...any) (*sql.Rows, error)
	QueryRowContext(ctx context.Context, query string, args ...any) *sql.Row
}

type dbtxWrapper interface {
	WrapDBTX(inner any) any
}

type Queries struct {
	reader  DBTX
	writer  DBTX
	wrapper dbtxWrapper
}

func New(db DBTX) *Queries {
	wrapper, _ := db.(dbtxWrapper)
	return &Queries{reader: db, writer: db, wrapper: wrapper}
}

func NewWithReplica(writer DBTX, reader DBTX) *Queries {
	wrapper, _ := writer.(dbtxWrapper)
	return &Queries{reader: reader, writer: writer, wrapper: wrapper}
}

func (queries *Queries) WithTx(transaction *sql.Tx) *Queries {
	if queries == nil {
		return &Queries{reader: transaction, writer: transaction, wrapper: nil}
	}
	db := queries.wrapDBTX(transaction)
	return &Queries{reader: db, writer: db, wrapper: queries.wrapper}
}

func (queries *Queries) wrapDBTX(db DBTX) DBTX {
	if queries.wrapper == nil {
		return db
	}
	wrapped, ok := queries.wrapper.WrapDBTX(db).(DBTX)
	if !ok {
		return db
	}
	return wrapped
}

func (queries *Queries) RunInTx(ctx context.Context, db *sql.DB, fn func(*Queries) error) error {
	transaction, err := db.BeginTx(ctx, nil)
	if err != nil {
		return err
	}
	defer transaction.Rollback()
	if err = fn(queries.WithTx(transaction)); err != nil {
		return err
	}
	return transaction.Commit()
}

output.txt

ring    nickname  place
GB1001  Steven    the pier
GB1001  Steven    the chip shop
GB1002  NULL      the harbour wall
GB1003  Gwen      (empty)

ring    known as     times seen  last seen
GB1001  Steven       2           2026-05-03
GB1002  ring GB1002  1           2026-05-02
GB1003  Gwen         0           NULL

Also on other engines

SQLiteRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.PostgreSQLRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.CockroachDBRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.TimescaleDBRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.MySQLRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.MariaDBRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.DuckDBRinged gulls and their sightings, where a nullable column, a LEFT JOIN and MAX give pointers while COALESCE and COUNT do not.