Bug Bounty 5: Distinct, Group By and Aggregates

[tishyakhanna97]

Checklist

• [x] Support aggregate fn SUM, COUNT, AGV, MIN, MAX
• [x] Support GROUP BY
• [x] Support DISTINCT
• [ ] Costing

[don-tay]

Test queries agg over 1 table

select majorid, count(sid), avg(sid), min(sid), max(sid), sum(sid), avg(majorid), min(gradyear) from student group by majorid

agg over 2 table join

select sectionid, count(sid), avg(majorid), min(eid), max(gradyear) from student, enroll where sid=studentid group by sectionid

aggregate over 3 table joins

select dname, sum(did), count(prof), avg(courseid), min(sectid) from dept,course,section where did=deptid and cid=courseid group by dname

[don-tay]

Test queries for distinct distinct on 1 table

select distinct gradyear from student

distinct on 2 tables

select distinct sname, studentid from student, enroll where sid=studentid order by sname desc

select distinct sname, eid from student, enroll where sid=studentid order by eid

distinct on 3 tables

select distinct did, title, prof from dept, course, section where did=deptid and cid=courseid order by prof

select distinct did, title, prof, yearoffered from dept, course, section where did=deptid and cid=courseid order by prof

distinct on 4 tables

select distinct sid,sname,dname,title,grade from student,dept,course,enroll where sid=studentid and deptid=did and majorid=did and sectionid = sectid

Query throwing error: EDIT: fix in #42

select distinct sname, gradyear from student

 sname gradyear
---------------
 alan    2022
  amy    2020
java.lang.NullPointerException: Cannot read field "value" because "anotherString" is null
        at java.base/java.lang.String.compareTo(String.java:1215)
        at simpledb.query.Constant.compareTo(Constant.java:36)
        at simpledb.query.DistinctScan.next(DistinctScan.java:51)
        at test.SimpleIJ.doQuery(SimpleIJ.java:71)
        at test.SimpleIJ.main(SimpleIJ.java:39)