From shavonne.marietta@studioform.it Thu Oct  13 15:31:59 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 6B9A6D80A8
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 13 Oct 2005 12:31:59 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 27407-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Thu, 13 Oct 2005 15:31:52 +0000 (GMT)
Received: from smtpout09.attiva.biz (smtpout09.attiva.biz [85.37.16.7])
	by svr1.postgresql.org (Postfix) with ESMTP id 7DDE1D7FE3
	for <pgsql-sql@postgresql.org>; Thu, 13 Oct 2005 12:31:51 -0300 (ADT)
Received: from FBCMFE02B07.fbc.local ([192.168.30.95]) by smtpout09.attiva.biz
	with Microsoft SMTPSVC(6.0.3790.211);
	Thu, 13 Oct 2005 17:32:38 +0200
Received: from dream ([81.74.60.30]) by FBCMFE02B07.fbc.local with Microsoft
	SMTPSVC(6.0.3790.211); Thu, 13 Oct 2005 17:32:41 +0200
Message-ID: <00cc01c5d00b$3a67dc70$1102a8c0@dream>
Reply-To: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
From: "Shavonne Marietta Wijesinghe" <shavonne.marietta@studioform.it>
To: <pgsql-sql@postgresql.org>
Subject: 
Date: Thu, 13 Oct 2005 17:31:48 +0200
Organization: Studio FORM
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_00C9_01C5D01B.FDB50300"
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2180
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
X-OriginalArrivalTime: 13 Oct 2005 15:32:41.0535 (UTC)
	FILETIME=[5989D4F0:01C5D00B]
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.183 required=5 tests=[AWL=-0.388,
	HTML_MESSAGE=0.001, MISSING_SUBJECT=1.57]
X-Spam-Level: *
X-Archive-Number: 200510/192
X-Sequence-Number: 23045

This is a multi-part message in MIME format.

------=_NextPart_000_00C9_01C5D01B.FDB50300
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

how can i do a query with 2 databases??

------=_NextPart_000_00C9_01C5D01B.FDB50300
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2900.2722" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3D"Verdana, Arial, Helvetica, sans-serif" =
color=3D#ff66cc size=3D2>how=20
can i do a query with 2 databases??<BR></FONT></DIV></BODY></HTML>

------=_NextPart_000_00C9_01C5D01B.FDB50300--




From smarlowe@g2switchworks.com Wed Oct  19 00:30:03 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id E2BC3D9CC1
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 18 Oct 2005 21:30:03 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 33309-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 00:29:59 +0000 (GMT)
Received: from koolancexeon.g2switchworks.com (mail.g2switchworks.com
	[63.87.162.25])
	by svr1.postgresql.org (Postfix) with ESMTP id A0617D9A7C
	for <pgsql-sql@postgresql.org>; Tue, 18 Oct 2005 21:30:00 -0300 (ADT)
Received: mail.g2switchworks.com 10.10.1.8 from 10.10.1.37 10.10.1.37 via HTTP
	with MS-WebStorage 6.5.6944
Received: from state.g2switchworks.com by mail.g2switchworks.com;
	18 Oct 2005 19:30:03 -0500
Subject: Re: 
From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <00cc01c5d00b$3a67dc70$1102a8c0@dream>
References: <00cc01c5d00b$3a67dc70$1102a8c0@dream>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Message-Id: <1129681803.15546.21.camel@state.g2switchworks.com>
Mime-Version: 1.0
X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) 
Date: Tue, 18 Oct 2005 19:30:03 -0500
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.007 required=5 tests=[AWL=0.007]
X-Spam-Level: 
X-Archive-Number: 200510/206
X-Sequence-Number: 23059

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?



From h.otto@freemail.hu Wed Oct  19 20:17:44 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4968DDA57F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 17:17:44 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 22759-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 20:17:41 +0000 (GMT)
Received: from viefep13-int.chello.at (viefep13-int.chello.at [213.46.255.15])
	by svr1.postgresql.org (Postfix) with ESMTP id 52E5CDA56A
	for <pgsql-sql@postgresql.org>; Wed, 19 Oct 2005 17:17:40 -0300 (ADT)
