
The following query produces the following error

BUT...

if I take out the "*" from category* it starts to work. Unfortunately I
need the "*" for my purposes. Bug?

This is from CVS of about a week ago.

SELECT question.title, count(comment.oid) FROM question,
category*,comment WHERE comment.webobject = question.oid and
question.category = category.oid GROUP BY question.title;
ERROR:  replace_vars_with_subplan_refs: variable not in target list

-- 
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com

Thu, 10 Jun 1999 13:48:01 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
BUG in 6.5 - GROUP BY inheritance
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> This is from CVS of about a week ago.

> SELECT question.title, count(comment.oid) FROM question,
> category*,comment WHERE comment.webobject = question.oid and
> question.category = category.oid GROUP BY question.title;

See my message from Sunday, "inherited GROUP BY is busted":

: It turns out that pretty much *anything* involving grouping or
: aggregation would fail if the query used inheritance, because the

This query seems to work with current sources (although EXPLAIN
fails for it --- I think that explain.c is out of sync with the
executor).

			regards, tom lane

Thu, 10 Jun 1999 11:06:22 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance 

Ok, I upgraded my CVS sources yesterday, and simple inherited/GROUP BY
queries are working again. But a more complex one still seems broken.
This is the query...




httpd=> SELECT question.oid, question.title, question.summary,
question.datetime, category.oid, category.title, category.image, email,
webuser.name, count(comment.oid) FROM question, webuser,
category*,comment WHERE question.webuser = webuser.oid and
question.category = category.oid AND comment.webobject = question.oid
GROUP BY question.oid, question.title, question.summary,
question.datetime, category.oid, category.title, category.image, email,
webuser.name UNION SELECT question.oid, question.title,
question.summary, question.datetime, category.oid, category.title,
category.image, webuser.email, webuser.name, 0 FROM question, webuser,
category* WHERE question.webuser = webuser.oid and question.category =
category.oid;
ERROR:  replace_vars_with_subplan_refs: variable not in target list


Tom Lane wrote:
> 
> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > This is from CVS of about a week ago.
> 
> > SELECT question.title, count(comment.oid) FROM question,
> > category*,comment WHERE comment.webobject = question.oid and
> > question.category = category.oid GROUP BY question.title;
> 
> See my message from Sunday, "inherited GROUP BY is busted":
> 
> : It turns out that pretty much *anything* involving grouping or
> : aggregation would fail if the query used inheritance, because the
> 
> This query seems to work with current sources (although EXPLAIN
> fails for it --- I think that explain.c is out of sync with the
> executor).
> 
>                         regards, tom lane

-- 
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters

Fri, 11 Jun 1999 23:12:56 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> httpd=> SELECT question.oid, question.title, question.summary,
> question.datetime, category.oid, category.title, category.image, email,
> webuser.name, count(comment.oid) FROM question, webuser,
> category*,comment WHERE question.webuser = webuser.oid and
> question.category = category.oid AND comment.webobject = question.oid
> GROUP BY question.oid, question.title, question.summary,
> question.datetime, category.oid, category.title, category.image, email,
> webuser.name UNION SELECT question.oid, question.title,
> question.summary, question.datetime, category.oid, category.title,
> category.image, webuser.email, webuser.name, 0 FROM question, webuser,
> category* WHERE question.webuser = webuser.oid and question.category =
> category.oid;
> ERROR:  replace_vars_with_subplan_refs: variable not in target list

Would it be possible to have some CREATE TABLE statements for these
tables, so that the problem can be reproduced without so much
reverse-engineering?

BTW, I do not know when "yesterday" your time might have been,
but I committed a critical fix for UNION + GROUP BY in
backend/optimizer/prep/prepunion.c at 10 Jun 02:55 EDT (-0400).
If you don't have that version please update and try again.

			regards, tom lane

Fri, 11 Jun 1999 10:02:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] BUG in 6.5 - GROUP BY inheritance 

Just wondering, when I do a COPY in the latest CVS into a file, it makes
the file both where I ask it to AND in
/home/postgres/data/base/<filename>. I've tried removing both files and
doing it again, and it still happens. The one in /home/postgres looks
like some kind of binary file.

This is latest CVS.

