mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-01-05 12:52:57 +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>
61 lines
2.7 KiB
Plaintext
61 lines
2.7 KiB
Plaintext
-- Migrated from DuckDB test: test/sql/join/natural/natural_join.test
|
|
-- Tests NATURAL JOIN functionality
|
|
CREATE TABLE emp_natural("id" INTEGER, "name" VARCHAR, dept_id INTEGER, ts1 TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE dept_natural(dept_id INTEGER, dept_name VARCHAR, ts2 TIMESTAMP TIME INDEX);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO emp_natural VALUES (1, 'Alice', 10, 1000), (2, 'Bob', 20, 2000), (3, 'Carol', 10, 3000);
|
|
|
|
Affected Rows: 3
|
|
|
|
INSERT INTO dept_natural VALUES (10, 'Engineering', 4000), (20, 'Sales', 5000), (30, 'Marketing', 6000);
|
|
|
|
Affected Rows: 3
|
|
|
|
-- NATURAL JOIN (joins on common column names)
|
|
SELECT * FROM emp_natural NATURAL JOIN dept_natural ORDER BY "id";
|
|
|
|
+----+-------+---------------------+---------+-------------+---------------------+
|
|
| id | name | ts1 | dept_id | dept_name | ts2 |
|
|
+----+-------+---------------------+---------+-------------+---------------------+
|
|
| 1 | Alice | 1970-01-01T00:00:01 | 10 | Engineering | 1970-01-01T00:00:04 |
|
|
| 2 | Bob | 1970-01-01T00:00:02 | 20 | Sales | 1970-01-01T00:00:05 |
|
|
| 3 | Carol | 1970-01-01T00:00:03 | 10 | Engineering | 1970-01-01T00:00:04 |
|
|
+----+-------+---------------------+---------+-------------+---------------------+
|
|
|
|
-- NATURAL LEFT JOIN
|
|
SELECT * FROM emp_natural NATURAL LEFT JOIN dept_natural ORDER BY "id";
|
|
|
|
+----+-------+---------------------+---------+-------------+---------------------+
|
|
| id | name | ts1 | dept_id | dept_name | ts2 |
|
|
+----+-------+---------------------+---------+-------------+---------------------+
|
|
| 1 | Alice | 1970-01-01T00:00:01 | 10 | Engineering | 1970-01-01T00:00:04 |
|
|
| 2 | Bob | 1970-01-01T00:00:02 | 20 | Sales | 1970-01-01T00:00:05 |
|
|
| 3 | Carol | 1970-01-01T00:00:03 | 10 | Engineering | 1970-01-01T00:00:04 |
|
|
+----+-------+---------------------+---------+-------------+---------------------+
|
|
|
|
-- NATURAL RIGHT JOIN
|
|
SELECT * FROM emp_natural NATURAL RIGHT JOIN dept_natural ORDER BY dept_id;
|
|
|
|
+----+-------+---------------------+---------+-------------+---------------------+
|
|
| id | name | ts1 | dept_id | dept_name | ts2 |
|
|
+----+-------+---------------------+---------+-------------+---------------------+
|
|
| 1 | Alice | 1970-01-01T00:00:01 | 10 | Engineering | 1970-01-01T00:00:04 |
|
|
| 3 | Carol | 1970-01-01T00:00:03 | 10 | Engineering | 1970-01-01T00:00:04 |
|
|
| 2 | Bob | 1970-01-01T00:00:02 | 20 | Sales | 1970-01-01T00:00:05 |
|
|
| | | | 30 | Marketing | 1970-01-01T00:00:06 |
|
|
+----+-------+---------------------+---------+-------------+---------------------+
|
|
|
|
DROP TABLE dept_natural;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE emp_natural;
|
|
|
|
Affected Rows: 0
|
|
|