Re: Badly performing WHERE caluse

[Knut Anders Hatlen <Kn...@Sun.COM>]

Bryan Pendleton <bp...@amberpoint.com> writes:

>> Do people agree that this appears to be a bug with the query planner, as the
>> selection of the most appropriate index should not be influenced by static
>> conditions such as 1=1 or 2>1?
>
> I don't know of any reason why the 1=1 versus 2>1 should have affected the
> query plan chosen.

Sorry for the late response. I just came across some code in the engine
that made me remember this thread. It looks like Derby doesn't treat 1=1
and 2>1 as boolean constants during optimization, so it takes the
selectivity from the = operator and the > operator, respectively.

The = operator has selectivity 0.1, and > has selectivity 0.33. This
means that the optimizer thinks 1=1 will match 10% of the rows and
2>1 will match 33% of the rows. This is apparently enough to make the
optimizer choose another plan in this case. Of course, both of the
predicates will match 100% of the rows, so the the information that's
given to the optimizer is inaccurate in any case.

Logged here: https://issues.apache.org/jira/browse/DERBY-4416

-- 
Knut Anders