[EMAIL PROTECTED] wrote: > On Nov 10, 8:57 am, Steve Holden <[EMAIL PROTECTED]> wrote: >> Robin Becker wrote: > ...snip... >>> In old style python there was a sort of standard behaviour whereby None >>> was comparable with most of the other primitive types. That at least >>> allowed us to performs various stupid tricks with data. Unfortunately it >>> seems that None is no longer orderable even against itself. >>> >>> Is there any advice on how to create N/A float or integer or string >>> values? I assume the DB api will continue to return None for null >>> columns no matter what the column type. >>> >>> Presumably I can always define my own comparator which makes None < x >>> for all x!=None. >> Yes, you can (though that will mean subtyping the standard Python types >> and ensuring you use only the subtypes, not always easy to maintain). >> >> Of course, using SQL against a traditional RDBMS will not return rows >> with NULL values for salary in a query such as >> >> SELECT name, address WHERE salary < 10000 >> >> precisely *because* NULL (absence of value) does not compare with any >> value. > > Huh? Thats like saying it's ok if cmp raises an error > when comparing negative numbers because "abs(x)" always > return positive ones. You will find plenty of cases > when db apps return NULL, e.g.: > > SELECT name, salary WHERE name LIKE 'Steven %' > I'm not saying an RDBMS can't return NULL values. I am saying that comparisons with NULL return NULL, not true or false. SQL uses three-valued logic.
> So you could say that 3.0 is forcing us to acknowledge database >> reality ;-) > > (Again) huh? > Reality in databases is that NULL *is* comparable. > "NULL==something" returns False, it doesn't raise an error. That's at best misleading and at worst just plain wrong. If I have the following table T: +-------+-------+ | a | b | +-------+-------+ | 1 | 1 | +-------+-------+ | 2 | NULL | +-------+-------+ you appear to be telling me that SELECT * FROM T WHERE b <> 1 will return (2, NULL), whereas in fact it returns the empty set, since the tests NULL = something, and NULL <> something both in fact return NULL. You can't do an equality or inequality comparison between NULL and anything else - even another NULL - and get anything but a NULL result. You have to explicitly test for NULLs using IS NULL. regards Steve -- Steve Holden +1 571 484 6266 +1 800 494 3119 Holden Web LLC http://www.holdenweb.com/ -- http://mail.python.org/mailman/listinfo/python-list