Sun, 13 Jun 1999 22:21:59 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
COPY bug?
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> Just wondering, when I do a COPY in the latest CVS into a file, it makes
> the file both where I ask it to AND in
> /home/postgres/data/base/<filename>. I've tried removing both files and
> doing it again, and it still happens. The one in /home/postgres looks
> like some kind of binary file.

Can't reproduce it here.  Anyone else seeing this?

If you do something like
	COPY int4_tbl TO 'int4out';
you will get the file dumped into the database directory, because that
is the current working directory of the backend; you need to give a
full path in this form of the COPY command to get the file put someplace
more useful.  But I don't see how COPY could produce two output files
when it's only doing one fopen()...

It occurs to me that it's a very dangerous thing for server-side COPY
to default to storing into the DB directory; pick the name of an
existing table and boom, you just overwrote your table.  Maybe the
server-side COPY command ought to insist on being given an absolute
path?

			regards, tom lane

Sun, 13 Jun 1999 16:09:42 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] COPY bug? 

Is it really necessary that the mailing lists block "non-member
submissions"?. I have several email addresses and this is rather
inconvenient.

-- 
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters

Wed, 16 Jun 1999 14:16:12 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
Postgres mailing lists
At 02:16 PM 6/16/99 +1000, Chris Bitmead wrote:
>
>Is it really necessary that the mailing lists block "non-member
>submissions"?. I have several email addresses and this is rather
>inconvenient.

In my experiences with other mailing lists - yes.  If the
mailing list address shows up anywhere accessible by 
web spiders, it will be found, and the list will be hit
by spam.

So I guess the question is really "which is worse, getting
hit by occassional spam or having to post from a subscribed
e-mail address?"

I have two e-mail addresses, one which I use for personal
e-mail and lists, so the answer's easy for me.  Keep it
closed to subscribers.

If no one anywhere has the e-mail address of the list on
a spider-accesible web page, then opening it *might*
work unless...

Anyone ever posts the list address to Usenet.



- Don Baccus, Portland OR <dhogaza@pacifier.com>
  Nature photos, on-line guides, and other goodies at
  http://donb.photo.net

Tue, 15 Jun 1999 21:43:38 -0700
Don Baccus <dhogaza@pacifier.com>
Re: [HACKERS] Postgres mailing lists
On Wed, 16 Jun 1999, Chris Bitmead wrote:

> 
> Is it really necessary that the mailing lists block "non-member
> submissions"?. I have several email addresses and this is rather
> inconvenient.

There is a pgsql-loopback address you can subscribe to, so that you don't
have to be a member of every list, but, yes, it is required...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 16 Jun 1999 01:46:04 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Postgres mailing lists
Well I reckon there should be a pseudo-subscription mechanism, where you
can register yourself as a contributor without actually getting a copy
of the email. I could subscribe under my several email addresses just so
that I can mail from all accounts but I don't really want to receive X
copies of everything. Is there a way to subscribe without getting
anything sent? A kind of suspended account I guess you would call it.

The Hermit Hacker wrote:
> 
> On Wed, 16 Jun 1999, Chris Bitmead wrote:
> 
> >
> > Is it really necessary that the mailing lists block "non-member
> > submissions"?. I have several email addresses and this is rather
> > inconvenient.
> 
> There is a pgsql-loopback address you can subscribe to, so that you don't
> have to be a member of every list, but, yes, it is required...
> 
> Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> Systems Administrator @ hub.org
> primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org

-- 
Chris Bitmead
mailto:chris@tech.com.au
http://www.techphoto.org - Photography News, Stuff that Matters

Wed, 16 Jun 1999 16:11:37 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] Postgres mailing lists
> At 02:16 PM 6/16/99 +1000, Chris Bitmead wrote:
> >
> >Is it really necessary that the mailing lists block "non-member
> >submissions"?. I have several email addresses and this is rather
> >inconvenient.
> 
> In my experiences with other mailing lists - yes.  If the
> mailing list address shows up anywhere accessible by 
> web spiders, it will be found, and the list will be hit
> by spam.
> 
> So I guess the question is really "which is worse, getting
> hit by occassional spam or having to post from a subscribed
> e-mail address?"
> 
> I have two e-mail addresses, one which I use for personal
> e-mail and lists, so the answer's easy for me.  Keep it
> closed to subscribers.
> 
> If no one anywhere has the e-mail address of the list on
> a spider-accesible web page, then opening it *might*
> work unless...
> 
> Anyone ever posts the list address to Usenet.

