mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-15 12:30:38 +00:00
6c066c1a4a
* test: migrate join tests Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * chore: update test results after rebasing main branch Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: unstable query sort results and natural_join test Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: count(*) with joining Signed-off-by: Dennis Zhuang <killme2008@gmail.com> * fix: unstable query sort results and style Signed-off-by: Dennis Zhuang <killme2008@gmail.com> --------- Signed-off-by: Dennis Zhuang <killme2008@gmail.com>
68 lines
2.6 KiB
Plaintext
68 lines
2.6 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/join/left_outer/test_left_outer.test
|
|
-- Tests LEFT OUTER JOIN functionality
|
|
CREATE TABLE left_t (a INTEGER, b INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE right_t (a INTEGER, c INTEGER, ts TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO left_t VALUES (1, 10, 1000), (2, 20, 2000), (3, 30, 3000);
|
|
|
|
Affected Rows: 3
|
|
|
|
INSERT INTO right_t VALUES (1, 100, 4000), (2, 200, 5000), (4, 400, 6000);
|
|
|
|
Affected Rows: 3
|
|
|
|
-- Basic LEFT JOIN
|
|
SELECT * FROM left_t LEFT JOIN right_t ON left_t.a = right_t.a ORDER BY left_t.a;
|
|
|
|
+---+----+---------------------+---+-----+---------------------+
|
|
| a | b | ts | a | c | ts |
|
|
+---+----+---------------------+---+-----+---------------------+
|
|
| 1 | 10 | 1970-01-01T00:00:01 | 1 | 100 | 1970-01-01T00:00:04 |
|
|
| 2 | 20 | 1970-01-01T00:00:02 | 2 | 200 | 1970-01-01T00:00:05 |
|
|
| 3 | 30 | 1970-01-01T00:00:03 | | | |
|
|
+---+----+---------------------+---+-----+---------------------+
|
|
|
|
-- LEFT JOIN with WHERE on left table
|
|
SELECT * FROM left_t LEFT JOIN right_t ON left_t.a = right_t.a WHERE left_t.b > 15 ORDER BY left_t.a;
|
|
|
|
+---+----+---------------------+---+-----+---------------------+
|
|
| a | b | ts | a | c | ts |
|
|
+---+----+---------------------+---+-----+---------------------+
|
|
| 2 | 20 | 1970-01-01T00:00:02 | 2 | 200 | 1970-01-01T00:00:05 |
|
|
| 3 | 30 | 1970-01-01T00:00:03 | | | |
|
|
+---+----+---------------------+---+-----+---------------------+
|
|
|
|
-- LEFT JOIN with WHERE on joined result
|
|
SELECT * FROM left_t LEFT JOIN right_t ON left_t.a = right_t.a WHERE right_t.c IS NULL ORDER BY left_t.a;
|
|
|
|
+---+----+---------------------+---+---+----+
|
|
| a | b | ts | a | c | ts |
|
|
+---+----+---------------------+---+---+----+
|
|
| 3 | 30 | 1970-01-01T00:00:03 | | | |
|
|
+---+----+---------------------+---+---+----+
|
|
|
|
-- LEFT JOIN with complex condition
|
|
SELECT * FROM left_t LEFT JOIN right_t ON left_t.a = right_t.a AND left_t.b < 25 ORDER BY left_t.a;
|
|
|
|
+---+----+---------------------+---+-----+---------------------+
|
|
| a | b | ts | a | c | ts |
|
|
+---+----+---------------------+---+-----+---------------------+
|
|
| 1 | 10 | 1970-01-01T00:00:01 | 1 | 100 | 1970-01-01T00:00:04 |
|
|
| 2 | 20 | 1970-01-01T00:00:02 | 2 | 200 | 1970-01-01T00:00:05 |
|
|
| 3 | 30 | 1970-01-01T00:00:03 | | | |
|
|
+---+----+---------------------+---+-----+---------------------+
|
|
|
|
DROP TABLE right_t;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE left_t;
|
|
|
|
Affected Rows: 0
|
|
|