Army mentioned earlier the analysis he wrote for Derby-781 (Subquery
materialization via
Hash Join). I've read most of that, and am confused by his analysis
of this select:
Q2. select 1 from tt, (t left outer join (s left outer join r on (f =
i)) on (b = e)) where (j=g)
Judging from the analysis, this query could be less ambiguously expressed as:
select 1
from
tt as T0,
(t as T1 left outer join
(s as T2 left outer join r as T3 on (T2.f = T3.i))
on (T1.b = T2.e))
where (T0.j=T3.g)
In particular, I don't understand the claim that the subqueries can
not be materialized
because of the fact that the (j=g) and (b=e) predicates create a
correlation between
the levels. I can't see any theoretical reason why the following
table expression
couldn't be materialized:
s as T2 left outer join r as T3 on (T2.f = T3.i)
There is no correlation between this table expression and the rest of
the from clause.
Furthermore, there is no correlation between:
(t as T1 left outer join (s as T2 left outer join r as T3 on (T2.f
= T3.i)) on (T1.b = T2.e))
And the rest of the from clause (just TT in this case).
My understanding of the semantics of select are that (logically) the
FROM clause is
executed first to produce a working table (in this case a CROSS JOIN
of TT with the
result of the left outer joins, which are evaluated independently of TT).
Army (or anyone else), can you provide a more detailed explanation of this?
Thanks,
James
