Compare · 010

IN lists from Go slices: ClickHouse and MariaDB

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

Compare ClickHouse withSQLitePostgreSQLCockroachDBTimescaleDBMySQLMariaDBDuckDB

Configuration

sgl.yamldiffers
ClickHouse
MariaDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4 - name: huts
4 - name: huts
5 dialect: clickhouse
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_huts.up.sqldiffers
ClickHouse
MariaDB
1CREATE TABLE huts (
1CREATE TABLE huts (
2 number UInt64,
2 number BIGINT NOT NULL PRIMARY KEY,
3 beach String,
3 beach VARCHAR(50) NOT NULL,
4 colour String,
4 colour VARCHAR(20) NOT NULL,
5 booked Bool DEFAULT false
5 booked BOOLEAN NOT NULL DEFAULT FALSE
6) ENGINE = MergeTree() ORDER BY number;
6);
db/migrations/0001_huts.down.sqlidentical
ClickHouse
MariaDB
1DROP TABLE huts;
1DROP TABLE huts;

Queries

AddHutdiffers
ClickHouse · db/queries/huts.sql
MariaDB · db/queries/huts.sql
1-- sgl.query(AddHut, exec)
1-- sgl.query(AddHut, exec)
2INSERT INTO huts (number, beach, colour) VALUES ({number:UInt64}, {beach:String}, {colour:String});
2INSERT INTO huts (number, beach, colour) VALUES (?, ?, ?);
HutsOnBeachesdiffers
ClickHouse · db/queries/huts.sql
MariaDB · db/queries/huts.sql
1-- sgl.query(HutsOnBeaches, many)
1-- sgl.query(HutsOnBeaches, many)
2-- ?1 as sgl.param(beaches, kind: slice)
2SELECT number, beach, colour, booked
3SELECT number, beach, colour, booked
3FROM huts
4FROM huts
4WHERE beach IN {beaches:Array(String)}
5WHERE beach IN (?1)
5ORDER BY number;
6ORDER BY number;
BookHutsdiffers
ClickHouse · db/queries/huts.sql
MariaDB · db/queries/huts.sql
1-- sgl.query(BookHuts, asyncexec)
1-- sgl.query(BookHuts, execrows)
2ALTER TABLE huts UPDATE booked = true WHERE number IN {numbers:Array(UInt64)} AND NOT booked;
2-- ?1 as sgl.param(numbers, kind: slice)
3UPDATE huts SET booked = TRUE WHERE number IN (?1) AND booked = FALSE;
CountBookedonly ClickHouse
ClickHouse · db/queries/huts.sql
MariaDB · not in this version
1-- sgl.query(CountBooked, one)
2SELECT count() AS huts FROM huts WHERE booked;
FreeHutsInColoursdiffers
ClickHouse · db/queries/huts.sql
MariaDB · db/queries/huts.sql
1-- sgl.query(FreeHutsInColours, many)
1-- sgl.query(FreeHutsInColours, many)
2-- ?1 as sgl.param(beach)
3-- ?2 as sgl.param(colours, kind: slice)
2SELECT number, colour
4SELECT number, colour
3FROM huts
5FROM huts
4WHERE beach = {beach:String} AND colour IN {colours:Array(String)} AND NOT booked
6WHERE beach = ?1 AND colour IN (?2) AND booked = FALSE
5ORDER BY number;
7ORDER BY number;

What sgl generates

