Bryan Pendleton <[email protected]> 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
