-- Migrated from DuckDB test: test/sql/subquery/test_neumann.test
-- Description: Test subqueries from the paper 'Unnesting Arbitrary Subqueries'
CREATE TABLE students("id" INTEGER, "name" VARCHAR, major VARCHAR, "year" INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

CREATE TABLE exams(sid INTEGER, course VARCHAR, curriculum VARCHAR, grade INTEGER, "year" INTEGER, ts TIMESTAMP TIME INDEX);

Affected Rows: 0

INSERT INTO students VALUES (1, 'Mark', 'CS', 2017, 1000);

Affected Rows: 1

INSERT INTO students VALUES (2, 'Dirk', 'CS', 2017, 2000);

Affected Rows: 1

INSERT INTO exams VALUES (1, 'Database Systems', 'CS', 10, 2015, 3000);

Affected Rows: 1

INSERT INTO exams VALUES (1, 'Graphics', 'CS', 9, 2016, 4000);

Affected Rows: 1

INSERT INTO exams VALUES (2, 'Database Systems', 'CS', 7, 2015, 5000);

Affected Rows: 1

INSERT INTO exams VALUES (2, 'Graphics', 'CS', 7, 2016, 6000);

Affected Rows: 1

-- Test 1: Subquery with MAX aggregation
SELECT s."name", 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 "name", course;

+------+------------------+-------+
| name | course           | grade |
+------+------------------+-------+
| Dirk | Database Systems | 7     |
| Dirk | Graphics         | 7     |
| Mark | Database Systems | 10    |
+------+------------------+-------+

-- Test 2: Complex subquery with AVG and conditions
-- TODO: It raise an error right now: Physical plan does not support logical expression ScalarSubquery(<subquery>)
SELECT s."name", 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."year">=e2."year")) 
ORDER BY "name", course;

Error: 1001(Unsupported), This feature is not implemented: Physical plan does not support logical expression ScalarSubquery(<subquery>)

-- Test 3: EXISTS subquery
SELECT "name", major 
FROM students s 
WHERE EXISTS(SELECT * FROM exams e WHERE e.sid=s."id" AND grade=10) OR s."name"='Dirk' 
ORDER BY "name";

+------+-------+
| name | major |
+------+-------+
| Dirk | CS    |
| Mark | CS    |
+------+-------+

-- Clean up
DROP TABLE exams;

Affected Rows: 0

DROP TABLE students;

Affected Rows: 0