Re: Query breaking with unknown expression type (lost s
Sorry 'bout that ...
Here's a test script that is as simple a setup as I've yet found that
reproduces this bug.
create table t1 (k int4, t1c int4);
create table t2 (k int4, t2c int4);
create table t3 (k int4, t3c int4);
insert into t1 values (1, 2);
insert into t2 values (1, 3);
insert into t3 values (1, 4);
-- triggers bug
select * from (select t1.*, (select t2.c from t2 limit 1) as v from t1) t1v
natural left join t3;
-- triggers bug
select * from (select t1.*, (select t2.t2c from t2 limit 1) as v from t1)
t1v
natural inner join t3 where t1v.t1c > 1 and (t3.t3c > 3 or t3.t3c is null);
-- does *not* trigger bug
select * from (select t1.*, (select t2.t2c from t2 limit 1) as v from t1)
t1v
natural right join t3;
-Matt
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, December 04, 2002 16:37
To: Matthew Gabeler-Lee
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query breaking with unknown expression type (lost
subquery from v iew?)
Matthew Gabeler-Lee <mgabelerlee@zycos.com> writes:
Query, used to work in 7.2.3:
SELECT * from VResults NATURAL LEFT JOIN qrp_events
WHERE qrp_score > 45 AND qry_charge < 3 AND vst_valstate IS NULL
AND (
(qpe_name = 'autoval.pl' AND qpe_version < 3)
OR qpe_name IS NULL
)
ORDER BY sdt_id ASC, qry_num ASC, qrp_pnum ASC
If that means subquery, there is a subquery in
the VResults view, but for some reason that subquery isn't showing up in
the
explain output!
How do you expect us to debug this when you haven't provided the view
definition?
I'm willing to dig into it if I have a test case to look at, but I don't
have time to try to intuit a test case from an incomplete bug report.
Give me a script to reproduce the failure, please.
regards, tom lane
Matthew Gabeler-Lee <mgabelerlee@zycos.com> writes:
-- triggers bug
select * from (select t1.*, (select t2.c from t2 limit 1) as v from t1) t1v
natural left join t3;
Excellent, thank you for the test case.
I've applied the attached patch to prevent this problem.
regards, tom lane
*** src/backend/optimizer/plan/planner.c.orig Tue Sep 24 14:38:23 2002
--- src/backend/optimizer/plan/planner.c Thu Dec 5 16:32:21 2002
***************
*** 330,339 ****
* nothing will happen after the first time. We do have to be
* careful to copy everything we pull up, however, or risk
* having chunks of structure multiply linked.
*/
subquery->jointree = (FromExpr *)
pull_up_subqueries(subquery, (Node *) subquery->jointree,
! below_outer_join); /*
* Now make a modifiable copy of the subquery that we can run
--- 330,343 ----
* nothing will happen after the first time. We do have to be
* careful to copy everything we pull up, however, or risk
* having chunks of structure multiply linked.
+ *
+ * Note: 'false' is correct here even if we are within an outer
+ * join in the upper query; the lower query starts with a clean
+ * slate for outer-join semantics.
*/
subquery->jointree = (FromExpr *)
pull_up_subqueries(subquery, (Node *) subquery->jointree,
! false); /*
* Now make a modifiable copy of the subquery that we can run
***************
*** 513,518 ****
--- 517,536 ----
* quals of higher queries.
*/
if (expression_returns_set((Node *) subquery->targetList))
+ return false;
+
+ /*
+ * Don't pull up a subquery that has any sublinks in its targetlist,
+ * either. As of PG 7.3 this creates problems because the pulled-up
+ * expressions may go into join alias lists, and the sublinks would
+ * not get fixed because we do flatten_join_alias_vars() too late.
+ * Eventually we should do a complete flatten_join_alias_vars as the
+ * first step of preprocess_expression, and then we could probably
+ * support this. (BUT: it might be a bad idea anyway, due to possibly
+ * causing multiple evaluations of an expensive sublink.)
+ */
+ if (subquery->hasSubLinks &&
+ contain_subplans((Node *) subquery->targetList))
return false;/*
Thanks, patch indeed fixes my original breaking query.
-Matt
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Matthew Gabeler-Lee <mgabelerlee@zycos.com> writes:
-- triggers bug
select * from (select t1.*, (select t2.c from t2 limit 1) as v from t1)
t1v
natural left join t3;
Excellent, thank you for the test case.
I've applied the attached patch to prevent this problem.
Import Notes
Resolved by subject fallback