Compare · 004

Nullability through joins, COALESCE and aggregates: ClickHouse and SQLite

Every file of the ClickHouse and SQLite versions side by side, with the lines that differ marked. 5 parameter or column types differ in Go.

Compare ClickHouse withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
SQLite
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: colony
4 - name: colony
5 dialect: clickhouse
5 dialect: sqlite
6 migrations: db/migrations
6 migrations: db/migrations
7 queries: db/queries
7 queries: db/queries
8 go:
8 go:
9 package: store
9 package: store
10 out: internal/store
10 out: internal/store

Migrations

db/migrations/0001_gulls.up.sqldiffers
ClickHouse
SQLite
1CREATE TABLE gulls (
1CREATE TABLE gulls (
2 id UInt64,
2 id INTEGER PRIMARY KEY,
3 ring String,
3 ring TEXT NOT NULL UNIQUE,
4 nickname Nullable(String)
4 nickname TEXT
5) ENGINE = MergeTree() ORDER BY id;
5);
6
6
7CREATE TABLE sightings (
7CREATE TABLE sightings (
8 id UInt64,
8 id INTEGER PRIMARY KEY,
9 gull_id UInt64,
9 gull_id INTEGER NOT NULL REFERENCES gulls (id),
10 seen_on Date,
10 seen_on TEXT NOT NULL,
11 place String
11 place TEXT NOT NULL
12) ENGINE = MergeTree() ORDER BY id;
12);
db/migrations/0001_gulls.down.sqlidentical
ClickHouse
SQLite
1DROP TABLE sightings;
1DROP TABLE sightings;
2DROP TABLE gulls;
2DROP TABLE gulls;

Queries

RingGulldiffers
ClickHouse · db/queries/gulls.sql
SQLite · db/queries/gulls.sql
1-- sgl.query(RingGull, exec)
1-- sgl.query(RingGull, exec)
2INSERT INTO gulls (id, ring, nickname) VALUES ({id:UInt64}, {ring:String}, {nickname:Nullable(String)});
2INSERT INTO gulls (ring, nickname) VALUES (?, ?);
RecordSightingdiffers
ClickHouse · db/queries/gulls.sql
SQLite · db/queries/gulls.sql
1-- sgl.query(RecordSighting, exec)
1-- sgl.query(RecordSighting, exec)
2INSERT INTO sightings (id, gull_id, seen_on, place) VALUES ({id:UInt64}, {gull_id:UInt64}, {seen_on:Date}, {place:String});
2INSERT INTO sightings (gull_id, seen_on, place) VALUES (?, ?, ?);
ListSightingsidentical
ClickHouse · db/queries/gulls.sql
SQLite · db/queries/gulls.sql
1-- sgl.query(ListSightings, many)
1-- sgl.query(ListSightings, many)
2SELECT gulls.ring, gulls.nickname, sightings.place
2SELECT gulls.ring, gulls.nickname, sightings.place
3FROM gulls
3FROM gulls
4LEFT JOIN sightings ON sightings.gull_id = gulls.id
4LEFT JOIN sightings ON sightings.gull_id = gulls.id
5ORDER BY gulls.id, sightings.seen_on;
5ORDER BY gulls.id, sightings.seen_on;
SummariseGullsdiffers
ClickHouse · db/queries/gulls.sql
SQLite · db/queries/gulls.sql
1-- sgl.query(SummariseGulls, many)
1-- sgl.query(SummariseGulls, many)
2SELECT
2SELECT
3 ring,
3 gulls.ring,
4 coalesce(nickname, concat('ring ', ring)) AS known_as,
4 COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
5 count(seen_on) AS times_seen,
5 COUNT(sightings.id) AS times_seen,
6 max(seen_on) AS last_seen
6 MAX(sightings.seen_on) AS last_seen
7FROM gulls
7FROM gulls
8LEFT JOIN sightings ON sightings.gull_id = gulls.id
8LEFT JOIN sightings ON sightings.gull_id = gulls.id
9GROUP BY gulls.id, ring, nickname
9GROUP BY gulls.id
10ORDER BY gulls.id
10ORDER BY gulls.id;
11SETTINGS join_use_nulls = 1;

What sgl generates

