UNDO of todays fix for join recognition
UNDO of todays fix for join recognition

After some thinking I realized that the current join recognition does
not support the second nested join of Q9 and Q12 in the embedded scenario.

The reason is the current handling of scopes. Because every for loop,
if clause, typeswitch and recursive user-defined function introduces a
new iteration scope (stored in fid), the variable mapping has to cope with
all that different scopes. The current join recognition however only counts
the scopes introduced by for-loops. Therefore the evaluation scope the join
recognition infers cannot be treated as a number (from which we may
compute the overall evaluation scope), but only as a relative information.

This relative knowledge allows to detect the following two join variants:
1 one side is completely independent (scope = 0)
2. one side is independent of the for loop of the other side (scope = other-1)

Query Q9 does contain two nested joins, whose arguments are all completely
independent. Therefore the join recognition detects both joins. If Q9
however is nested inside a user-defined function, we cannot be sure that
it is called in the outer-most scope. This does not prevent the join
recognition to detect both joins, but they are treated differently then
before. Each join is evaluated in its current level - 1 (see second variant
of join recognition).

In Peters embedded scenario he calls the Q9-UDF in the outermost scope.
Thus the first thetajoin is evaluated as in the outermost scope (like the.
normal query Q9). The second join however is evaluated in the context of the
outer for loop (this is the current scope - 1). This makes the join in
embedded Q9 and Q12 especially expensive as they have to evaluate the second
join on cartesian products (for-loop 1--for-loop 2 and for-loop 1--for-loop 3).

Unless somebody is willing to invest some effort to cope with all scopes
(which probably includes quite some work and thinking) I see no solution
to this problem. We are limited to the two above sketched cases and Q9 and
Q12 will be slow in the embedded scenario.

Jan

zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb