BUG #2112: query kills db thread

The following bug has been logged online:

Bug reference: 2112
Logged by: Jim Dew
Email address: jdew@yggdrasil.ca
PostgreSQL version: 8.1.0
Operating system: OpenBSD
Description: query kills db thread
Details:

Stumbled upon a (broken) query that causes the 8.1.0 thread handling the
query to die:

select foo from (select null) as foo

Now, this query just produces an error on 8.0.3

8.1.0:

Welcome to psql 8.1.0, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

suth=# select foo from (select null) as foo;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>


8.0.3:
Welcome to psql 8.0.3, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

suth=# select foo from (select null) as foo;
ERROR: subquery foo does not have attribute 0
suth=#

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Jim Dew" <jdew@yggdrasil.ca> writes:
> Stumbled upon a (broken) query that causes the 8.1.0 thread handling the
> query to die:
> select foo from (select null) as foo

Confirmed here --- will look into it. Thanks for the report!

> Now, this query just produces an error on 8.0.3

However, there's something rotten in the state of Denmark in 8.0 too:

regression=# select foo from (select null) as foo;
ERROR: subquery foo does not have attribute 0

7.4 gives what I'd consider a reasonable error message:

regression=# select foo from (select null) as foo;
ERROR: relation reference "foo" cannot be used as a select-list entry
HINT: Write "foo".* to denote all the columns of the relation.

Maybe we need a regression test to exercise this case, because it seems
we've been backsliding on this rather badly ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Jim Dew" <jdew@yggdrasil.ca> writes:
> Stumbled upon a (broken) query that causes the 8.1.0 thread handling the
> query to die:
> select foo from (select null) as foo

Fixed in 8.0 and up. If you need the patch for 8.1, it's pretty simple:

Index: src/backend/executor/execQual.c
================================================== =================
RCS file: /cvsroot/pgsql/src/backend/executor/execQual.c,v
retrieving revision 1.183.2.1
diff -c -r1.183.2.1 execQual.c
*** src/backend/executor/execQual.c 22 Nov 2005 18:23:08 -0000 1.183.2.1
--- src/backend/executor/execQual.c 14 Dec 2005 16:25:05 -0000
***************
*** 541,547 ****
Assert(variable->varno != OUTER);
slot = econtext->ecxt_scantuple;

! tuple = slot->tts_tuple;
tupleDesc = slot->tts_tupleDescriptor;

/*
--- 541,547 ----
Assert(variable->varno != OUTER);
slot = econtext->ecxt_scantuple;

! tuple = ExecFetchSlotTuple(slot);
tupleDesc = slot->tts_tupleDescriptor;

/*

Thanks for the report!

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster