Bug bounty 2: Indexes

[tishyakhanna97]

Lab 2

Check if non equality predicates work fine:

• [x] Create both type of indexes and ensure command to create works as expected
• [x] Check costing for hash and btree index lookup

[don-tay]

Test query:

create index en_eid_idx on enroll(eid) using btree
select eid,sectionid from enroll where eid=14

create index dept_did_idx on dept(did) using hash
select did,dname from dept where did=10

For both queries, the index was used after it was created

[tishyakhanna97]

Non eq search using index

[tishyakhanna97]

Index Creation & searches [This comment is just to track my methodology, can see if there are any bugs in the next comment]

Case 1: Index on int, string using hash

Create Index using Hash on Students.sid Create Index using Hash on Students.sname

Results

Creation

• Expected behavior: Index created
• Actual Behavior: Index created
• Sample Queries: create index stu_id on student(sid) using hash, create index stu_name on student(sname) using hash

Equality Search

• Expected behavior: Index used when searching on sid, sname
• Actual Behavior: Index used when searching on sid, sname
• Sample Queries:

  Input: (index on sid)

  SQL> select sid, sname from student where sid = 5,

  Output:

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

  Input: (index on sname)

  SQL> select sid, sname from student where sname = 'bob',

  Output:

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

  Non Equality Search (except for >= and <=)

• Expected behavior: Index used when searching on sid, sname, but no output
• Actual Behavior: Index used when searching on sid, sname, but no output
• Sample Queries:

  Input:

  SQL> select sid, sname from student where sid > 5,

  Output:

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

  Input:

  SQL> select sid, sname from student where sname > 'bob',

  Output:

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

Case 2: Index on int, string using btree

Create Index using Btree on Students.sid Create Index using Btree on Students.sname

Creation: No issue

create index stu_id on student(sid) using btree create index stu_name on student(sname) using btree

Equality Search

No issues - expected behavior (except for >= and <=)

Non Equality Search

No issues - expected behavior

Case 3: Index on int, string using hash & Btree (Same table, diff columns)

Create index using Hash on students.sid and Btree on students.sname Create index using Hash on students.sname and Btree on students.sid

Creation: No issue

create index stu_sid_idx on student(sid) using btree && create index stu_name_idx on student(sname) using hash create index stu_sid_idx on student(sid) using hash && create index stu_name_idx on student(sname) using btree

Equality Search

No issues - expected behavior `select sid, sname from student where sid = 5 and sname = 'bob' (btree on id, hash on sname)

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

`select sid, sname from student where sid = 5 and sname = 'bob' (hash on id, btree on sname)

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

`#### Non Equality Search No issues - expected behavior

Case 4 Index on non existent table.id using btree, hash

Create index on non existent table: Unexpected behavior: Index is created on uninitialized table. index is also usable. Indexes can also be created if there is no such table

creating new database
transaction 1 committed
BTree index stu_sid_idx created on STUDENT(sid).
Table STUDENT created.

Case 5 Index created after inserting values

Create index after inserting values: Unexpected behavior: no output Input: select sname from student where sname = 'bob' Output:

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

[tishyakhanna97]

Unexpected behavior:

Using >= or <=: treated as though using = (for indexes)

Input: select sid, sname from student where sname >= 'bob' Output:

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

Using string without quotes in where clause leads to returning the entire table

Input: (string without quotes) select sid from student where sname = bob Output: (all sids)

Creating index after inserting values: Index is unusable

Input: select sname from student where sname = 'bob' Output:

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

[tishyakhanna97]

Moved errors to other issues. Otherwise indexes seem to be alright