Optimizer problem with subselect.c?

Hi all.

Interesting result when migrating from 7.2.? to 7.4.1, the dump and import went fine, no errors or whistles, in fact all my tests went through okay but a few types of queries which once worked, now don't, and in an odd fashion.

When I checked out the mailing lists and changelogs (in cvs) it seems there is some gotchas in the subselect code in the optimizer/plan section, so I have a hunch it's there somewhere.

select count(id) from connection_info where now() - opentime <= '1 day';
ERROR: variable not found in subplan target lists

This particular error seems to have some fame attached to it...

Anyway, here's the vitals, including our layout:

View "connection_info"
Column | Type | Modifiers
------------------------+--------------------------+-----------
location_appearance_id | integer |
status | integer |
termination_reason | integer |
inputoctets | integer |
outputoctets | integer |
opentime | timestamp with time zone |
termtime | timestamp with time zone |
end_time | timestamp with time zone |
lastpackettime | timestamp with time zone |
id | integer |
session_status | integer |
connection_status | integer |
location_id | integer |
client_ip | inet |
client_mac | macaddr |
original_url | text |
sshash | character varying(64) |
login_id | integer |
organization_id | integer |
connection_timeout | interval |
roaming_username | text |
actual_used | double precision |
current_used | double precision |
idle | double precision |
View definition: SELECT "session".location_appearance_id, "session".status, "session".termination_reason, "session".inputoctets, "session".outputoctets, "session".opentime, "session".termtime, "session".end_time, "session".lastpackettime, "session".id, "session".status AS session_status, "session".status AS connection_status, location_appearance.location_id, location_appearance.client_ip, location_appearance.client_mac, location_appearance.original_url, site_session.sshash, login_site_session.login_id, login.organization_id, login.connection_timeout, roaming_site_session.username AS roaming_username, date_part('epoch'::text, ("session".lastpackettime - "session".opentime)) AS actual_used, date_part('epoch'::text, (now() - "session".opentime)) AS current_used, date_part('epoch'::text, (now() - "session".lastpackettime)) AS idle FROM (((((("session" JOIN location_appearance ON (("session".location_appearance_id = location_appearance.id))) JOIN location_appearance_site_sessio ON
((location_appearance.id = location_appearance_site_sessio.location_appearance_id))) JOIN site_session ON (((location_appearance_site_sessio.site_session_sshash)::text = (site_session.sshash)::text))) LEFT JOIN login_site_session ON (((login_site_session.site_session_sshash)::text = (site_session.sshash)::text))) LEFT JOIN login ON ((login_site_session.login_id = login.id))) LEFT JOIN roaming_site_session ON (((roaming_site_session.site_session_sshash)::text = (site_session.sshash)::text))) WHERE (site_session.status <> 3);

-----------------

Table "session"
Column | Type | Modifiers
------------------------+--------------------------+-------------------------------------------------------
location_appearance_id | integer | not null
status | integer | default 0
termination_reason | integer |
inputoctets | integer | default 0
outputoctets | integer | default 0
opentime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
termtime | timestamp with time zone |
end_time | timestamp with time zone |
lastpackettime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
id | integer | not null default nextval('"connection_id_seq"'::text)
Indexes: connection_id_idx,
session_laid_idx,
session_status_idx
Triggers: RI_ConstraintTrigger_776160,
RI_ConstraintTrigger_776163,
RI_ConstraintTrigger_776397,
RI_ConstraintTrigger_776400

-------------------

Table "location_appearance"
Column | Type | Modifiers
--------------+--------------------------+----------------------------------------------------------------
id | integer | not null default nextval('"location_appearance_id_seq"'::text)
location_id | integer | not null
client_ip | inet | not null
client_mac | macaddr |
original_url | text |
create_time | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes: location_appearance_id_idx
Primary key: location_appearance_pk
Triggers: RI_ConstraintTrigger_776097,
RI_ConstraintTrigger_776101,
RI_ConstraintTrigger_776102,
RI_ConstraintTrigger_776164,
RI_ConstraintTrigger_776165,
RI_ConstraintTrigger_776334,
RI_ConstraintTrigger_776338,
RI_ConstraintTrigger_776339,
RI_ConstraintTrigger_776401,
RI_ConstraintTrigger_776402

-------------------

