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)))
>
>
>>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:45:25 CDT