RingGulldiffers
ClickHouse
SQLite
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error
1func (queries *Queries) RingGull(ctx context.Context, params RingGullParams) error
2
2
3type RingGullParams struct {
3type RingGullParams struct {
4 ID uint64
5 Ring string
4 Ring string
6 Nickname *string
5 Nickname *string
7}
6}
RecordSightingdiffers
ClickHouse
SQLite
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error
1func (queries *Queries) RecordSighting(ctx context.Context, params RecordSightingParams) error
2
2
3type RecordSightingParams struct {
3type RecordSightingParams struct {
4 ID uint64
4 GullID int64
5 GullID uint64
5 SeenOn string
6 SeenOn time.Time
7 Place string
6 Place string
8}
7}
ListSightingsdiffers
ClickHouse
SQLite
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
1func (queries *Queries) ListSightings(ctx context.Context) ([]ListSightingsRow, error)
2
2
3type ListSightingsRow struct {
3type ListSightingsRow struct {
4 Ring string `json:"ring"`
4 Ring string `json:"ring"`
5 Nickname *string `json:"nickname"`
5 Nickname *string `json:"nickname"`
6 Place string `json:"place"`
6 Place *string `json:"place"`
7}
7}
SummariseGullsdiffers
ClickHouse
SQLite
1func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error)
1func (queries *Queries) SummariseGulls(ctx context.Context) ([]SummariseGullsRow, error)
2
2
3type SummariseGullsRow struct {
3type SummariseGullsRow struct {
4 Ring string `json:"ring"`
4 Ring string `json:"ring"`
5 KnownAs string `json:"known_as"`
5 KnownAs string `json:"known_as"`
6 TimesSeen uint64 `json:"times_seen"`
6 TimesSeen int64 `json:"times_seen"`
7 LastSeen *time.Time `json:"last_seen"`
7 LastSeen *string `json:"last_seen"`
8}
8}

The program