AddHutdiffers
ClickHouse
MariaDB
1func (queries *Queries) AddHut(ctx context.Context, params AddHutParams) error
1func (queries *Queries) AddHut(ctx context.Context, params AddHutParams) error
2
2
3type AddHutParams struct {
3type AddHutParams struct {
4 Number uint64
4 Number int64
5 Beach string
5 Beach string
6 Colour string
6 Colour string
7}
7}
HutsOnBeachesdiffers
ClickHouse
MariaDB
1func (queries *Queries) HutsOnBeaches(ctx context.Context, beaches []string) ([]HutsOnBeachesRow, error)
1func (queries *Queries) HutsOnBeaches(ctx context.Context, params HutsOnBeachesParams) ([]HutsOnBeachesRow, error)
2
2
3type HutsOnBeachesParams struct {
4 Beaches []string
5}
6
3type HutsOnBeachesRow struct {
7type HutsOnBeachesRow struct {
4 Number uint64 `json:"number"`
8 Number int64 `json:"number"`
5 Beach string `json:"beach"`
9 Beach string `json:"beach"`
6 Colour string `json:"colour"`
10 Colour string `json:"colour"`
7 Booked bool `json:"booked"`
11 Booked bool `json:"booked"`
8}
12}
BookHutsdiffers
ClickHouse
MariaDB
1func (queries *Queries) BookHuts(ctx context.Context, numbers []uint64) error
1func (queries *Queries) BookHuts(ctx context.Context, params BookHutsParams) (int64, error)
2
3type BookHutsParams struct {
4 Numbers []int64
5}
CountBookedonly ClickHouse
ClickHouse
MariaDB · not in this version
1func (queries *Queries) CountBooked(ctx context.Context) (CountBookedRow, error)
2
3type CountBookedRow struct {
4 Huts uint64 `json:"huts"`
5}
FreeHutsInColoursdiffers
ClickHouse
MariaDB
1func (queries *Queries) FreeHutsInColours(ctx context.Context, params FreeHutsInColoursParams) ([]FreeHutsInColoursRow, error)
1func (queries *Queries) FreeHutsInColours(ctx context.Context, params FreeHutsInColoursParams) ([]FreeHutsInColoursRow, error)
2
2
3type FreeHutsInColoursParams struct {
3type FreeHutsInColoursParams struct {
4 Beach string
4 Beach string
5 Colours []string
5 Colours []string
6}
6}
7
7
8type FreeHutsInColoursRow struct {
8type FreeHutsInColoursRow struct {
9 Number uint64 `json:"number"`
9 Number int64 `json:"number"`
10 Colour string `json:"colour"`
10 Colour string `json:"colour"`
11}
11}

The program

