Builds a multi-level Project with all lateral column aliases and their dependencies. First, from top scope, we acquire dependency levels of all aliases. Dependency level is defined as a number of attributes that an attribute depends on in the lateral alias reference chain. For example, in a query like:

SELECT 0 AS a, 1 AS b, 2 AS c, b AS d, a AS e, d AS f, a AS g, g AS h, h AS i

Dependency levels will be as follows:

level 0: a, b, c level 1: d, e, g level 2: f, h level 3: i

Once we have dependency levels, we construct a multi-level Project in a following way:

• There is exactly one Project node per level.
• Project lists are compounded such that project lists on upper levels must contain all attributes from the below levels.
• iterationStart is the project list on level 0.
• In each iteration, if we are resolving LCAs from Aggregate, we need to update aliases in the project list based on remappedAliases (detailed description in updateAliasTrees). remappedAliases should be None if we are resolving LCAs in Project.
• originalProjectList is placed on top of the multi-level Project. Any aliases that have been laterally referenced need to be replaced with only their names. This is because their full definitions ( attr as name ) have already been defined on lower levels.
• If an attribute is never referenced, it does not show up in multi-level project lists, but instead only in the top-most Project.
• Additionally, collect all aliases from newly created Project nodes and add them to aliasesToCollect.

For previously given query, following above rules, resolved Project would look like:

Project [a, b, 2 AS c, d, a AS e, d AS f, g, h, h AS i] +- Project [a, b, d, g, g AS h] +- Project [a, b, b AS d, a AS g] +- Project [0 AS a, 1 AS b] +- OneRowRelation

Handles lateral column references in Aggregate operator by doing the following:

• Replaces the original Aggregate with a Project(Aggregate) structure in order to eliminate lateral column references from the Aggregate (detailed description can be found in extractLcaAndReplaceAggWithProject).
• Asserts that the newly created Aggregate is valid.
• Because the new Project now contains lateral column references, call buildProjectWithResolvedLca to resolve LCAs in the Project.
• Set the flag in the current NameScope that we have an LCA in Aggregate operator.
• In order to be able to resolve Sort on top of an Aggregate that has LCAs, we need to collect all aliases from Aggregate, as well as any aliases from artificially inserted Project nodes.
• Collects all aliases from newly created Aggregate and Project nodes and adds them to aliasesToCollect