example.godiffers
ClickHouse
SQLite
1package main
1package main
2
2
3import (
3import (
4 "context"
4 "context"
5 "database/sql"
5 "database/sql"
6 "fmt"
6 "fmt"
7 "io"
7 "io"
8 "time"
9
8
10 "seagull.sh/seagull/examples/clickhouse/004_nullability/internal/store"
9 "seagull.sh/seagull/examples/sqlite/004_nullability/internal/store"
11)
10)
12
11
13// run rings three gulls, records where two of them were seen, and lists them twice.
12// run rings three gulls, records where two of them were seen, and lists them twice.
14// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
13// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
15// a pointer:
14// a pointer:
16//
15//
17// - gulls.nickname is Nullable(String), so Nickname is *string, as a parameter and a
16// - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
18// column; a ClickHouse column is never NULL unless its type is Nullable(T);
17// column;
19// - ListSightings leaves join_use_nulls at the server's default, 0, so its LEFT JOIN
18// - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
20// fills an unmatched row with each column's default value instead of NULL. Place is a
19// NULL place, so Place is *string;
21// String column, so it stays a string, and Gwen, who was never seen, gets an empty
20// - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
22// one;
21// - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
23// - SummariseGulls sets join_use_nulls = 1, so the unmatched row is NULL: count skips
22// *string.
24// it and gives 0, and max over no dates gives NULL, so TimesSeen is uint64 and
25// LastSeen is *time.Time. Without the setting, count would see Gwen's default row and
26// give 1;
27// - coalesce with a fallback that is never NULL is never NULL, so KnownAs is string.
28//
23//
29// Takes database (*sql.DB) which is a migrated database.
24// Takes database (*sql.DB) which is a migrated database.
30// Takes output (io.Writer) which receives both listings.
25// Takes output (io.Writer) which receives both listings.
31//
26//
32// Returns error when a query fails.
27// Returns error when a query fails.
33func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28func run(ctx context.Context, database *sql.DB, output io.Writer) error {
34 queries := store.New(database)
29 queries := store.New(database)
35
30
36 gulls := []store.RingGullParams{
31 gulls := []store.RingGullParams{
37 {ID: 1, Ring: "GB1001", Nickname: new("Steven")},
32 {Ring: "GB1001", Nickname: new("Steven")},
38 {ID: 2, Ring: "GB1002", Nickname: nil},
33 {Ring: "GB1002", Nickname: nil},
39 {ID: 3, Ring: "GB1003", Nickname: new("Gwen")},
34 {Ring: "GB1003", Nickname: new("Gwen")},
40 }
35 }
41 for _, gull := range gulls {
36 for _, gull := range gulls {
42 if err := queries.RingGull(ctx, gull); err != nil {
37 if err := queries.RingGull(ctx, gull); err != nil {
43 return fmt.Errorf("ringing %s: %w", gull.Ring, err)
38 return fmt.Errorf("ringing %s: %w", gull.Ring, err)
44 }
39 }
45 }
40 }
46 sightings := []store.RecordSightingParams{
41 sightings := []store.RecordSightingParams{
47 {ID: 1, GullID: 1, SeenOn: day(1), Place: "the pier"},
42 {GullID: 1, SeenOn: "2026-05-01", Place: "the pier"},
48 {ID: 2, GullID: 1, SeenOn: day(3), Place: "the chip shop"},
43 {GullID: 1, SeenOn: "2026-05-03", Place: "the chip shop"},
49 {ID: 3, GullID: 2, SeenOn: day(2), Place: "the harbour wall"},
44 {GullID: 2, SeenOn: "2026-05-02", Place: "the harbour wall"},
50 }
45 }
51 for _, sighting := range sightings {
46 for _, sighting := range sightings {
52 if err := queries.RecordSighting(ctx, sighting); err != nil {
47 if err := queries.RecordSighting(ctx, sighting); err != nil {
53 return fmt.Errorf("recording a sighting: %w", err)
48 return fmt.Errorf("recording a sighting: %w", err)
54 }
49 }
55 }
50 }
56
51
57 rows, err := queries.ListSightings(ctx)
52 rows, err := queries.ListSightings(ctx)
58 if err != nil {
53 if err != nil {
59 return fmt.Errorf("listing sightings: %w", err)
54 return fmt.Errorf("listing sightings: %w", err)
60 }
55 }
61 fmt.Fprintln(output, "ring nickname place")
56 fmt.Fprintln(output, "ring nickname place")
62 for _, row := range rows {
57 for _, row := range rows {
63 fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orEmpty(row.Place))
58 fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
64 }
59 }
65
60
66 summaries, err := queries.SummariseGulls(ctx)
61 summaries, err := queries.SummariseGulls(ctx)
67 if err != nil {
62 if err != nil {
68 return fmt.Errorf("summarising gulls: %w", err)
63 return fmt.Errorf("summarising gulls: %w", err)
69 }
64 }
70 fmt.Fprintln(output, "\nring known as times seen last seen")
65 fmt.Fprintln(output, "\nring known as times seen last seen")
71 for _, summary := range summaries {
66 for _, summary := range summaries {
72 lastSeen := "NULL"
67 fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, orNull(summary.LastSeen))
73 if summary.LastSeen != nil {
74 lastSeen = summary.LastSeen.Format(time.DateOnly)
75 }
76 fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
77 }
68 }
78 return nil
69 return nil
79}
70}
80
71
81// day returns a date in May 2026, at midnight UTC. A Date parameter binds the calendar
82// date a time has in its own location.
83//
84// Takes date (int) which is the day of the month.
85//
86// Returns time.Time which is the date.
87func day(date int) time.Time {
88 return time.Date(2026, time.May, date, 0, 0, 0, 0, time.UTC)
89}
90
91// orNull returns the text a nullable field holds, or NULL when the field is nil.
72// orNull returns the text a nullable field holds, or NULL when the field is nil.
92//
73//
93// Takes value (*string) which is a nullable field.
74// Takes value (*string) which is a nullable field.
94//
75//
95// Returns string which is the text to print.
76// Returns string which is the text to print.
96func orNull(value *string) string {
77func orNull(value *string) string {
97 if value == nil {
78 if value == nil {
98 return "NULL"
79 return "NULL"
99 }
80 }
100 return *value
81 return *value
101}
82}
102
103// orEmpty returns a string field's text, or (empty) when it holds the empty string, the
104// default value ClickHouse gives a String column in an unmatched outer-join row.
105//
106// Takes value (string) which is a String column.
107//
108// Returns string which is the text to print.
109func orEmpty(value string) string {
110 if value == "" {
111 return "(empty)"
112 }
113 return value
114}

Go type differences

The same SQL types map to different Go types, because each database reports them differently.

QueryFieldClickHouseSQLite
RecordSightingparameter gull_iduint64int64
RecordSightingparameter seen_ontime.Timestring
ListSightingscolumn placestring*string
SummariseGullscolumn times_seenuint64int64
SummariseGullscolumn last_seen*time.Time*string