Table "site_session"
Column | Type | Modifiers
------------+--------------------------+----------------------------------------------------
sshash | character varying(64) | not null
createtime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
lastupdate | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
status | integer | default 0
Indexes: site_session_date_idx,
site_session_sshash_idx,
site_session_status_idx
Primary key: site_session_pk
Triggers: RI_ConstraintTrigger_776104,
RI_ConstraintTrigger_776105,
RI_ConstraintTrigger_776110,
RI_ConstraintTrigger_776111,
RI_ConstraintTrigger_776113,
RI_ConstraintTrigger_776114,
RI_ConstraintTrigger_776119,
RI_ConstraintTrigger_776120,
RI_ConstraintTrigger_776179,
RI_ConstraintTrigger_776180,
RI_ConstraintTrigger_776185,
RI_ConstraintTrigger_776186,
RI_ConstraintTrigger_776341,
RI_ConstraintTrigger_776342,
RI_ConstraintTrigger_776347,
RI_ConstraintTrigger_776348,
RI_ConstraintTrigger_776350,
RI_ConstraintTrigger_776351,
RI_ConstraintTrigger_776356,
RI_ConstraintTrigger_776357,
RI_ConstraintTrigger_776416,
RI_ConstraintTrigger_776417,
RI_ConstraintTrigger_776422,
RI_ConstraintTrigger_776423

-------------------

Table "location_appearance_site_sessio"
Column | Type | Modifiers
------------------------+-----------------------+-----------
location_appearance_id | integer | not null
site_session_sshash | character varying(64) | not null
Indexes: lass_id_idx,
lass_sshash_idx
Triggers: RI_ConstraintTrigger_776100,
RI_ConstraintTrigger_776103,
RI_ConstraintTrigger_776337,
RI_ConstraintTrigger_776340

------------------

Table "login"
Column | Type | Modifiers
--------------------+--------------------------+-------------------------------------------------------------
id | integer | not null default nextval('"login_id_seq"'::text)
organization_id | integer | not null
email | text | not null
realname | character varying(32) | default ''::character varying
title | character varying(32) | default ''::character varying
question | character varying(32) | default ''::character varying
answer | character varying(32) | default ''::character varying
contact_method | integer | default 0
connection_timeout | interval | default '00:15:00'::interval
auto_connect | boolean | not null default true
createdate | timestamp with time zone | not null default ('now'::text)::timestamp(6) with time zone
Primary key: login_pk
Unique keys: login_id_key
Triggers: RI_ConstraintTrigger_775965,
RI_ConstraintTrigger_775969,
RI_ConstraintTrigger_775970,
RI_ConstraintTrigger_776026,
RI_ConstraintTrigger_776027,
RI_ConstraintTrigger_776035,
RI_ConstraintTrigger_776036,
RI_ConstraintTrigger_776044,
RI_ConstraintTrigger_776045,
RI_ConstraintTrigger_776053,
RI_ConstraintTrigger_776054,
RI_ConstraintTrigger_776056,
RI_ConstraintTrigger_776057,
RI_ConstraintTrigger_776083,
RI_ConstraintTrigger_776084,
RI_ConstraintTrigger_776107,
RI_ConstraintTrigger_776108,
RI_ConstraintTrigger_776205,
RI_ConstraintTrigger_776209,
RI_ConstraintTrigger_776210,
RI_ConstraintTrigger_776263,
RI_ConstraintTrigger_776264,
RI_ConstraintTrigger_776272,
RI_ConstraintTrigger_776273,
RI_ConstraintTrigger_776281,
RI_ConstraintTrigger_776282,
RI_ConstraintTrigger_776290,
RI_ConstraintTrigger_776291,
RI_ConstraintTrigger_776293,
RI_ConstraintTrigger_776294,
RI_ConstraintTrigger_776320,
RI_ConstraintTrigger_776321,
RI_ConstraintTrigger_776344,
RI_ConstraintTrigger_776345

--------------------

Table "roaming_site_session"
Column | Type | Modifiers
---------------------+--------------------------+----------------------------------------------------
username | text | not null
site_session_sshash | character varying(64) | not null
createtime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes: roaming_sshash_idx
Triggers: RI_ConstraintTrigger_776112,
RI_ConstraintTrigger_776349

--------------------

Table "login_site_session"
Column | Type | Modifiers
---------------------+--------------------------+----------------------------------------------------
login_id | integer | not null
site_session_sshash | character varying(64) | not null
createtime | timestamp with time zone | default ('now'::text)::timestamp(6) with time zone
Indexes: login_sshash_idx
Triggers: RI_ConstraintTrigger_776106,
RI_ConstraintTrigger_776109,
RI_ConstraintTrigger_776343,
RI_ConstraintTrigger_776346

-----------------------

And I think that's all of them. Most of the relevant data is in the original view (connection_info). Also, please pardon the obfuscation of the database, it's the softwares' fault!

Thanks,
--Daniel F. O'Neill
fatport.com