hzxa21 / peloton Goto Github PK

Predicate evaluation is done after the projection

• Operator

  • Pros:
    1. Good for logical to physical transformation
  • Cons:
    1. Complicated logical transformation
    2. Need to extract multi-table predicates in the first stage and fill up the operator

• Property

  • Pros:
    1. Good for logical transformation
  • Cons:
    1. May produce invalid plan (e.g. Hash Join for non-equii-join). Set a large cost?
    2. Extra enumerations? (Cascade framework already deal with this?)

When inserting a tuple with the number of values given smaller than the column count, the database gives an insertion failure and exits.

REFERENCES keyword (and maybe ON DELETE, ON UPDATE) for foreign key does not seem to be supported.

• Preserve order for the select elements in the query derived table
• Hash and compare the inner SCAN correctly
• Inner table has DISTINCT
• Parser support for select * from A join B, C, nested where and nested select
• Support explain
• Should implement ResetState() in all executor
• Subquery in having clause
• Same table alias in subquery and outer query
• Outer/Semi/Mark/Single Join
• What-if api
• Inject
• Make child_prop_deriver to be an iterator

• Do we really need to bind oids to tv_expr in the binder? Maybe resolving table and col name is enough?
• Plan to implement subquery (no aggregation) in WHERE clause (OT - Outer Table)
  • EXISTS (SELECT * FROM A1, A2, ... An where [predicates])
    1. Break down predicates by conjunction, we get [p1, p2, ... pm].
    2. Find out one pi that has the form Ai.col = OT.col (without OR), pull it out as the join condition and transform EXISTS into INNER JOIN with DISTINCT in Ai.col in the right child. If there is not such pi, keep EXISTS which indicates that it should be done in a nested iteration way.
    3. Note that we also need to find out all pi that references the ancestor tables (tables that do not occur in the current level query) and pull them out into the upper level query.
  • OT.col in (SELECT expr FROM A1, A2, ... An where [predicates])
    1. Transform IN into INNER JOIN with DISTINCT in expr in the right child with join condition OT.col = expr.
    2. Break down predicates by conjunction, we get [p1, p2, ... pm].
    3. Find out all pi that references the ancestor tables (tables that do not occur in the current level query) and pull them out into the upper level query.
  • OT.col scalar_op (SELECT expr FROM A1, A2, ... An where [predicates])
    1. Transform it into INNER JOIN with join condition OT.col scalar_op expr. Note that we need to set a flag in the join to indicate that during execution we need to check whether the right child only produce one value for each left tuple.
    2. Break down predicates by conjunction, we get [p1, p2, ... pm].
    3. Find out all pi that references the ancestor tables (tables that do not occur in the current level query) and pull them out into the upper level query.
• Optimizer refactor
  • Don't split logical transofmration and implementation
  • Only have sort property now (only introduce hash in to_plan operation)
  • Enforce property is done in OptimizeInput and OptimizeGroup skip enforce expression
• Articles we need to explore:
  • https://github.com/prestodb/presto/wiki/New-Optimizer
  • http://www.btw-2015.de/res/proceedings/Hauptband/Wiss/Neumann-Unnesting_Arbitrary_Querie.pdf
  • http://btw2017.informatik.uni-stuttgart.de/slidesandpapers/F1-10-37/paper_web.pdf
  • http://sites.computer.org/debull/95SEP-CD.pdf
  • http://www.vldb.org/pvldb/vol7/p1243-shanbhag.pdf
  • https://pdfs.semanticscholar.org/bbb0/d870073ce97ff2c61fc58a8c700d7120adf2.pdf