Compare · 012

Validated ORDER BY: ClickHouse and PostgreSQL

Every file of the ClickHouse and PostgreSQL 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
PostgreSQL
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: regatta
4 - name: regatta
5 dialect: clickhouse
5 dialect: postgres
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
11 driver: database/sql

Migrations

db/migrations/0001_entries.up.sqldiffers
ClickHouse
PostgreSQL
1CREATE TABLE entries (
1CREATE TABLE entries (
2 id UInt64,
2 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3 boat String,
3 boat TEXT NOT NULL,
4 crew UInt8,
4 crew BIGINT NOT NULL,
5 finish_seconds UInt32
5 finish_seconds BIGINT NOT NULL
6) ENGINE = MergeTree() ORDER BY id;
6);
db/migrations/0001_entries.down.sqlidentical
ClickHouse
PostgreSQL
1DROP TABLE entries;
1DROP TABLE entries;

Queries

EnterBoatdiffers
ClickHouse · db/queries/entries.sql
PostgreSQL · db/queries/entries.sql
1-- sgl.query(EnterBoat, exec)
1-- sgl.query(EnterBoat, exec)
2INSERT INTO entries (id, boat, crew, finish_seconds)
2INSERT INTO entries (boat, crew, finish_seconds) VALUES ($1, $2, $3);
3VALUES ({id:UInt64}, {boat:String}, {crew:UInt8}, {finish_seconds:UInt32});
ListEntriesidentical
ClickHouse · db/queries/entries.sql
PostgreSQL · db/queries/entries.sql
1-- sgl.query(ListEntries, many)
1-- sgl.query(ListEntries, many)
2-- sgl.sortable(order_by, [boat, crew, finish_seconds])
2-- sgl.sortable(order_by, [boat, crew, finish_seconds])
3SELECT id, boat, crew, finish_seconds FROM entries
3SELECT id, boat, crew, finish_seconds FROM entries

What sgl generates

EnterBoatdiffers
ClickHouse
PostgreSQL
1func (queries *Queries) EnterBoat(ctx context.Context, params EnterBoatParams) error
1func (queries *Queries) EnterBoat(ctx context.Context, params EnterBoatParams) error
2
2
3type EnterBoatParams struct {
3type EnterBoatParams struct {
4 ID uint64
5 Boat string
4 Boat string
6 Crew uint8
5 Crew int64
7 FinishSeconds uint32
6 FinishSeconds int64
8}
7}
ListEntriesdiffers
ClickHouse
PostgreSQL
1func (queries *Queries) ListEntries(ctx context.Context, params ListEntriesParams) ([]ListEntriesRow, error)
1func (queries *Queries) ListEntries(ctx context.Context, params ListEntriesParams) ([]ListEntriesRow, error)
2
2
3type ListEntriesParams struct {
3type ListEntriesParams struct {
4 OrderBy ListEntriesOrderBy
4 OrderBy ListEntriesOrderBy
5 OrderByDirection OrderDirection
5 OrderByDirection OrderDirection
6}
6}
7
7
8type ListEntriesRow struct {
8type ListEntriesRow struct {
9 ID uint64 `json:"id"`
9 ID int64 `json:"id"`
10 Boat string `json:"boat"`
10 Boat string `json:"boat"`
11 Crew uint8 `json:"crew"`
11 Crew int64 `json:"crew"`
12 FinishSeconds uint32 `json:"finish_seconds"`
12 FinishSeconds int64 `json:"finish_seconds"`
13}
13}
14
14
15type ListEntriesOrderBy string
15type ListEntriesOrderBy string
16
16
17type OrderDirection string
17type OrderDirection string

The program

