Re: Box query
Date: 22 Apr 2006 17:37:23 -0700
Message-ID: <1145752643.417344.20490_at_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(
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 > b1.low and b2.low < b1.high
*or* b1.low > b2.low and b1.low < b2.high
then 1 else 0.00000001 end)))
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 Sun Apr 23 2006 - 02:37:23 CEST