-- Migrated from DuckDB test: test/sql/join/inner/test_range_join.test
-- Tests inequality JOIN conditions
CREATE TABLE events("id" INTEGER, event_time INTEGER, duration INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE time_ranges(start_time INTEGER, end_time INTEGER, range_name VARCHAR, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO events VALUES (1, 10, 5, 1000), (2, 25, 3, 2000), (3, 45, 8, 3000);

Affected Rows: 3

INSERT INTO time_ranges VALUES (0, 20, 'Early', 4000), (20, 40, 'Mid', 5000), (40, 60, 'Late', 6000);

Affected Rows: 3

-- Range join using BETWEEN
SELECT e."id", e.event_time, t.range_name
FROM events e JOIN time_ranges t ON e.event_time BETWEEN t.start_time AND t.end_time
ORDER BY e."id";

+----+------------+------------+
| id | event_time | range_name |
+----+------------+------------+
| 1  | 10         | Early      |
| 2  | 25         | Mid        |
| 3  | 45         | Late       |
+----+------------+------------+

-- Inequality join conditions
SELECT e."id", e.event_time, e.duration, t.range_name
FROM events e JOIN time_ranges t ON e.event_time >= t.start_time AND e.event_time < t.end_time
ORDER BY e."id";

+----+------------+----------+------------+
| id | event_time | duration | range_name |
+----+------------+----------+------------+
| 1  | 10         | 5        | Early      |
| 2  | 25         | 3        | Mid        |
| 3  | 45         | 8        | Late       |
+----+------------+----------+------------+

-- Join with overlap condition
SELECT e."id", t.range_name
FROM events e JOIN time_ranges t ON
  e.event_time < t.end_time AND (e.event_time + e.duration) > t.start_time
ORDER BY e."id", t.start_time;

+----+------------+
| id | range_name |
+----+------------+
| 1  | Early      |
| 2  | Mid        |
| 3  | Late       |
+----+------------+

-- Self join with inequality
SELECT e1."id" as id1, e2."id" as id2, e1.event_time, e2.event_time
FROM events e1 JOIN events e2 ON e1.event_time < e2.event_time
ORDER BY e1."id", e2."id";

+-----+-----+------------+------------+
| id1 | id2 | event_time | event_time |
+-----+-----+------------+------------+
| 1   | 2   | 10         | 25         |
| 1   | 3   | 10         | 45         |
| 2   | 3   | 25         | 45         |
+-----+-----+------------+------------+

DROP TABLE time_ranges;

Affected Rows: 0

DROP TABLE events;

Affected Rows: 0