Compare · 004

Nullability through joins, COALESCE and aggregates: DuckDB and MySQL

Every file of the DuckDB and MySQL versions side by side, with the lines that differ marked.

Compare DuckDB withSQLite PostgreSQL CockroachDB TimescaleDB MySQL MariaDB ClickHouse

Configuration

sgl.yamldiffers

DuckDB
MySQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: colony
4  - name: colony
5    dialect: duckdb
5    dialect: mysql
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

DuckDB
MySQL
1CREATE SEQUENCE gull_ids;
2
3CREATE SEQUENCE sighting_ids;
4
5CREATE TABLE gulls (
1CREATE TABLE gulls (
6    id       BIGINT  PRIMARY KEY DEFAULT nextval('gull_ids'),
2    id       BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
7    ring     VARCHAR NOT NULL UNIQUE,
3    ring     VARCHAR(10) NOT NULL UNIQUE,
8    nickname VARCHAR
4    nickname VARCHAR(50)
9);
5);
10
6
11CREATE TABLE sightings (
7CREATE TABLE sightings (
12    id      BIGINT  PRIMARY KEY DEFAULT nextval('sighting_ids'),
8    id      BIGINT       NOT NULL AUTO_INCREMENT PRIMARY KEY,
13    gull_id BIGINT  NOT NULL REFERENCES gulls (id),
9    gull_id BIGINT       NOT NULL,
14    seen_on DATE    NOT NULL,
10    seen_on DATE         NOT NULL,
15    place   VARCHAR NOT NULL
11    place   VARCHAR(100) NOT NULL,
12    FOREIGN KEY (gull_id) REFERENCES gulls (id)
16);
13);

db/migrations/0001_gulls.down.sqldiffers

DuckDB
MySQL
1DROP TABLE sightings;
1DROP TABLE sightings;
2DROP TABLE gulls;
2DROP TABLE gulls;
3DROP SEQUENCE sighting_ids;
4DROP SEQUENCE gull_ids;

Queries

RingGulldiffers

DuckDB · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(RingGull, exec)
1-- sgl.query(RingGull, exec)
2INSERT INTO gulls (ring, nickname) VALUES ($1, $2);
2INSERT INTO gulls (ring, nickname) VALUES (?, ?);

RecordSightingdiffers

DuckDB · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(RecordSighting, exec)
1-- sgl.query(RecordSighting, exec)
2INSERT INTO sightings (gull_id, seen_on, place) VALUES ($1, $2, $3);
2INSERT INTO sightings (gull_id, seen_on, place) VALUES (?, ?, ?);

ListSightingsidentical

DuckDB · db/queries/gulls.sql
MySQL · 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

DuckDB · db/queries/gulls.sql
MySQL · db/queries/gulls.sql
1-- sgl.query(SummariseGulls, many)
1-- sgl.query(SummariseGulls, many)
2SELECT
2SELECT
3    gulls.ring,
3    gulls.ring,
4    COALESCE(gulls.nickname, 'ring ' || gulls.ring) AS known_as,
4    COALESCE(gulls.nickname, gulls.ring) AS known_as,
5    COUNT(sightings.id) AS times_seen,
5    COUNT(sightings.id) AS times_seen,
6    MAX(sightings.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, gulls.ring, gulls.nickname
9GROUP BY gulls.id
10ORDER BY gulls.id;
10ORDER BY gulls.id;

What sgl generates

RingGullidentical

DuckDB
MySQL
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	Ring     string
4	Ring     string
5	Nickname *string
5	Nickname *string
6}
6}

RecordSightingidentical

DuckDB
MySQL
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	GullID int64
4	GullID int64
5	SeenOn time.Time
5	SeenOn time.Time
6	Place  string
6	Place  string
7}
7}

ListSightingsidentical

DuckDB
MySQL
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}

SummariseGullsidentical

DuckDB
MySQL
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 int64      `json:"times_seen"`
6	TimesSeen int64      `json:"times_seen"`
7	LastSeen  *time.Time `json:"last_seen"`
7	LastSeen  *time.Time `json:"last_seen"`
8}
8}

The program

example.godiffers

