db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Synge" <james.sy...@gmail.com>
Subject Diagnosing DERBY-47 and DERBY-713 (queries with IN (?,?) filters)
Date Sun, 01 Oct 2006 15:17:18 GMT
I'd like to fix DERBY-47 and DERBY-713 (essentially the same bug), and
so have been working to understand what is going wrong.

I've created a wiki page with my analysis so far,


In summary, for the queries I tested of the form:

SELECT ... FROM t WHERE indexedColumn IN (?, ..., ?)

It would appear that the IN list is treated as a single predicate to be applied
to each row in the selected index (i.e. for each value of the column found in
the index, each value in the IN list is compared with that column value), but
I'm new to working on Derby, and so could use some help figuring out how
to debug the execution of queries.  I've been able to debug through
the optimizer,
but I don't understand the generated code, especially given that it's expressed
as bytecode.  Can someone suggest a means of debugging the execution
phase (or getting a human readable form of the code)?

FWIW, my tests clearly show that for the type of query shown above, it
would be better to treat the IN list rather like the outer table of a
nested loop join (as the results below demonstrate), where each
parameter in the IN list is used to "probe" the index.
This matches a suggestion Daniel Neades made in DERBY-713.

James Synge

View raw message