modules/sql-engine/src/integrationTest/sql/group1/subquery/test_neumann.test

# name: test/sql/subquery/test_neumann.test
# description: Test subqueries from the paper 'Unnesting Arbitrary Subqueries'
# group: [subquery]

statement ok
PRAGMA enable_verification

statement ok
CREATE TABLE students(id INTEGER, name VARCHAR, major VARCHAR, year_c INTEGER)

statement ok
CREATE TABLE exams(sid INTEGER, course VARCHAR, curriculum VARCHAR, grade INTEGER, year_c INTEGER)

statement ok
INSERT INTO students VALUES (1, 'Mark', 'CS', 2017)

statement ok
INSERT INTO students VALUES (2, 'Dirk', 'CS', 2017)

statement ok
INSERT INTO exams VALUES (1, 'Database Systems', 'CS', 10, 2015)

statement ok
INSERT INTO exams VALUES (1, 'Graphics', 'CS', 9, 2016)

statement ok
INSERT INTO exams VALUES (2, 'Database Systems', 'CS', 7, 2015)

statement ok
INSERT INTO exams VALUES (2, 'Graphics', 'CS', 7, 2016)

query TTI
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;
----
Dirk	Database Systems	7
Dirk	Graphics	7
Mark	Database Systems	10

query TTI
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_c>=e2.year_c)) ORDER BY name, course;
----
Dirk	Database Systems	7
Dirk	Graphics	7

query TT
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
----
Dirk	CS
Mark	CS