Re: Box query
Date: Sun, 23 Apr 2006 00:50:07 GMT
Message-ID: <3bA2g.64447$VV4.1212341_at_ursa-nb00s0.nbnet.nb.ca>
Mikito Harakiri 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 Sun Apr 23 2006 - 02:50:07 CEST