Обсуждение: 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; /*