psql 7.3.1: signal 11 + replace_vars_with_subplan_refs: variable not in subplan target list

From: Stefanos Harhalakis <v13(at)it(dot)teithe(dot)gr>
To: pgsql-bugs(at)postgresql(dot)org
Subject: psql 7.3.1: signal 11 + replace_vars_with_subplan_refs: variable not in subplan target list
Date: 2003-02-02 20:47:04
Message-ID: 200302022247.04882.v13@it.teithe.gr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

I'm stuck here with a query that kills postgresql backend.

I've installed postgresql 7.3.1 on two machines..
one with gcc 3.2, glibc 2.3.1 source based (a)
and the other with gcc 2.95.3, glibc 2.2.3 slackware 8 (b)
both are PIII with kernel 2.4.20.
(a) has ext3 and (b) has xfs for the database storage...

tested both with altered and original postgresql.conf...

I've the same program installed on both machines using the same database...
both fail when i'm executing a specific query...

Those are the errors (I've removed a lot from the queries to isolate the
problem):

This fails:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND v.sw_ip IS
NULL AND ( h.mac = v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) );
ERROR: replace_vars_with_subplan_refs: variable not in subplan target list

but this works:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac =
v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) );

this fails too:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac =
v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) AND ( ( h.sw_ip IS NULL AND
v.sw_ip IS NULL ) ) );
ERROR: replace_vars_with_subplan_refs: variable not in subplan target list

and this causes a segmentation fault:
netinfo=> select * from view_all v where NOT EXISTS ( SELECT * FROM history h
WHERE ( h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac =
v.mac OR ( h.mac IS NULL AND v.mac IS NULL ) ) AND ( h.sw_ip = v.sw_ip OR (
h.sw_ip IS NULL AND v.sw_ip IS NULL ) ) );
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.

To reproduce it:
createdb test
createlang plpgsql test
psql -f create.sql test

and then run this:
select * from view_all v where NOT EXISTS ( SELECT * FROM history h WHERE (
h.ip = v.ip OR ( h.ip IS NULL AND v.ip IS NULL ) ) AND ( h.mac = v.mac OR (
h.mac IS NULL AND v.mac IS NULL ) ) AND ( h.sw_ip = v.sw_ip OR ( h.sw_ip IS
NULL AND v.sw_ip IS NULL ) ) );

<<V13>>

--
Key fingerprint: 7448 3D26 EA5D 275C 749A B597 3576 0073 6AD2 9335

Attachment Content-Type Size
create.sql text/plain 30.7 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Insyde 2003-02-03 10:52:26 Re: Problem when adding an existing primary key
Previous Message Josh Berkus 2003-02-02 20:28:53 Re: Problem when adding an existing primary key