subselects

Vadim, I know you are still thinking about subselects, but I have some
more clarification that may help.

We have to add phantom range table entries to correlated subselects so
they will pass the parser.  We might as well add those fields to the
target list of the subquery at the same time:

    select *
    from taba
    where col1 = (select col2
              from tabb
              where taba.col3 = tabb.col4)

becomes:

    select *
    from taba
    where col1 = (select col2, tabb.col4 <---
              from tabb, taba  <---
              where taba.col3 = tabb.col4)

We add a field to TargetEntry and RangeTblEntry to mark the fact that it
was entered as a correlation entry:

    bool    isCorrelated;

Second, we need to hook the subselect to the main query.  I recommend we
add two fields to Query for this:

    Query *parentQuery;
    List *subqueries;

The parentQuery pointer is used to resolve field names in the correlated
subquery.

    select *
    from taba
    where col1 = (select col2, tabb.col4 <---
              from tabb, taba  <---
              where taba.col3 = tabb.col4)

In the query above, the subquery can be easily parsed, and we add the
subquery to the parsent's parentQuery list.

In the parent query, to parse the WHERE clause, we create a new operator
type, called IN or NOT_IN, or ALL, where the left side is a Var, and the
right side is an index to a slot in the subqueries List.

We can then do the rest in the upper optimizer.

--
Bruce Momjian
maillist@candle.pha.pa.us