I think there is a way to add all your e-mail addresses to the list
without getting mail to each address.  Not sure how, though.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 16 Jun 1999 06:11:42 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Postgres mailing lists
> Well I reckon there should be a pseudo-subscription mechanism, where you
> can register yourself as a contributor without actually getting a copy
> of the email. I could subscribe under my several email addresses just so
> that I can mail from all accounts but I don't really want to receive X
> copies of everything. Is there a way to subscribe without getting
> anything sent? A kind of suspended account I guess you would call it.

That's what loopback does.

> 
> The Hermit Hacker wrote:
> > 
> > On Wed, 16 Jun 1999, Chris Bitmead wrote:
> > 
> > >
> > > Is it really necessary that the mailing lists block "non-member
> > > submissions"?. I have several email addresses and this is rather
> > > inconvenient.
> > 
> > There is a pgsql-loopback address you can subscribe to, so that you don't
> > have to be a member of every list, but, yes, it is required...
> > 
> > Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> > Systems Administrator @ hub.org
> > primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org
> 
> -- 
> Chris Bitmead
> mailto:chris@tech.com.au
> http://www.techphoto.org - Photography News, Stuff that Matters
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 16 Jun 1999 06:12:55 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Postgres mailing lists
On Wed, 16 Jun 1999, Bruce Momjian wrote:

> > At 02:16 PM 6/16/99 +1000, Chris Bitmead wrote:
> > >
> > >Is it really necessary that the mailing lists block "non-member
> > >submissions"?. I have several email addresses and this is rather
> > >inconvenient.
> > 
> > In my experiences with other mailing lists - yes.  If the
> > mailing list address shows up anywhere accessible by 
> > web spiders, it will be found, and the list will be hit
> > by spam.
> > 
> > So I guess the question is really "which is worse, getting
> > hit by occassional spam or having to post from a subscribed
> > e-mail address?"
> > 
> > I have two e-mail addresses, one which I use for personal
> > e-mail and lists, so the answer's easy for me.  Keep it
> > closed to subscribers.
> > 
> > If no one anywhere has the e-mail address of the list on
> > a spider-accesible web page, then opening it *might*
> > work unless...
> > 
> > Anyone ever posts the list address to Usenet.
> 
> I think there is a way to add all your e-mail addresses to the list
> without getting mail to each address.  Not sure how, though.

Subscribe all addresses to loopback.  It doesn't send mail out, it's 
just a place for majordomo to look for ok addresses.

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null
       # include <std/disclaimers.h>                   TEAM-OS2
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================




Wed, 16 Jun 1999 06:48:45 -0400 (EDT)
Vince Vielhaber <vev@michvhf.com>
Re: [HACKERS] Postgres mailing lists
Thus spake Chris Bitmead
> Is it really necessary that the mailing lists block "non-member
> submissions"?. I have several email addresses and this is rather
> inconvenient.

Spam is inconvenient.  The term for mailing lists that don't do this
sort of blocking is "spam amplifier."

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Wed, 16 Jun 1999 08:00:55 -0400 (EDT)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [HACKERS] Postgres mailing lists
On Wed, 16 Jun 1999, Chris Bitmead wrote:

> Well I reckon there should be a pseudo-subscription mechanism, where you
> can register yourself as a contributor without actually getting a copy
> of the email. I could subscribe under my several email addresses just so
> that I can mail from all accounts but I don't really want to receive X
> copies of everything. Is there a way to subscribe without getting
> anything sent? A kind of suspended account I guess you would call it.
> 

> > There is a pgsql-loopback address you can subscribe to, so that you
> > don't have to be a member of every list, but, yes, it is required...