Received: from OTTO ([213.222.172.216]) by viefep13-int.chello.at
	(InterMail vM.6.01.04.04 201-2131-118-104-20050224) with SMTP
	id <20051019201741.PMOJ23665.viefep13-int.chello.at@OTTO>
	for <pgsql-sql@postgresql.org>; Wed, 19 Oct 2005 22:17:41 +0200
Message-ID: <001a01c5d4ea$243a1830$6400a8c0@OTTO>
From: =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
To: <pgsql-sql@postgresql.org>
References: <00cc01c5d00b$3a67dc70$1102a8c0@dream>
	<1129681803.15546.21.camel@state.g2switchworks.com>
Subject: NULL in IN clause
Date: Wed, 19 Oct 2005 22:17:34 +0200
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-2";
	reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2900.2527
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2527
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.021 required=5 tests=[AWL=-0.186,
	UPPERCASE_25_50=0.207]
X-Spam-Level: 
X-Archive-Number: 200510/224
X-Sequence-Number: 23077

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





From terry@ashtonwoodshomes.com Wed Oct  19 20:35:42 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id DEFFEDA60D
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 17:35:42 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 24628-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 20:35:39 +0000 (GMT)
Received: from mail.greatgulfhomes.com (mail.greatgulfhomes.com
	[216.191.52.66])
	by svr1.postgresql.org (Postfix) with ESMTP id A5366DA5A6
	for <pgsql-sql@postgresql.org>; Wed, 19 Oct 2005 17:35:39 -0300 (ADT)
Received: from [10.243.102.72] ([216.191.52.74]) (authenticated bits=0)
	by mail.greatgulfhomes.com (8.12.8/8.12.8) with ESMTP id j9JKXgPT029302;
	Wed, 19 Oct 2005 16:33:44 -0400
Message-ID: <4356AF8C.80709@ashtonwoodshomes.com>
Date: Wed, 19 Oct 2005 16:41:48 -0400
From: Terry Fielder <terry@ashtonwoodshomes.com>
User-Agent: Mozilla Thunderbird 1.0.5 (Windows/20050711)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: =?ISO-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: NULL in IN clause
References: <00cc01c5d00b$3a67dc70$1102a8c0@dream>
	<1129681803.15546.21.camel@state.g2switchworks.com>
	<001a01c5d4ea$243a1830$6400a8c0@OTTO>
In-Reply-To: <001a01c5d4ea$243a1830$6400a8c0@OTTO>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.025 required=5 tests=[AWL=0.025]
X-Spam-Level: 
X-Archive-Number: 200510/225
X-Sequence-Number: 23078



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



From david_dick@iprimus.com.au Wed Oct  19 20:47:34 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 7D84FDA5ED
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 17:47:34 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 30216-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 20:47:30 +0000 (GMT)
Received: from smtp02.syd.iprimus.net.au (smtp02.syd.iprimus.net.au
	[210.50.76.196])
	by svr1.postgresql.org (Postfix) with ESMTP id D9A4EDA5D9
	for <pgsql-sql@postgresql.org>; Wed, 19 Oct 2005 17:47:30 -0300 (ADT)
Received: from [210.50.200.61] (210.50.200.61) by smtp02.syd.iprimus.net.au
	(7.2.065.1) id 43023B05016A30FE; Thu, 20 Oct 2005 06:46:55 +1000
Message-ID: <4356B00C.2040702@iprimus.com.au>
Date: Thu, 20 Oct 2005 06:43:56 +1000
From: David Dick <david_dick@iprimus.com.au>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-AU; rv:1.7.8) Gecko/20050511
X-Accept-Language: en-au, en
MIME-Version: 1.0
To: =?ISO-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: NULL in IN clause
References: <00cc01c5d00b$3a67dc70$1102a8c0@dream>
	<1129681803.15546.21.camel@state.g2switchworks.com>
	<001a01c5d4ea$243a1830$6400a8c0@OTTO>
In-Reply-To: <001a01c5d4ea$243a1830$6400a8c0@OTTO>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=1.557 required=5 tests=[AWL=-0.311,
	DNS_FROM_RFC_POST=1.376, DNS_FROM_RFC_WHOIS=0.492]
