On Tue, 6 Feb 2007, Aaron Logue wrote: > On Mon, 5 Feb 2007, Stephan Szabo wrote: > > On Fri, 2 Feb 2007, Aaron Logue wrote: > > > The following bug has been logged online: > > > > > > Bug reference: 2961 > > > Logged by: Aaron Logue > > > Email address: [EMAIL PROTECTED] > > > PostgreSQL version: 8.2.1 > > > Operating system: Linux (various flavors) > > > Description: NULL values in subselects force NOT IN to false > > > Details: > > > > > > SELECT X FROM (SELECT 42 AS X) AS FOO WHERE X NOT IN (7,NULL); > > > > > > returns 0 rows. Shouldn't "X NOT IN (7,NULL)" be > > > true if X is neither 7 nor NULL? Removing the NULL causes the row to be > > > returned. > > > > NOT IN with NULLs is defined by spec in a way that most people do not > > expect if they aren't thinking about three valued logic. > > > > x NOT IN RVC is effectively NOT(x = ANY RVC). > > ... > > Shouldn't IS be used to compare x with a NULL rather than = ?
It depends on what you're trying to do, really. SQL could have defined IN differently, but they decided to make IN in terms of equality rather than say distinctness. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
