[egenix-users] SELECTing with placeholders and NULL values

[Charles Bearden]

> -----Original Message-----
> From: Steve Holden [mailto:sholden at holdenweb.com]
> Sent: Wednesday, June 23, 2004 9:34 AM
> 
> Charles Bearden wrote:
> 
[...]
> >
> >How can NULL values be used with the standard placeholders in
mx.ODBC?
> >I'm probably missing something obvious.
> >
> 
> I'm not sure you are. The point about NULL is that it introduces a
> three-valued logic into SQl comparisons. An explicit comparison with
> NULL can never be true - as far as I know, even "NULL = NULL" will
> evaluate to NULL. In fact, SQL Query Analyzer proves this:
> 
> if NULL = NULL
> SELECT 1
> ELSE
> SELECT 2
> 
> returns 2.
> 
> So, for example, in a SQL Server table near me:
> 
> SELECT COUNT(*) FROM OnePlus WHERE TGUID = NULL
> 
> returns zero, even though
> 
> SELECT COUNT(*) FROM OnePlus WHERE TGUID IS NULL
> 
> returns 38!
> 
> In other words, the only explicit test allowable for NULL values is
"IS
> NULL", and you are going to have to phrase your queries to allow that.

Thank you for your response.  I knew about the need to use the 'IS' test
rather than the '=' test with NULL values.  I suppose I thought that
there would be a consistent way of using values of all kinds with
placeholders in SELECT statements.  As your example demonstrates, a NULL
value in a WHERE clause is useless (and thus meaningless) unless tested
with 'IS'.  It seems to me therefore that NULL is unambiguous in this
context, and that 'WHERE Foo = ?', where the value of '?' is NULL, could
safely be rewritten behind the scenes to 'WHERE Foo IS NULL' in all
cases, in order to provide a consistent way of handling all values
interpolated into a WHERE clause with placeholders.  There are probably
strong reasons not to do this (too much of Perl's DWIMNWIS, which on
balance I find confusing; or, inconsistent with the semantic conventions
of placeholders in other ODBC and database APIs).

Sorry if I seem to be fussing.  I suppose I'm just being lazy, not
wanting to have to generate my WHERE clauses dynamically :-)

> The only alternative would be to retrieve the rows and then perform
the
> testing in Python, which is likely to be much less efficient. Of
course,
> you know much more about the problem than me, so you may be able to
find
> some way to easily get what ytou want, but I suspect you may end up
> havikng to use different statements when arguments are None and
non-None.

That's what I'll do.  Some WHERE clauses may have 4 or 5 tests, of which
three may have NULL or non-NULL values.  I just thought that if I didn't
have to generate the statements dynamically, the code would be neater.

Thanks,

Chuck Bearden
Systems Analyst III
School of Health Information Sciences
University of Texas at Houston
713.500.3954 (voice)
713.500.3907 (fax)
Charles.F.Bearden at uth.tmc.edu