[Zope-DB] Inconsistent SQL results

[Mark Evans]

Hi all,

I am running Zope 2.6.1 on W2K with Oracle 8.1.  I have been using this
combination for some time and am very happy with it.  Recently, a new report
screen I wrote started showing inconsistent behaviour.  I have been able to
narrow it down to the zsql query, but I can't see why it behaves the way it is.
The query is this:

select distinct
aliquot.sample_id,
u_protein_sequence as protein,
u_dna_sequence as dna

from
lims_sys.aliquot aliquot,
lims_sys.test test,
lims_sys.test_user test_user

where
test.aliquot_id = aliquot.aliquot_id and
test.test_id = test_user.test_id and
(u_protein_sequence = 'F' or u_dna_sequence='F' or u_protein_sequence is null or
u_dna_sequence is null) and
test.test_template_id = 4 and
aliquot.status in ('P','C','V','A')

This returns the correct result, shown below, only the FIRST time it is
executed.

SAMPLE ID   PROTEIN     DNA
290         F        T
291         F        T
292         F        T
293         F        T
294         F        T
295         F        T

After that, it always returns this:
SAMPLE ID   PROTEIN     DNA
290         F     None
291         F     None
292         F     None
293         F     None

What happened?  If I execute this sql from a sql command window, it always
returns T or F.  The only way I have been able to reset the query in Zope is to
close the db connection and reopen it.  But then that only works for one
execution.  Is there something wrong with a cache somewhere or something in my
query?  I'm boggled.

TIA,

Mark