> The Hermit Hacker wrote:
> > 
> > On Wed, 16 Jun 1999, Chris Bitmead wrote:
> > 
> > >
> > > Is it really necessary that the mailing lists block "non-member
> > > submissions"?. I have several email addresses and this is rather
> > > inconvenient.
> > 
> > There is a pgsql-loopback address you can subscribe to, so that you don't
> > have to be a member of every list, but, yes, it is required...
> > 
> > Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
> > Systems Administrator @ hub.org
> > primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org
> 
> -- 
> Chris Bitmead
> mailto:chris@tech.com.au
> http://www.techphoto.org - Photography News, Stuff that Matters
> 

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Wed, 16 Jun 1999 10:19:01 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Postgres mailing lists
"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> Spam is inconvenient.  The term for mailing lists that don't do this
> sort of blocking is "spam amplifier."

And, in fact, we were getting spammed pretty heavily until the blocking
solution was put in place last summer.  See the mailing list archives.
We've been mostly spam-free since then, though.

I for one would object to any thought of taking out the block.  The
loopback list seems to me to solve any problems that legitimate users
would have ...

			regards, tom lane

Wed, 16 Jun 1999 10:13:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Postgres mailing lists 
At 9:16 PM -0700 6/15/99, Chris Bitmead wrote:
>Is it really necessary that the mailing lists block "non-member
>submissions"?. I have several email addresses and this is rather
>inconvenient.

I don't know how postgres does it exactly, but NetBSD has open lists and
some aggressive kind of spam filtering based on known spammers.  I think
it's been very nice to have discussions, for example, which span both the
port-mac68k@netbsd.org list and e.g. a MkLinux list.  The spam filtering
seems to be very effective since I only see a piece of spam on the netbsd
lists once every 3 months or so.  I would suggest you consider cooperating
with them to use whatever mechanism they use.

As another specific example there was a person on port-mac68k who was
getting postgres up on a Mac 68k system and having some problems.  I tried
to cross-post my responses to the postgres-ports lists, but they never
appeared.  I was doing it from this very email address on this very
machine.  Aside from the 'now' - 'current' problem I reported earlier, his
were mostly related to interactions among the NetBSD installation, the
NetBSD package system and our install instructions rather than to any
fundamental deficiencies in postgres.  However I think he had valid
problems which I think the Postgres documenters could address, and which
would improve our product.  I think it is unfortunate that I was not able
to involve one of the postgres lists in the discussion.

Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu

Wed, 16 Jun 1999 16:25:15 -0700
"Henry B. Hotz" <hotz@jpl.nasa.gov>
Re: [HACKERS] Postgres mailing lists
On Wed, 16 Jun 1999, Henry B. Hotz wrote:

> As another specific example there was a person on port-mac68k who was
> getting postgres up on a Mac 68k system and having some problems.  I tried
> to cross-post my responses to the postgres-ports lists, but they never
> appeared.

Curious about this, since if its rejected as "non-member submission", it
will get sent back to you...

Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: scrappy@hub.org           secondary: scrappy@{freebsd|postgresql}.org 


Thu, 17 Jun 1999 09:23:34 -0300 (ADT)
The Hermit Hacker <scrappy@hub.org>
Re: [HACKERS] Postgres mailing lists
This is a test to make sure I really can post to the ports list.

At 5:23 AM -0700 6/17/99, The Hermit Hacker wrote:
>On Wed, 16 Jun 1999, Henry B. Hotz wrote:
>
>> As another specific example there was a person on port-mac68k who was
>> getting postgres up on a Mac 68k system and having some problems.  I tried
>> to cross-post my responses to the postgres-ports lists, but they never
>> appeared.
>
>Curious about this, since if its rejected as "non-member submission", it
>will get sent back to you...
>

I didn't get a bounce message.

The ports list should allow bug reports from anybody.  Also the rest of
what I said still applies:  I think it is nice to allow cross-posted
discussions among normally unrelated lists (the person with the problem
wouldn't have been a known subscriber and someone with a solution might be
a subscriber only of the non-postgres lists) and NetBSD has (or had a year
ago) a filtering mechanism that allows it while still effectively blocking
spam.

Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu

Thu, 17 Jun 1999 11:35:37 -0700
"Henry B. Hotz" <hotz@jpl.nasa.gov>
Re: [HACKERS] Postgres mailing lists
What is the status of nested structures?
Like

CREATE TABLE foo (x int4, y text);
CREATE TABLE bar (z foo, y foo);

Postgres doesn't complain but then again it doesn't seem to do anything
good with it. I can't remember seeing it mentioned in the docs anywhere
but I had the idea from somewhere that the postgres of old supported it.