example.godiffers
ClickHouse
MariaDB
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
8
9 "seagull.sh/seagull/examples/clickhouse/010_slices/internal/store"
9 "seagull.sh/seagull/examples/mysql/010_slices/internal/store"
10)
10)
11
11
12// run lets out beach huts by passing Go slices where the SQL has an IN list. ClickHouse
12// run lets out beach huts by passing Go slices where the SQL has an IN list. A binding
13// takes a whole array as one parameter, so the query says IN {beaches:Array(String)} and
13// such as ?1 as sgl.param(beaches, kind: slice) makes the parameter a slice, and the
14// the method takes a []string; no kind: slice binding is needed and the SQL is not
14// generated method rewrites IN (?1) to one ? per element at call time; the generated SQL
15// rewritten at call time. The slice is sent as one array value however long it is, and an
15// already writes every other ?N as a plain ?, the only placeholder MySQL takes. An empty
16// empty slice is an empty array, which matches nothing.
16// slice turns IN (?1) into IN (SELECT NULL FROM DUAL WHERE FALSE), which matches nothing.
17//
18// Other engines expand IN (?1) into one placeholder per element, which is why sgl vet
19// refuses a kind: slice parameter on a batch query (Q017) or a dynamic: runtime query
20// (Q018); an Array parameter is a single value, so neither applies here.
21//
17//
22// ALTER TABLE ... UPDATE reports no changed rows, so BookHuts is asyncexec and the
18// The rewrite happens before the statement runs, so sgl vet refuses a slice parameter on
23// example counts the booked huts before and after each booking.
19// a batch or copyfrom query (Q017) or a dynamic: runtime query (Q018).
24//
20//
25// Takes database (*sql.DB) which is a migrated database.
21// Takes database (*sql.DB) which is a migrated database.
26// Takes output (io.Writer) which receives each search and booking.
22// Takes output (io.Writer) which receives each search and booking.
27//
23//
28// Returns error when a query fails.
24// Returns error when a query fails.
29func run(ctx context.Context, database *sql.DB, output io.Writer) error {
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
30 queries := store.New(database)
26 queries := store.New(database)
31
27
32 huts := []store.AddHutParams{
28 huts := []store.AddHutParams{
33 {Number: 1, Beach: "Polzeath", Colour: "blue"},
29 {Number: 1, Beach: "Polzeath", Colour: "blue"},
34 {Number: 2, Beach: "Polzeath", Colour: "yellow"},
30 {Number: 2, Beach: "Polzeath", Colour: "yellow"},
35 {Number: 3, Beach: "Polzeath", Colour: "red"},
31 {Number: 3, Beach: "Polzeath", Colour: "red"},
36 {Number: 4, Beach: "Watergate", Colour: "blue"},
32 {Number: 4, Beach: "Watergate", Colour: "blue"},
37 {Number: 5, Beach: "Watergate", Colour: "red"},
33 {Number: 5, Beach: "Watergate", Colour: "red"},
38 {Number: 6, Beach: "Fistral", Colour: "yellow"},
34 {Number: 6, Beach: "Fistral", Colour: "yellow"},
39 }
35 }
40 for _, hut := range huts {
36 for _, hut := range huts {
41 if err := queries.AddHut(ctx, hut); err != nil {
37 if err := queries.AddHut(ctx, hut); err != nil {
42 return fmt.Errorf("adding hut %d: %w", hut.Number, err)
38 return fmt.Errorf("adding hut %d: %w", hut.Number, err)
43 }
39 }
44 }
40 }
45
41
46 for _, beaches := range [][]string{{"Polzeath", "Fistral"}, {"Watergate"}, {}} {
42 for _, beaches := range [][]string{{"Polzeath", "Fistral"}, {"Watergate"}, {}} {
47 found, err := queries.HutsOnBeaches(ctx, beaches)
43 found, err := queries.HutsOnBeaches(ctx, store.HutsOnBeachesParams{Beaches: beaches})
48 if err != nil {
44 if err != nil {
49 return fmt.Errorf("finding huts on %v: %w", beaches, err)
45 return fmt.Errorf("finding huts on %v: %w", beaches, err)
50 }
46 }
51 numbers := make([]uint64, 0, len(found))
47 numbers := make([]int64, 0, len(found))
52 for _, hut := range found {
48 for _, hut := range found {
53 numbers = append(numbers, hut.Number)
49 numbers = append(numbers, hut.Number)
54 }
50 }
55 fmt.Fprintf(output, "huts on %v: %v\n", beaches, numbers)
51 fmt.Fprintf(output, "huts on %v: %v\n", beaches, numbers)
56 }
52 }
57
53
58 // Hut 3 is taken by the first booking, so the second books only hut 5.
54 // Hut 3 is taken by the first booking, so the second books only hut 5.
59 for _, numbers := range [][]uint64{{1, 3, 4}, {3, 5}} {
55 for _, numbers := range [][]int64{{1, 3, 4}, {3, 5}} {
60 booked, err := bookHuts(ctx, queries, numbers)
56 booked, err := queries.BookHuts(ctx, store.BookHutsParams{Numbers: numbers})
61 if err != nil {
57 if err != nil {
62 return err
58 return fmt.Errorf("booking huts %v: %w", numbers, err)
63 }
59 }
64 fmt.Fprintf(output, "booking huts %v booked %d\n", numbers, booked)
60 fmt.Fprintf(output, "booking huts %v booked %d\n", numbers, booked)
65 }
61 }
66
62
67 free, err := queries.FreeHutsInColours(ctx, store.FreeHutsInColoursParams{
63 free, err := queries.FreeHutsInColours(ctx, store.FreeHutsInColoursParams{
68 Beach: "Polzeath",
64 Beach: "Polzeath",
69 Colours: []string{"blue", "yellow"},
65 Colours: []string{"blue", "yellow"},
70 })
66 })
71 if err != nil {
67 if err != nil {
72 return fmt.Errorf("finding free huts: %w", err)
68 return fmt.Errorf("finding free huts: %w", err)
73 }
69 }
74 for _, hut := range free {
70 for _, hut := range free {
75 fmt.Fprintf(output, "hut %d at Polzeath is %s and free\n", hut.Number, hut.Colour)
71 fmt.Fprintf(output, "hut %d at Polzeath is %s and free\n", hut.Number, hut.Colour)
76 }
72 }
77 return nil
73 return nil
78}
74}
79
80// bookHuts books the huts that are free among numbers and returns how many it booked, by
81// counting the booked huts before and after. The connection sets mutations_sync=2, so the
82// mutation has finished when BookHuts returns.
83//
84// Takes queries (*store.Queries) which runs the queries.
85// Takes numbers ([]uint64) which are the huts to book.
86//
87// Returns uint64 which is how many huts were booked.
88// Returns error when a query fails.
89func bookHuts(ctx context.Context, queries *store.Queries, numbers []uint64) (uint64, error) {
90 before, err := queries.CountBooked(ctx)
91 if err != nil {
92 return 0, fmt.Errorf("counting booked huts: %w", err)
93 }
94 if err := queries.BookHuts(ctx, numbers); err != nil {
95 return 0, fmt.Errorf("booking huts %v: %w", numbers, err)
96 }
97 after, err := queries.CountBooked(ctx)
98 if err != nil {
99 return 0, fmt.Errorf("counting booked huts: %w", err)
100 }
101 return after.Huts - before.Huts, nil
102}

Go type differences

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

QueryFieldClickHouseMariaDB
AddHutparameter numberuint64int64
HutsOnBeachescolumn numberuint64int64
BookHutsparameter numbers[]uint64[]int64
FreeHutsInColourscolumn numberuint64int64