Query breaking with unknown expression type (lost s

[Matthew Gabeler-Lee]

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:t...@sss.pgh.pa.us]
Sent: Wednesday, December 04, 2002 16:37
To: Matthew Gabeler-Lee
Cc: pgsql-...@postgresql.org
Subject: Re: [GENERAL] Query breaking with unknown expression type (lost
subquery from v iew?)

Matthew Gabeler-Lee <mgabe...@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Matthew Gabeler-Lee <mgabe...@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;

/*

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majo...@postgresql.org)

[Matthew Gabeler-Lee]

Thanks, patch indeed fixes my original breaking query.

-Matt

-----Original Message-----
From: Tom Lane [mailto:t...@sss.pgh.pa.us]

Matthew Gabeler-Lee <mgabe...@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.

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org