Thu, 24 Jun 1999 17:16:23 +1000
Chris Bitmead <cbitmead@ozemail.com.au>
Nested structures
Using the very latest CVS I'm encountering a bug in SUBSELECTs.
This query returns 22 rows...
 
SELECT id,title FROM question;

This query returns 15 rows...

SELECT id,title FROM question WHERE question.id IN (SELECT webobject
FROM comment);

Therefore I would expect this query to return 7 rows. Instead it returns
none....

SELECT id,title FROM question WHERE question.id NOT IN (SELECT webobject
FROM comment);

I've tried it with a newly dumped and created database and it still
happens. However I can't get it to happen on a new and empty database
with simple data. In other words I can't come up with a  simple test
case. All I can think to do therefore is put my data in a file for
download. It is 100k.

ftp://ftp.tech.com.au/pub/datadump.gz

As an aside, while creating this dump I tried to load it into another
database and drop a few tables to make it smaller. I then tried to dump
it again and got the following error...

dumpRules(): SELECT failed for table productv.  Explanation from
backend: 'ERROR:  cache lookup of attribute 1 in relation 1864370 failed

-- 
Chris Bitmead
mailto:chris@tech.com.au

Sat, 26 Jun 1999 00:58:20 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
Severe SUBSELECT bug in 6.5 CVS

Ok, I've come up with a test case now. It's got to do with nulls so I
don't know if someone's going to come back and say that this is the way
it's meant to work. It sure doesn't seem intuitive but perhaps someone
can tell me if it's correct behaviour. I feel sure it can't be because
it means one spurious record in the database could destroy lots of
previously working queries. In other words you could have a whole lot of
queries that work. Then if some joker puts a record in the database with
a null, all the other records will no longer be returned. Anyway, here
is the simple test case...


httpd=> create table a (i int, aa text);      
CREATE
httpd=> create table b (i int, bb text);
CREATE
httpd=> insert into a values(1, 'foo');
INSERT 1878534 1
httpd=> insert into b values(null, 'bar');
INSERT 1878535 1
httpd=> select * from a where i not in (select i from b);
i|aa
-+--
(0 rows)

I would expect the single record in a to be returned here. Imagine I
have thousands of records in the database that this query returns. Then
someone adds a record to b with a null. Now all those previous return
values will no longer be returned. Seems really dangerous but  maybe
that is how nulls work???



Chris Bitmead wrote:
> 
> Using the very latest CVS I'm encountering a bug in SUBSELECTs.
> This query returns 22 rows...
> 
> SELECT id,title FROM question;
> 
> This query returns 15 rows...
> 
> SELECT id,title FROM question WHERE question.id IN (SELECT webobject
> FROM comment);
> 
> Therefore I would expect this query to return 7 rows. Instead it returns
> none....
> 
> SELECT id,title FROM question WHERE question.id NOT IN (SELECT webobject
> FROM comment);
> 
> I've tried it with a newly dumped and created database and it still
> happens. However I can't get it to happen on a new and empty database
> with simple data. In other words I can't come up with a  simple test
> case. All I can think to do therefore is put my data in a file for
> download. It is 100k.
> 
> ftp://ftp.tech.com.au/pub/datadump.gz
> 
> As an aside, while creating this dump I tried to load it into another
> database and drop a few tables to make it smaller. I then tried to dump
> it again and got the following error...
> 
> dumpRules(): SELECT failed for table productv.  Explanation from
> backend: 'ERROR:  cache lookup of attribute 1 in relation 1864370 failed
> 
> --
> Chris Bitmead
> mailto:chris@tech.com.au

-- 
Chris Bitmead
mailto:chris@tech.com.au

Sun, 27 Jun 1999 01:29:45 +1000
Chris Bitmead <chris.bitmead@bigfoot.com>
Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> httpd=> select * from a where i not in (select i from b);
> [ returns nothing if b contains any nulls in column i ]

Of course, what's happening here is that the NOT IN is (in effect)
transformed to
	a.i != b.i1 AND a.i != b.i2 AND a.i != b.i3 ...
(writing i1, i2, ... for the values extracted from b).  Then, since
any comparison involving NULL returns FALSE, the where-clause fails
for all values of a.i.

