Bug bounty Lab 1: non equality predicates

[tishyakhanna97]

Lab 1

Check if non equality predicates work fine:

• [x] All operators work on string, int
• [x] All operators are present
• [x] Check costing

[don-tay]

Test queries

select sid,sname from student where sid=1

select sid,sname from student where sname='a'

select sid,sname from student where sid=sname

select sid,sname from student where sid>1

select sid,sname from student where sid<=1

select sid,sname from student where sname>='a'

Queries throwing error:

select sid,sname from student where sid='a'

select sid,sname from student where sid<sname

java.lang.NullPointerException: Cannot read field "value" because "anotherInteger" is null
        at java.base/java.lang.Integer.compareTo(Integer.java:1461)
        at simpledb.query.Constant.compareTo(Constant.java:36)
        at simpledb.query.Term.isTermSatisfied(Term.java:76)
        at simpledb.query.Term.isSatisfied(Term.java:37)
        at simpledb.query.Predicate.isSatisfied(Predicate.java:49)
        at simpledb.query.SelectScan.next(SelectScan.java:35)
        at simpledb.query.ProjectScan.next(ProjectScan.java:32)
        at test.SimpleIJ.doQuery(SimpleIJ.java:71)
        at test.SimpleIJ.main(SimpleIJ.java:39)

[don-tay]

Error above is caused by searching for string in an integer column. In postgres, this error is thrown as such

ERROR:  invalid input syntax for type integer: "a"
LINE 1: select sid,sname from student where sid='a';
                                                ^

[don-tay]

I think we can leave the error queries as it is, since postgres is not explicit about what causes the error too

[tishyakhanna97]

@don-tay maybe can test non eq predicates when comparing diff tables as well

[don-tay]

Test queries for predicates in fields between different tables:

select sid, sname, eid from student, enroll where sid<studentid

select sid, sname, eid from student, enroll where sid<eid

Invalid queries (throwing error as expected)

select sid, sname, eid from student, enroll where sname<eid

[don-tay]

Following query not behaving as expected:

select sid,sname from student where sid!=1

index on sid used
 sid sname
----------
transaction 2 committed

select sid,sname from student where sid<>1    

index on sid used
 sid sname
----------
transaction 2 committed

[don-tay]

However, following query works:

select sid,sname from student where sname<>'a'

select eid from enroll where eid != 1

After a btree idx is created on enroll(eid), the above query gives incorrect result (empty table).

[tishyakhanna97]

@don-tay Same finding while testing indexes. However, I think this is a bonus https://github.com/don-tay/simpledb/issues/34#issuecomment-1068730139

[don-tay]

Thanks Tishya, I will implement defaulting to a seq scan for non-equi pred

EDIT: PR #40

[tishyakhanna97]

good to close? @don-tay