Compare · 012

Validated ORDER BY: MariaDB and TimescaleDB

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

Compare MariaDB withSQLite PostgreSQL CockroachDB TimescaleDB MySQL DuckDB ClickHouse

Configuration

sgl.yamldiffers

MariaDB
TimescaleDB
1version: 1
1version: 1
2
2
3databases:
3databases:
4  - name: regatta
4  - name: regatta
5    dialect: mysql
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

MariaDB
TimescaleDB
1CREATE TABLE entries (
1CREATE TABLE entries (
2    id             BIGINT      NOT NULL AUTO_INCREMENT PRIMARY KEY,
2    id             BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
3    boat           VARCHAR(50) NOT NULL,
3    boat           TEXT   NOT NULL,
4    crew           INT         NOT NULL,
4    crew           BIGINT NOT NULL,
5    finish_seconds INT         NOT NULL
5    finish_seconds BIGINT NOT NULL
6);
6);

db/migrations/0001_entries.down.sqlidentical

MariaDB
TimescaleDB
1DROP TABLE entries;
1DROP TABLE entries;

Queries

EnterBoatdiffers

MariaDB · db/queries/entries.sql
TimescaleDB · db/queries/entries.sql
1-- sgl.query(EnterBoat, exec)
1-- sgl.query(EnterBoat, exec)
2INSERT INTO entries (boat, crew, finish_seconds) VALUES (?, ?, ?);
2INSERT INTO entries (boat, crew, finish_seconds) VALUES ($1, $2, $3);

ListEntriesidentical

MariaDB · db/queries/entries.sql
TimescaleDB · 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

MariaDB
TimescaleDB
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	Boat          string
4	Boat          string
5	Crew          int32
5	Crew          int64
6	FinishSeconds int32
6	FinishSeconds int64
7}
7}

ListEntriesdiffers

MariaDB
TimescaleDB
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            int64  `json:"id"`
9	ID            int64  `json:"id"`
10	Boat          string `json:"boat"`
10	Boat          string `json:"boat"`
11	Crew          int32  `json:"crew"`
11	Crew          int64  `json:"crew"`
12	FinishSeconds int32  `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

MariaDB
TimescaleDB
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/mysql/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 and the rows come back unsorted. A
17// text from a request, adds nothing to the SQL and the rows come back in whatever order
18// sortable column the query does not select makes sgl vet report Q011.
18// the server reads them, here the order they were entered. A sortable column the query
19// does not select makes sgl vet report Q011.
19//
20//
20// Takes database (*sql.DB) which is a migrated database.
21// Takes database (*sql.DB) which is a migrated database.
21// Takes output (io.Writer) which receives each listing.
22// Takes output (io.Writer) which receives each listing.
22//
23//
23// Returns error when a query fails.
24// Returns error when a query fails.
24func run(ctx context.Context, database *sql.DB, output io.Writer) error {
25func run(ctx context.Context, database *sql.DB, output io.Writer) error {
25	queries := store.New(database)
26	queries := store.New(database)
26
27
27	entries := []store.EnterBoatParams{
28	entries := []store.EnterBoatParams{
28		{Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},
29		{Boat: "Kittiwake", Crew: 4, FinishSeconds: 3725},
29		{Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},
30		{Boat: "Salty Chip", Crew: 3, FinishSeconds: 3410},
30		{Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},
31		{Boat: "Gull Wing", Crew: 5, FinishSeconds: 3990},
31		{Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},
32		{Boat: "Driftwood", Crew: 2, FinishSeconds: 3588},
32	}
33	}
33	for _, entry := range entries {
34	for _, entry := range entries {
34		if err := queries.EnterBoat(ctx, entry); err != nil {
35		if err := queries.EnterBoat(ctx, entry); err != nil {
35			return fmt.Errorf("entering %s: %w", entry.Boat, err)
36			return fmt.Errorf("entering %s: %w", entry.Boat, err)
36		}
37		}
37	}
38	}
38
39
39	orders := []struct {
40	orders := []struct {
40		label      string
41		label      string
41		parameters store.ListEntriesParams
42		parameters store.ListEntriesParams
42	}{
43	}{
43		{label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},
44		{label: "results", parameters: store.ListEntriesParams{OrderBy: store.ListEntriesOrderByFinishSeconds, OrderByDirection: store.OrderAsc}},
44		{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}},
45		{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}},
46		{label: "raw text, ignored", parameters: store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc}},
47		{label: "raw text, ignored", parameters: store.ListEntriesParams{OrderBy: "boat; DROP TABLE entries", OrderByDirection: store.OrderAsc}},
47	}
48	}
48	for _, order := range orders {
49	for _, order := range orders {
49		listed, err := queries.ListEntries(ctx, order.parameters)
50		listed, err := queries.ListEntries(ctx, order.parameters)
50		if err != nil {
51		if err != nil {
51			return fmt.Errorf("listing entries %s: %w", order.label, err)
52			return fmt.Errorf("listing entries %s: %w", order.label, err)
52		}
53		}
53		fmt.Fprintf(output, "%s:\n", order.label)
54		fmt.Fprintf(output, "%s:\n", order.label)
54		for _, entry := range listed {
55		for _, entry := range listed {
55			finish := time.Duration(entry.FinishSeconds) * time.Second
56			finish := time.Duration(entry.FinishSeconds) * time.Second
56			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)
57		}
58		}
58	}
59	}
59	return nil
60	return nil
60}
61}

Go type differences

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

Query	Field	MariaDB	TimescaleDB
EnterBoat	parameter `crew`	`int32`	`int64`
EnterBoat	parameter `finish_seconds`	`int32`	`int64`
ListEntries	column `crew`	`int32`	`int64`
ListEntries	column `finish_seconds`	`int32`	`int64`