Re: Box query

From: Bob Badour <bbadour_at_pei.sympatico.ca>
Date: Sun, 23 Apr 2006 00:50:07 GMT
Message-ID: <3bA2g.64447$VV4.1212341@ursa-nb00s0.nbnet.nb.ca>

Mikito Harakiri wrote:

> 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)))

Changing the having clause won't fix a problem that already happened before the having clause is applied.

>>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)

I already posted a corrected query that works. You will have to check your own work from here on out; otherwise, see my remarks above about changing the having clause to fix a problem that occurs before it even sees the data. Received on Sat Apr 22 2006 - 19:50:07 CDT