Thread:
On Thu, 2005-10-13 at 10:31, Shavonne Marietta Wijesinghe wrote: > how can i do a query with 2 databases?? This is only supported by an add on called dblink, and it's a little bit klunky. Could schemas solve your problem?
Hi, I have just run this command on 8.0.4 : SELECT 'foo' WHERE 0 NOT IN (NULL, 1); And it resulted is zero rows. Without NULL it is OK. Is this a bug, or the standard has such a rule? Best Regards, Otto
Havasvölgyi Ottó wrote: > Hi, > > I have just run this command on 8.0.4 : > > SELECT 'foo' WHERE 0 NOT IN (NULL, 1); 0 <> NULL (Indeed nothing equals NULL, other then sometimes NULL itself) 0 <> 1 Therefore, the statement: 0 NOT IN (NULL, 1) Should always equate to false. Therefore No rows returned. Ever. Terry > > And it resulted is zero rows. > Without NULL it is OK. > Is this a bug, or the standard has such a rule? > > Best Regards, > Otto > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org > -- Terry Fielder terry@greatgulfhomes.com Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085
As i understand it, the use of NULL in SQL means the value of the column is unknown. Therefore that result would seem fair. Havasvölgyi Ottó wrote: > Hi, > > I have just run this command on 8.0.4 : > > SELECT 'foo' WHERE 0 NOT IN (NULL, 1); > > And it resulted is zero rows. > Without NULL it is OK. > Is this a bug, or the standard has such a rule? > > Best Regards, > Otto > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
Havasvölgyi Ottó <h.otto@freemail.hu> writes:
> I have just run this command on 8.0.4 :
> SELECT 'foo' WHERE 0 NOT IN (NULL, 1);
> And it resulted is zero rows.
> Without NULL it is OK.
> Is this a bug, or the standard has such a rule?
This is per spec.
The computation is effectivelyNOT (0 = NULL OR 0 = 1)NOT (NULL OR FALSE)NOT NULLNULL
ie, the result is UNKNOWN, which WHERE treats the same as FALSE.
regards, tom lane
On Wed, 19 Oct 2005, [iso-8859-2] Havasv�lgyi Ott� wrote: > Hi, > > I have just run this command on 8.0.4 : > > SELECT 'foo' WHERE 0 NOT IN (NULL, 1); > > And it resulted is zero rows. > Without NULL it is OK. > Is this a bug, or the standard has such a rule? This is standard behavior. Seeing if I can do this from memory... a NOT IN b is equivalent in the spec to NOT(a IN b). a IN b is equivalent to a =ANY b. a =ANY b returns true if a = x is true for any x in b. a =ANY b returns false if a = x is false for all x in b. Otherwise it returns unknown. 0 = NULL returns unknown 0 = 1 returns false So, 0 IN (NULL,1) returns unknown. NOT(unknown) is unknown. WHERE clauses only return rows for which the search condition is true, so a row is not returned.