[EMAIL PROTECTED] wrote:
when looking at the code it seems to me that the probe values are
set by the constructor, and then cloned on each new openCore(). I don't
think this approach is compatible with re-using internal result sets
(see DERBY-827), because when re-executing a prepared statement with
different parameters I end up with a MultiProbeTableScanResultSet with
the same probe values as before. Example (from lang/inbetween.sql):
<snip example>
Odd. Do you have data in your table that is different from the data created in
lang/inbetween.sql? I ran all of the DDL statements for "bt1" as copied from
that test, then ran the statements you showed in your example. But it looks
like I'm seeing different behavior (note: I added printlns to MultiProbeTSCR to
print out the received proble values):
ij> prepare p1 as 'select * from bt1 where i in (?, ?)';
ij> execute p1 using 'values (2, 4)';
IJ WARNING: Autocommit may close using result set
-=-> Probe values: 2 4
I |C |DE
------------------------
2 |two |22.2
1 row selected
ij> execute p1 using 'values (-2, -4)';
IJ WARNING: Autocommit may close using result set
-=-> Probe values: -2 -4
I |C |DE
------------------------
0 rows selected
I also executed the above statements with autocommit turned off, but I see the
same results...?
Note, though, that the issue of statement reuse *did* come up when I wrote
MultiProbeTableScanResultSet. As I'm sure you noticed, there is the following
comment in the openCore() method of that class:
if (needSort)
{
/* RESOLVE: For some reason sorting the probeValues array
* directly leads to incorrect parameter value assignment when
* executing a prepared statement multiple times. Need to figure
* out why (maybe related to DERBY-827?). In the meantime, if
* we're going to sort the values we use clones. This is not
* ideal, but it works for now.
*/
Maybe this is irrelevant, but since you're becoming more familiar with
re-execution of prepared statements, maybe you have some insight here? ;)
My take is that the probe values must somehow be obatined from the
activation whenever openCore() gets called. How difficult would it be
to do that?
If that might solve the problem you're seeing (which I am not currently able to
reproduce) then I think the approach would be to generate the array of probe
values as a "saved object", which can then be retrieved from the activation just
like any other saved object. My perhaps slightly uninformed guess is that this
shouldn't be too difficult--maybe a day or two of coding?
And of course, such an effort would be much more valuable if it solved the
cloning the "workaround" that is mentioned in the above RESOLVE comment, as well...
It'd be great if you could post an sql script that demonstrates the problem you
show in its entirety, as I'm unable to reproduce it on my own.
Thank you for bringing this up!
Army