I think this actually is a bug, not because it's wrong for "x != NULL"
to be false, but because the SQL spec defines "a NOT IN t" as equivalent
to "NOT (a IN t)".  IN is implemented as
	a.i = b.i1 OR a.i = b.i2 OR a.i = b.i3 ...
which will effectively ignore nulls in b --- it'll return true if and
only if a.i matches one of the non-null values in b.  Our implementation
fails to maintain the equivalence that NOT IN is the negation of this.

It appears to me that to follow the SQL spec, a NULL found in a.i
should return NULL for both IN and NOT IN (the spec appears to say that
the result of IN is "unknown" in that case, and we are using NULL to
represent "unknown"):
            c) If the implied <comparison predicate> is true for at least
              one row RT in T, then "R <comp op> <some> T" is true.
            d) If T is empty or if the implied <comparison predicate> is
              false for every row RT in T, then "R <comp op> <some> T" is
              false.
            e) If "R <comp op> <quantifier> T" is neither true nor false,
              then it is unknown.
(recall that null compared to anything yields unknown, not false).
I don't believe we currently have that behavior, but it seems
reasonable.

More subtly, it looks like for a non-null a.i, IN should return TRUE
if there is a match in b, even if b also contains nulls (fine), but if
there is no match in b and b contains nulls then the spec seems to
require NULL, *not* FALSE, to be returned!  I'm not sure I like that
conclusion...

In the meantime, a workaround for Chris is to use NOT (i IN ...) instead
of NOT IN.  That should work as he expects, at least for nulls in b.

			regards, tom lane

Sat, 26 Jun 1999 13:22:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS 
> Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > httpd=> select * from a where i not in (select i from b);
> > [ returns nothing if b contains any nulls in column i ]
> 
> Of course, what's happening here is that the NOT IN is (in effect)
> transformed to
> 	a.i != b.i1 AND a.i != b.i2 AND a.i != b.i3 ...
> (writing i1, i2, ... for the values extracted from b).  Then, since
> any comparison involving NULL returns FALSE, the where-clause fails
> for all values of a.i.
> 
> I think this actually is a bug, not because it's wrong for "x != NULL"
> to be false, but because the SQL spec defines "a NOT IN t" as equivalent
> to "NOT (a IN t)".  IN is implemented as
> 	a.i = b.i1 OR a.i = b.i2 OR a.i = b.i3 ...
> which will effectively ignore nulls in b --- it'll return true if and
> only if a.i matches one of the non-null values in b.  Our implementation
> fails to maintain the equivalence that NOT IN is the negation of this.
> 
> It appears to me that to follow the SQL spec, a NULL found in a.i
> should return NULL for both IN and NOT IN (the spec appears to say that
> the result of IN is "unknown" in that case, and we are using NULL to
> represent "unknown"):

I would be interested to see how other databases handle this.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 26 Jun 1999 14:55:03 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS
Bruce Momjian ha scritto:

> > Chris Bitmead <chris.bitmead@bigfoot.com> writes:
> > > httpd=> select * from a where i not in (select i from b);
> > > [ returns nothing if b contains any nulls in column i ]
> >
> > Of course, what's happening here is that the NOT IN is (in effect)
> > transformed to
> >       a.i != b.i1 AND a.i != b.i2 AND a.i != b.i3 ...
> > (writing i1, i2, ... for the values extracted from b).  Then, since
> > any comparison involving NULL returns FALSE, the where-clause fails
> > for all values of a.i.
> >
> > I think this actually is a bug, not because it's wrong for "x != NULL"
> > to be false, but because the SQL spec defines "a NOT IN t" as equivalent
> > to "NOT (a IN t)".  IN is implemented as
> >       a.i = b.i1 OR a.i = b.i2 OR a.i = b.i3 ...
> > which will effectively ignore nulls in b --- it'll return true if and
> > only if a.i matches one of the non-null values in b.  Our implementation
> > fails to maintain the equivalence that NOT IN is the negation of this.
> >
> > It appears to me that to follow the SQL spec, a NULL found in a.i
> > should return NULL for both IN and NOT IN (the spec appears to say that
> > the result of IN is "unknown" in that case, and we are using NULL to
> > represent "unknown"):
>
> I would be interested to see how other databases handle this.
>