X-Spam-Level: *
X-Archive-Number: 200510/226
X-Sequence-Number: 23079

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=F6lgyi Ott=F3 wrote:
> Hi,
>=20
> I have just run this command on 8.0.4 :
>=20
> SELECT 'foo' WHERE 0 NOT IN (NULL, 1);
>=20
> And it resulted is zero rows.
> Without NULL it is OK.
> Is this a bug, or the standard has such a rule?
>=20
> Best Regards,
> Otto
>=20
>=20
>=20
> ---------------------------(end of broadcast)--------------------------=
-
> TIP 4: Have you searched our list archives?
>=20
>               http://archives.postgresql.org
>=20




From sszabo@megazone.bigpanda.com Wed Oct  19 21:00:04 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id B892FDA741
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 18:00:04 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 31035-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 21:00:01 +0000 (GMT)
Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210])
	by svr1.postgresql.org (Postfix) with ESMTP id 70ED5DA72D
	for <pgsql-sql@postgresql.org>; Wed, 19 Oct 2005 18:00:02 -0300 (ADT)
Received: by megazone.bigpanda.com (Postfix, from userid 1001)
	id 23A35354B6; Wed, 19 Oct 2005 14:00:03 -0700 (PDT)
Received: from localhost (localhost [127.0.0.1])
	by megazone.bigpanda.com (Postfix) with ESMTP
	id 21946354B5; Wed, 19 Oct 2005 14:00:03 -0700 (PDT)
Date: Wed, 19 Oct 2005 14:00:03 -0700 (PDT)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: NULL in IN clause
In-Reply-To: <001a01c5d4ea$243a1830$6400a8c0@OTTO>
Message-ID: <20051019135459.D99528@megazone.bigpanda.com>
References: <00cc01c5d00b$3a67dc70$1102a8c0@dream>
	<1129681803.15546.21.camel@state.g2switchworks.com>
	<001a01c5d4ea$243a1830$6400a8c0@OTTO>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=X-UNKNOWN
Content-Transfer-Encoding: QUOTED-PRINTABLE
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 required=5 tests=[AWL=0.006]
X-Spam-Level: 
X-Archive-Number: 200510/228
X-Sequence-Number: 23081

On Wed, 19 Oct 2005, [iso-8859-2] Havasv=F6lgyi Ott=F3 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 =3DANY b. a =3DANY b returns true if a =3D x is true for any x in b. a=
 =3DANY
b returns false if a =3D x is false for all x in b.  Otherwise it returns
unknown.

0 =3D NULL returns unknown
0 =3D 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.



From tgl@sss.pgh.pa.us Wed Oct  19 21:11:35 2005
X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 4728FD891E
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 18:11:35 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 33032-10
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 19 Oct 2005 21:11:32 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id 1570ED6DAA
	for <pgsql-sql@postgresql.org>; Wed, 19 Oct 2005 18:11:32 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j9JLBUXV026535;
	Wed, 19 Oct 2005 17:11:30 -0400 (EDT)
To: =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <h.otto@freemail.hu>
Cc: pgsql-sql@postgresql.org
Subject: Re: NULL in IN clause 
In-reply-to: <001a01c5d4ea$243a1830$6400a8c0@OTTO> 
References: <00cc01c5d00b$3a67dc70$1102a8c0@dream>
	<1129681803.15546.21.camel@state.g2switchworks.com>
	<001a01c5d4ea$243a1830$6400a8c0@OTTO>
Comments: In-reply-to =?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?=
	<h.otto@freemail.hu> message dated "Wed, 19 Oct 2005 22:17:34 +0200"
Date: Wed, 19 Oct 2005 17:11:30 -0400
Message-ID: <26534.1129756290@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.11 required=5 tests=[AWL=-0.097,
	UPPERCASE_25_50=0.207]
X-Spam-Level: 
X-Archive-Number: 200510/227
X-Sequence-Number: 23080

=?iso-8859-2?Q?Havasv=F6lgyi_Ott=F3?= <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 effectively
	NOT (0 = NULL OR 0 = 1)
	NOT (NULL OR FALSE)
	NOT NULL
	NULL
ie, the result is UNKNOWN, which WHERE treats the same as FALSE.

			regards, tom lane