Re: Box query

From: Mikito Harakiri <mikharakiri_nospaum_at_yahoo.com>
Date: 22 Apr 2006 17:37:23 -0700
Message-ID: <1145752643.417344.20490@v46g2000cwv.googlegroups.com>

Bob Badour wrote:
> If you take a look at the second-to-last step, your query failed before
> it got to the having clause:
>
> Let tmp1 = { (box1,box2,exp(sum(ln(...)))) |
> (1,1,6), (1,2,3e-8),
> (2,1,3e-8), (2,2,6)
> }

All right, let's make the case condition symmetric:

having 1 = exp(sum(ln(

       case when b2.low between b1.low and b1.high
               *or*   b1.low between b2.low and b2.high

then 1 else 0.00000001 end)))

> It doesn't have the correct intersection volume of (1,2,4), which is
> what I thought the query was supposed to return. And if one has two
> intersecting hyperboxes that abutt, one could argue that it should
> return a row indicating a zero-volume intersection. Your having clause
> will simply remove the result.

This would be true if strict ordering were used

having 1 = exp(sum(ln(

       case when b2.low > b1.low and b2.low < b1.high
               *or*   b1.low >  b2.low and b1.low < b2.high

then 1 else 0.00000001 end)))

With "less than or equal" we report cubes as intersecting even if they touch each other.

The alternative "having" clause leveraging the min aggregate is buggy as well. How about

having 0 < min(

       case when b2.low between b1.low and b1.high
               *or*   b1.low between b2.low and b2.high

then 1 else 0 end) Received on Sat Apr 22 2006 - 19:37:23 CDT