Bruce Momjian wrote:
>
> I was thinking about subselects, and how to attach the two queries.
>
> What if the subquery makes a range table entry in the outer query, and
> the query is set up like the UNION queries where we put the scans in a
> row, but in the case we put them over/under each other.
>
> And we push a temp table into the catalog cache that represents the
> result of the subquery, then we could join to it in the outer query as
> though it was a real table.
>
> Also, can't we do the correlated subqueries by adding the proper
> target/output columns to the subquery, and have the outer query
> reference those columns in the subquery range table entry.
Yes, this is a way to handle subqueries by joining to temp table.
After getting plan we could change temp table access path to
node material. On the other hand, it could be useful to let optimizer
know about cost of temp table creation (have to think more about it)...
Unfortunately, not all subqueries can be handled by "normal" joins: NOT IN
is one example of this - joining by <> will give us invalid results.
Setting special NOT EQUAL flag is not enough: subquery plan must be
always inner one in this case. The same for handling ALL modifier.
Note, that we generaly can't use aggregates here: we can't add MAX to
subquery in the case of > ALL (subquery), because of > ALL should return FALSE
if subquery returns NULL(s) but aggregates don't take NULLs into account.
>
> Maybe I can write up a sample of this? Vadim, would this help? Is this
> the point we are stuck at?
Personally, I was stuck by holydays -:)
Now I can spend ~ 8 hours ~ each day for development...
Vadim