DuckDB
MySQL
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"
8	"time"
9
9
10	"seagull.sh/seagull/examples/duckdb/004_nullability/internal/store"
10	"seagull.sh/seagull/examples/mysql/004_nullability/internal/store"
11)
11)
12
12
13// run rings three gulls, records where two of them were seen, and lists them twice.
13// 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
14// Seagull decides each field's nullability from the SQL, and a field that can be NULL is
15// a pointer:
15// a pointer:
16//
16//
17//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
17//   - gulls.nickname has no NOT NULL, so Nickname is *string, as a parameter and a
18//     column;
18//     column;
19//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
19//   - sightings.place is NOT NULL, but the LEFT JOIN gives a gull with no sightings a
20//     NULL place, so Place is *string;
20//     NULL place, so Place is *string;
21//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
21//   - COALESCE with a non-NULL last argument is never NULL, so KnownAs is string;
22//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
22//   - COUNT is never NULL, but MAX over no rows is, so TimesSeen is int64 and LastSeen is
23//     *time.Time, as seen_on is a DATE.
23//     *time.Time, the type of the DATE column it reads.
24//
24//
25// Takes database (*sql.DB) which is a migrated database.
25// Takes database (*sql.DB) which is a migrated database.
26// Takes output (io.Writer) which receives both listings.
26// Takes output (io.Writer) which receives both listings.
27//
27//
28// Returns error when a query fails.
28// Returns error when a query fails.
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30	queries := store.New(database)
30	queries := store.New(database)
31
31
32	gulls := []store.RingGullParams{
32	gulls := []store.RingGullParams{
33		{Ring: "GB1001", Nickname: new("Steven")},
33		{Ring: "GB1001", Nickname: new("Steven")},
34		{Ring: "GB1002", Nickname: nil},
34		{Ring: "GB1002", Nickname: nil},
35		{Ring: "GB1003", Nickname: new("Gwen")},
35		{Ring: "GB1003", Nickname: new("Gwen")},
36	}
36	}
37	for _, gull := range gulls {
37	for _, gull := range gulls {
38		if err := queries.RingGull(ctx, gull); err != nil {
38		if err := queries.RingGull(ctx, gull); err != nil {
39			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
39			return fmt.Errorf("ringing %s: %w", gull.Ring, err)
40		}
40		}
41	}
41	}
42	sightings := []store.RecordSightingParams{
42	sightings := []store.RecordSightingParams{
43		{GullID: 1, SeenOn: day(1), Place: "the pier"},
43		{GullID: 1, SeenOn: day(2026, time.May, 1), Place: "the pier"},
44		{GullID: 1, SeenOn: day(3), Place: "the chip shop"},
44		{GullID: 1, SeenOn: day(2026, time.May, 3), Place: "the chip shop"},
45		{GullID: 2, SeenOn: day(2), Place: "the harbour wall"},
45		{GullID: 2, SeenOn: day(2026, time.May, 2), Place: "the harbour wall"},
46	}
46	}
47	for _, sighting := range sightings {
47	for _, sighting := range sightings {
48		if err := queries.RecordSighting(ctx, sighting); err != nil {
48		if err := queries.RecordSighting(ctx, sighting); err != nil {
49			return fmt.Errorf("recording a sighting: %w", err)
49			return fmt.Errorf("recording a sighting: %w", err)
50		}
50		}
51	}
51	}
52
52
53	rows, err := queries.ListSightings(ctx)
53	rows, err := queries.ListSightings(ctx)
54	if err != nil {
54	if err != nil {
55		return fmt.Errorf("listing sightings: %w", err)
55		return fmt.Errorf("listing sightings: %w", err)
56	}
56	}
57	fmt.Fprintln(output, "ring    nickname  place")
57	fmt.Fprintln(output, "ring    nickname  place")
58	for _, row := range rows {
58	for _, row := range rows {
59		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
59		fmt.Fprintf(output, "%-7s %-9s %s\n", row.Ring, orNull(row.Nickname), orNull(row.Place))
60	}
60	}
61
61
62	summaries, err := queries.SummariseGulls(ctx)
62	summaries, err := queries.SummariseGulls(ctx)
63	if err != nil {
63	if err != nil {
64		return fmt.Errorf("summarising gulls: %w", err)
64		return fmt.Errorf("summarising gulls: %w", err)
65	}
65	}
66	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
66	fmt.Fprintln(output, "\nring    known as     times seen  last seen")
67	for _, summary := range summaries {
67	for _, summary := range summaries {
68		lastSeen := "NULL"
68		lastSeen := "NULL"
69		if summary.LastSeen != nil {
69		if summary.LastSeen != nil {
70			lastSeen = summary.LastSeen.Format(time.DateOnly)
70			lastSeen = summary.LastSeen.Format(time.DateOnly)
71		}
71		}
72		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
72		fmt.Fprintf(output, "%-7s %-12s %-11d %s\n", summary.Ring, summary.KnownAs, summary.TimesSeen, lastSeen)
73	}
73	}
74	return nil
74	return nil
75}
75}
76
76
77// day returns a date in May 2026, the month the sightings were made.
77// day returns midnight UTC on a date, the value a DATE column holds.
78//
78//
79// Takes dayOfMonth (int) which is the day.
79// Takes year (int) which is the year.
80// Takes month (time.Month) which is the month.
81// Takes date (int) which is the day of the month.
80//
82//
81// Returns time.Time which is midnight UTC on that day.
83// Returns time.Time which is the start of that day.
82func day(dayOfMonth int) time.Time {
84func day(year int, month time.Month, date int) time.Time {
83	return time.Date(2026, time.May, dayOfMonth, 0, 0, 0, 0, time.UTC)
85	return time.Date(year, month, date, 0, 0, 0, 0, time.UTC)
84}
86}
85
87
86// orNull returns the text a nullable field holds, or NULL when the field is nil.
88// orNull returns the text a nullable field holds, or NULL when the field is nil.
87//
89//
88// Takes value (*string) which is a nullable field.
90// Takes value (*string) which is a nullable field.
89//
91//
90// Returns string which is the text to print.
92// Returns string which is the text to print.
91func orNull(value *string) string {
93func orNull(value *string) string {
92	if value == nil {
94	if value == nil {
93		return "NULL"
95		return "NULL"
94	}
96	}
95	return *value
97	return *value
96}
98}