search

don-tay / simpledb

Subset of SQL-compliant DBMS
0 stars 0 forks source link

Handle sort plan creation in query planner depending on GROUP BY/DISTINCT #38

Closed don-tay closed 2 years ago

don-tay commented 2 years ago

Changes in this PR

Overview

This fixes following query (in #35):

select dname,count(sid) from student, dept where majorid=did group by dname order by dname desc
 dname countofsid
-----------------
 math         4
drama         6
compsci         7
don-tay commented 2 years ago

In postgres, there are 2 cases for ORDER BY

  1. If there is GROUP BY or DISTINCT then the sort fields must be found in the GROUP BY or DISTINCT field.
  2. If there is no GROUP BY or DISTINCT then can sort on any field in the tables (beyond what is being projected).
don-tay commented 2 years ago

In terms of implementation, this means 2 cases:

  1. If group by or distinct field is available, push sort plan to be created at the end.
  2. Else, sort plan should be created BEFORE project plan
don-tay commented 2 years ago

Behaviour above now implemented: Test queries:

SQL> select distinct sname from student order by sid
 sname
------
java.lang.RuntimeException: field sid not found.
        at simpledb.query.DistinctScan.getVal(DistinctScan.java:101)
        at simpledb.materialize.RecordComparator.compare(RecordComparator.java:41)
        at simpledb.materialize.SortPlan.splitIntoRuns(SortPlan.java:102)
        at simpledb.materialize.SortPlan.open(SortPlan.java:42)
        at test.SimpleIJ.doQuery(SimpleIJ.java:69)
        at test.SimpleIJ.main(SimpleIJ.java:39)
transaction 2 rolled back

SQL> select sname from student group by sname order by sid
 sname
------
java.lang.RuntimeException: field sid not found.
        at simpledb.materialize.GroupByScan.getVal(GroupByScan.java:93)
        at simpledb.materialize.RecordComparator.compare(RecordComparator.java:41)
        at simpledb.materialize.SortPlan.splitIntoRuns(SortPlan.java:102)
        at simpledb.materialize.SortPlan.open(SortPlan.java:42)
        at simpledb.plan.ProjectPlan.open(ProjectPlan.java:32)
        at test.SimpleIJ.doQuery(SimpleIJ.java:69)
        at test.SimpleIJ.main(SimpleIJ.java:39)
transaction 2 rolled back