-- Migrated from DuckDB test: test/sql/join/test_complex_join_expr.test
CREATE TABLE test (a INTEGER, b INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO test VALUES (4, 1, 1000), (2, 2, 2000);

Affected Rows: 2

CREATE TABLE test2 (b INTEGER, c INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO test2 VALUES (1, 2, 3000), (3, 0, 4000);

Affected Rows: 2

-- INNER JOIN with complex expression
SELECT * FROM test JOIN test2 ON test.a+test2.c=test.b+test2.b ORDER BY test.a;

+---+---+---------------------+---+---+---------------------+
| a | b | ts                  | b | c | ts                  |
+---+---+---------------------+---+---+---------------------+
| 4 | 1 | 1970-01-01T00:00:01 | 3 | 0 | 1970-01-01T00:00:04 |
+---+---+---------------------+---+---+---------------------+

-- LEFT JOIN with complex expression
SELECT * FROM test LEFT JOIN test2 ON test.a+test2.c=test.b+test2.b ORDER BY test.a;

+---+---+---------------------+---+---+---------------------+
| a | b | ts                  | b | c | ts                  |
+---+---+---------------------+---+---+---------------------+
| 2 | 2 | 1970-01-01T00:00:02 |   |   |                     |
| 4 | 1 | 1970-01-01T00:00:01 | 3 | 0 | 1970-01-01T00:00:04 |
+---+---+---------------------+---+---+---------------------+

-- RIGHT JOIN with complex expression
SELECT * FROM test RIGHT JOIN test2 ON test.a+test2.c=test.b+test2.b ORDER BY test.a NULLS FIRST;

+---+---+---------------------+---+---+---------------------+
| a | b | ts                  | b | c | ts                  |
+---+---+---------------------+---+---+---------------------+
|   |   |                     | 1 | 2 | 1970-01-01T00:00:03 |
| 4 | 1 | 1970-01-01T00:00:01 | 3 | 0 | 1970-01-01T00:00:04 |
+---+---+---------------------+---+---+---------------------+

-- FULL JOIN with complex expression
SELECT * FROM test FULL OUTER JOIN test2 ON test.a+test2.c=test.b+test2.b ORDER BY test.a NULLS FIRST;

+---+---+---------------------+---+---+---------------------+
| a | b | ts                  | b | c | ts                  |
+---+---+---------------------+---+---+---------------------+
|   |   |                     | 1 | 2 | 1970-01-01T00:00:03 |
| 2 | 2 | 1970-01-01T00:00:02 |   |   |                     |
| 4 | 1 | 1970-01-01T00:00:01 | 3 | 0 | 1970-01-01T00:00:04 |
+---+---+---------------------+---+---+---------------------+

-- Basic equi-join
SELECT * FROM test JOIN test2 ON test.b = test2.b ORDER BY test.a;

+---+---+---------------------+---+---+---------------------+
| a | b | ts                  | b | c | ts                  |
+---+---+---------------------+---+---+---------------------+
| 4 | 1 | 1970-01-01T00:00:01 | 1 | 2 | 1970-01-01T00:00:03 |
+---+---+---------------------+---+---+---------------------+

DROP TABLE test2;

Affected Rows: 0

DROP TABLE test;

Affected Rows: 0