-- Tests join edge cases and special scenarios
CREATE TABLE empty_table("id" INTEGER, "value" VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE single_row("id" INTEGER, "data" VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE duplicate_keys("id" INTEGER, "description" VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO single_row VALUES (1, 'only_row', 1000);

Affected Rows: 1

INSERT INTO duplicate_keys VALUES (1, 'first', 1000), (1, 'second', 2000), (2, 'third', 3000), (2, 'fourth', 4000);

Affected Rows: 4

-- Join with empty table
SELECT s."id", s."data", e."value" FROM single_row s LEFT JOIN empty_table e ON s."id" = e."id" ORDER BY s."id";

+----+----------+-------+
| id | data     | value |
+----+----------+-------+
| 1  | only_row |       |
+----+----------+-------+

-- Join with duplicate keys
SELECT s."id", s."data", d."description" FROM single_row s LEFT JOIN duplicate_keys d ON s."id" = d."id" ORDER BY d."description";

+----+----------+-------------+
| id | data     | description |
+----+----------+-------------+
| 1  | only_row | first       |
| 1  | only_row | second      |
+----+----------+-------------+

-- Self-join with duplicates
SELECT d1."description" as desc1, d2."description" as desc2 FROM duplicate_keys d1 INNER JOIN duplicate_keys d2 ON d1."id" = d2."id" AND d1.ts < d2.ts ORDER BY d1.ts, d2.ts;

+-------+--------+
| desc1 | desc2  |
+-------+--------+
| first | second |
| third | fourth |
+-------+--------+

DROP TABLE empty_table;

Affected Rows: 0

DROP TABLE single_row;

Affected Rows: 0

DROP TABLE duplicate_keys;

Affected Rows: 0