----------------------------------------------
create table a (i int, aa char(10));
create table b (i int, bb char(10));
insert into a values(1, 'foo');
insert into b values(null, 'bar');
select * from a where i not in (select i from b);
-----------------------------------------------
I tried the above script on:

    Informix-SE
    Oracle8

and both of them return 0 rows, like PostgreSQL.

______________________________________________________________
PostgreSQL 6.5.0 on i586-pc-linux-gnu, compiled by gcc 2.7.2.3
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Jose'



Mon, 28 Jun 1999 14:52:42 +0200
=?iso-8859-1?Q?Jos=E9?= Soares <jose@sferacarta.com>
Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS
> > > It appears to me that to follow the SQL spec, a NULL found in a.i
> > > should return NULL for both IN and NOT IN (the spec appears to say that
> > > the result of IN is "unknown" in that case, and we are using NULL to
> > > represent "unknown"):
> >
> > I would be interested to see how other databases handle this.
> >
> 
> ----------------------------------------------
> create table a (i int, aa char(10));
> create table b (i int, bb char(10));
> insert into a values(1, 'foo');
> insert into b values(null, 'bar');
> select * from a where i not in (select i from b);
> -----------------------------------------------
> I tried the above script on:
> 
>     Informix-SE
>     Oracle8
> 
> and both of them return 0 rows, like PostgreSQL.
> 

Yes, this is how I remembered Informix doing it.  Returning a NULL in
the subselect does not match anything, so hopefully we don't have a bug.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 28 Jun 1999 14:39:28 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS
Bruce Momjian wrote:

> >     Informix-SE
> >     Oracle8
> >
> > and both of them return 0 rows, like PostgreSQL.
> >
> 
> Yes, this is how I remembered Informix doing it.  
> Returning a NULL in
> the subselect does not match anything, so hopefully we 
> don't have a bug.

What is the general policy? Follow the SQL standard, or do what all the
other databases do?

Tue, 29 Jun 1999 09:51:40 +1000
Chris Bitmead <cbitmead@ozemail.com.au>
Re: [HACKERS] Severe SUBSELECT bug in 6.5 CVS
Several problems - Java and CVS.

CVS has stopped working for me. I get the error...
Fatal error, aborting.
: no such user

I've tried logging in and out to no avail. It was working for me before.
As an aside I did an strace cvs update and saw "I love you" in the
trace. (??!)

Java - I tried to build JDBC to teach myself Java. I'm getting the
following build errors. While I'm only teaching myself Java the brackets
don't even seem to match. I'm using Java 1.2 Linux.

javac postgresql/Driver.java
postgresql/Driver.java:107: Identifier expected.
    } catch(PSQLException(ex1) {
                              ^
postgresql/Driver.java:111: 'catch' without 'try'.
    } catch(Exception ex2) {
      ^
2 errors
make[1]: *** [postgresql/Driver.class] Error 1
make[1]: Leaving directory
`/usr/local/src/postgres-cvs/pgsql/src/interfaces/jdbc'
make: *** [all] Error 2

Tue, 06 Jul 1999 22:24:36 +1000
Chris Bitmead <chris@tech.com.au>
CVS, Java etc
Chris Bitmead wrote:
> 
> 
> CVS has stopped working for me. I get the error...
> Fatal error, aborting.
> : no such user

I have been seeing this as well.
I started seeing this just after doing a restore of my hard drive,
so I thought it was just me. Anybody got any clues?

-- 

Mark Hollomon
mhh@nortelnetworks.com

Wed, 07 Jul 1999 08:10:27 -0400
"Mark Hollomon" <mhh@nortelnetworks.com>
Re: [HACKERS] CVS, Java etc
Mark Hollomon wrote:
> 
> Chris Bitmead wrote:
> >
> >
> > CVS has stopped working for me. I get the error...
> > Fatal error, aborting.
> > : no such user
> 
> I have been seeing this as well.
> I started seeing this just after doing a restore of my hard drive,
> so I thought it was just me. Anybody got any clues?

It is failing for me as well.  Sorry, no clue.

Clark

Wed, 07 Jul 1999 08:25:36 -0400
Clark Evans <clark.evans@manhattanproject.com>
Re: [HACKERS] CVS, Java etc