[TransWarp] Topics for tomorrow, and beyond

[Phillip J. Eby]

At 11:53 PM 10/15/03 -0400, Phillip J. Eby wrote:

>Join operators
>
>   Theta join (standard)
>
>   Outer join, Existence join (what's the associativity of these?)

Note that the exists join can be emulated via an outer join, combined with 
an assertion that the columns on the target are not null.  Both might 
actually be doable as a kind of mask operator that sits atop the "special" 
RV, placed into a regular join.  E.g.

left_outer_join(foo.x==bar.y,foo,bar)

would be rendered as:

theta_join(foo.x==bar.y, foo, outer_wrapper(bar,y))

And there could be similar exists/not_exists wrappers, allowing all joins 
to be reduced to thetas over wrappers.  But there's lots of things to 
verify before taking this approach, mostly under the heading, what happens 
when you join the result to something else?  Probably it's okay, but the 
associativity of these sorts of joins doesn't seem to be well documented 
*anywhere*...


>   Aggregates - how to specify, fold?
>
>   Constant folding to remove outer joined tables by relocating
>     criteria to base table

Plus, don't forget UNION...  perhaps INTERSECT and DIFFERENCE as well.  We 
don't use these right now because Sybase didn't do them years ago, but 
there were times that I really wanted them...



>Filter operators
>
>   Aggregates - no syntax yet
>
>   IN()
>     map to set of OR()'s for constant sequence
>     EXISTS join for correlation parameter or subexpression
>     what do we do if parameter is input?  SQL generation will suck
>     subexpression for IN() would need a way to create a table alias
>       (probably better just to use correlation)
>
>   NOT()
>     what happens to traversals underneath?
>        force not_exists join?
>          that would mean no output parameters allowed

...and NOT-IN()!


>SQL Generation
>   ...and cross-database joins

Note that an equijoin to an aggregate may be simulatable by embedding a 
'(SELECT ...) AS whatever' in the SELECT clause of a query...  at least on 
DB's that support doing that.  We may want to use that trick in some cases.