Re: Query breaking with unknown expression type (lost s

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;

      /*