CaltechCS122 笔记：Assignment 2: SQL Translation and Joins

Assignment 2: SQL Translation and Joins

NanoDB 是加州理工大学 Caltech CS122 课程使用的教学数据库系统

task

1. Implement a simple query planner that translates a wide range of parsed SQL expressions into query plans that can be executed
2. Complete the implementation of a nested-loop join plan-node that supports inner and left-outer joins
3. Create some automated tests to ensure that your inner- and outer-join support works correctly
   Submit your work along with a design document describing your efforts

DataStuct

General Planner Approach

clause in Select Stmt in a specific order:

1. WITH
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY
8. LIMIT / OFFSET

在构造 Planner 的类中，makePlan() 方法的结构如下所示：

PlanNode plan = null;

if (FROM-clause is present)
    plan = generate_FROM_clause_plan();

if (WHERE-clause is present)
    plan = add_WHERE_clause_to(plan);

if (GROUP-BY-clause and/or HAVING-clause is present)
    plan = handle_grouping_and_aggregation(plan);

if (ORDER-BY-clause is present)
    plan = add_ORDER_BY_clause_to(plan);

// There's always a SELECT clause of some sort!
plan = add_SELECT_clause_to(plan);

Grouping and Aggregation

Nested-Loop Join

for tr in r:
    for ts in s:
        if pred(tr, ts):
            add join(tr, ts) to result

Join Type

1. cross join/ tb1, tb2

2. inner join

3. left join

4. right join

5. full join

Automated Testing

LimitOffsetNode