mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-05-17 21:40:37 +00:00
2b72e66536
* test: subquery test migrated from duckdb * test: update test * test: skip unsupported features and add sources
59 lines
1.9 KiB
Plaintext
59 lines
1.9 KiB
Plaintext
-- from:
|
|
-- https://github.com/duckdb/duckdb/blob/74687ec572e9e6ccf34f9b15daa62998b34a3e13/test/sql/subquery/test_neumann.test
|
|
CREATE TABLE students(ts TIMESTAMP TIME INDEX, id INTEGER, n VARCHAR, major VARCHAR, y INTEGER);
|
|
|
|
Affected Rows: 0
|
|
|
|
CREATE TABLE exams(ts TIMESTAMP TIME INDEX, sid INTEGER, course VARCHAR, curriculum VARCHAR, grade INTEGER, y INTEGER);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO students VALUES (1, 1, 'Mark', 'CS', 2017);
|
|
|
|
Affected Rows: 1
|
|
|
|
INSERT INTO students VALUES (2, 2, 'Dirk', 'CS', 2017);
|
|
|
|
Affected Rows: 1
|
|
|
|
INSERT INTO exams VALUES (1, 1, 'Database Systems', 'CS', 10, 2015);
|
|
|
|
Affected Rows: 1
|
|
|
|
INSERT INTO exams VALUES (2, 1, 'Graphics', 'CS', 9, 2016);
|
|
|
|
Affected Rows: 1
|
|
|
|
INSERT INTO exams VALUES (3, 2, 'Database Systems', 'CS', 7, 2015);
|
|
|
|
Affected Rows: 1
|
|
|
|
INSERT INTO exams VALUES (4, 2, 'Graphics', 'CS', 7, 2016);
|
|
|
|
Affected Rows: 1
|
|
|
|
SELECT s.n, e.course, e.grade FROM students s, exams e WHERE s.id=e.sid AND e.grade=(SELECT MAX(e2.grade) FROM exams e2 WHERE s.id=e2.sid) ORDER BY n, course;
|
|
|
|
+------+------------------+-------+
|
|
| n | course | grade |
|
|
+------+------------------+-------+
|
|
| Dirk | Database Systems | 7 |
|
|
| Dirk | Graphics | 7 |
|
|
| Mark | Database Systems | 10 |
|
|
+------+------------------+-------+
|
|
|
|
-- skipped, unsupported feature: correlated column in predicate, see also:
|
|
-- https://github.com/GreptimeTeam/greptimedb/issues/5012
|
|
-- SELECT s.n, e.course, e.grade FROM students s, exams e WHERE s.id=e.sid AND (s.major = 'CS' OR s.major = 'Games Eng') AND e.grade <= (SELECT AVG(e2.grade) - 1 FROM exams e2 WHERE s.id=e2.sid OR (e2.curriculum=s.major AND s.y>=e2.y)) ORDER BY n, course;
|
|
-- skipped, unsupported feature: exists, see also:
|
|
-- https://github.com/GreptimeTeam/greptimedb/issues/5012
|
|
-- SELECT n, major FROM students s WHERE EXISTS(SELECT * FROM exams e WHERE e.sid=s.id AND grade=10) OR s.n='Dirk' ORDER BY n;
|
|
DROP TABLE students;
|
|
|
|
Affected Rows: 0
|
|
|
|
DROP TABLE exams;
|
|
|
|
Affected Rows: 0
|
|
|