[Zope-DB] More Zope Zsql inconsitency

[Mark Evans]

After further exploration I have found the following .  Using a more explicit
query:

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' and u_dna_sequence ='F') or      -- Line1
 (u_protein_sequence = 'F' and u_dna_sequence ='T') or      -- Line2
 (u_protein_sequence = 'T' and u_dna_sequence ='F') or      -- Line3
 (u_protein_sequence is null and u_dna_sequence ='F') or    -- Line4
 (u_protein_sequence is null and u_dna_sequence ='T') or    -- Line5
 (u_protein_sequence = 'T' and u_dna_sequence is null) or   -- Line6
 (u_protein_sequence = 'F' and u_dna_sequence is null) or   -- Line7
 (u_protein_sequence is null and u_dna_sequence is null) or -- Line8
 (u_protein_sequence = 'T' and u_dna_sequence ='T')         -- Line9
)
 and
test.test_template_id = 4 and
aliquot.status in ('P','C','V','A')

There are only a very few rows in the table where u_dna_sequence is null. The
format of the row that contains the null is always protein= T, DNA = null.  If
line 7 (which corresponds to this pattern) is commented out, then everything
runs ok with no errors.  If it is left in, then the query will run correctly the
first time, but everytime after that, random dna values are set to None in the
ZMI zsql test display.  Since my DTML that formats the output sets calls None a
"no", if something is really a "yes", this is a problem.  Any ideas why the ZMI
or zope is doing this?  Is there known issues with handling null values?

Thanks,

Mark