example.godiffers
ClickHouse
PostgreSQL
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/clickhouse/012_sortable/internal/store"
10 "seagull.sh/seagull/examples/postgres/012_sortable/internal/store"
11)
11)
12
12
13// run lists a regatta's entries in four orders. sgl.sortable(order_by, [boat, crew,
13// run lists a regatta's entries in four orders. sgl.sortable(order_by, [boat, crew,
14// finish_seconds]) has no placeholder: it gives ListEntriesParams an OrderBy field, typed
14// finish_seconds]) has no placeholder: it gives ListEntriesParams an OrderBy field, typed
15// with one constant per allowed column, and an OrderByDirection. The method appends ORDER
15// with one constant per allowed column, and an OrderByDirection. The method appends ORDER
16// BY only when OrderBy is one of the allowed columns, so a value that is not, such as raw
16// BY only when OrderBy is one of the allowed columns, so a value that is not, such as raw
17// text from a request, adds nothing to the SQL. A sortable column the query does not
17// text from a request, adds nothing to the SQL and the rows come back in whatever order
18// select makes sgl vet report Q011.
18// the server reads them, here the order they were entered. A sortable column the query
19//
19// does not select makes sgl vet report Q011.
20// Without ORDER BY, ClickHouse returns rows in whatever order its parallel reads finish,
21// so the last listing prints only how many entries came back.
22//
20//
23// Takes database (*sql.DB) which is a migrated database.
21// Takes database (*sql.DB) which is a migrated database.
24// Takes output (io.Writer) which receives each listing.
22// Takes output (io.Writer) which receives each listing.
25//
23//
26// Returns error when a query fails.
24// Returns error when a query fails.
27func run(ctx context.Context, database *sql.DB, output io.Writer) error {
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
28 queries := store.New(database)
26 queries := store.New(database)
29
27
30 entries := []store.EnterBoatParams{
28 entries := []store.EnterBoatParams{
31 {ID: 1, Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},
29 {Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},
32 {ID: 2, Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},
30 {Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},
33 {ID: 3, Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},
31 {Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},
34 {ID: 4, Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},
32 {Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},
35 }
33 }
36 for _, entry := range entries {
34 for _, entry := range entries {
37 if err := queries.EnterBoat(ctx, entry); err != nil {
35 if err := queries.EnterBoat(ctx, entry); err != nil {
38 return fmt.Errorf("entering %s: %w", entry.Boat, err)
36 return fmt.Errorf("entering %s: %w", entry.Boat, err)
39 }
37 }
40 }
38 }
41
39
42 orders := []struct {
40 orders := []struct {
43 label string
41 label string
44 parameters store.ListEntriesParams
42 parameters store.ListEntriesParams
45 }{
43 }{
46 {label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},
44 {label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},
47 {label: "by boat", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByBoat, OrderByDirection: store.OrderAsc}},
45 {label: "by boat", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByBoat, OrderByDirection: store.OrderAsc}},
48 {label: "largest crew first", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByCrew, OrderByDirection: store.OrderDesc}},
46 {label: "largest crew first", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByCrew, OrderByDirection: store.OrderDesc}},
47 {label: "raw text, ignored", parameters: store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc}},
49 }
48 }
50 for _, order := range orders {
49 for _, order := range orders {
51 listed, err := queries.ListEntries(ctx, order.parameters)
50 listed, err := queries.ListEntries(ctx, order.parameters)
52 if err != nil {
51 if err != nil {
53 return fmt.Errorf("listing entries %s: %w", order.label, err)
52 return fmt.Errorf("listing entries %s: %w", order.label, err)
54 }
53 }
55 fmt.Fprintf(output, "%s:\n", order.label)
54 fmt.Fprintf(output, "%s:\n", order.label)
56 for _, entry := range listed {
55 for _, entry := range listed {
57 finish := time.Duration(entry.FinishSeconds) * time.Second
56 finish := time.Duration(entry.FinishSeconds) * time.Second
58 fmt.Fprintf(output, " %-10s crew %d %s\n", entry.Boat, entry.Crew, finish)
57 fmt.Fprintf(output, " %-10s crew %d %s\n", entry.Boat, entry.Crew, finish)
59 }
58 }
60 }
59 }
61
62 unsorted, err := queries.ListEntries(ctx, store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc})
63 if err != nil {
64 return fmt.Errorf("listing entries with raw text: %w", err)
65 }
66 fmt.Fprintf(output, "raw text, ignored: %d entries, in no set order\n", len(unsorted))
67 return nil
60 return nil
68}
61}

Go type differences

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

QueryFieldClickHousePostgreSQL
EnterBoatparameter crewuint8int64
EnterBoatparameter finish_secondsuint32int64
ListEntriescolumn iduint64int64
ListEntriescolumn crewuint8int64
ListEntriescolumn finish_secondsuint32int64