Re: inner query bug - Mailing list pgsql-bugs

I tried copying the text of the patch into a file and running patch on
it, but apparently I'm somehow impaired.  Manually patched the relevant
files, and it appears to be working just fine.

Thanks for all your help, it was really much more than I expected.

Andrew Holm-Hansen
andrew@einer.org

On Tue, 2003-11-25 at 13:11, Tom Lane wrote:
> Andrew Holm-Hansen <andrew@einer.org> writes:
> > SC2test7=# select * from txstatus,tx txx where txstatus.txid = txx.txid
> > AND txstatus.statuschangetime = (select max(txstatus.statuschangetime)
> > from txstatus where txstatus.txid = txx.txid); FATAL:  terminating
> > connection due to administrator command
>
> The "due to administrator command" bit leaves me still scratching my
> head a little.  AFAICS that message can only appear in response to a
> SIGTERM signal.  You might want to root around and see if there is
> anything in your system that might try to SIGTERM backends unexpectedly.
>
> > select * from txstatus,tx txx where txstatus.txid = txx.txid AND
> > txstatus.statuschangetime = (select max(txstatus.statuschangetime) from
> > txstatus where txstatus.txid = txx.txid);
> > ERROR:  variable not found in subplan target list
>
> This behavior, however, I do understand now; including the fact that
> it's not very repeatable.  Your test case involved enough tables (after
> view expansion) to result in GEQO planning being invoked, which would
> cause the selected plan to vary somewhat randomly.  The failure would
> occur if a SubPlan got used in a clause of a hash join --- and the
> symptoms would be different depending on whether it was on the inner or
> outer side of the join.  This problem is new in 7.4 because prior
> releases wouldn't consider hash join for join clauses any more
> complicated than "var = var".
>
> I plan to apply the attached minimal patch to 7.4 branch, and a more
> extensive cleanup to HEAD.
>
>             regards, tom lane
>
> *** src/backend/executor/nodeHashjoin.c.orig    Thu Sep 25 02:57:59 2003
> --- src/backend/executor/nodeHashjoin.c    Tue Nov 25 13:59:45 2003
> ***************
> *** 417,423 ****
>        */
>       hjstate->hj_InnerHashKeys = (List *)
>           ExecInitExpr((Expr *) hashNode->hashkeys,
> !                      innerPlanState(hjstate));
>       ((HashState *) innerPlanState(hjstate))->hashkeys =
>           hjstate->hj_InnerHashKeys;
>
> --- 417,423 ----
>        */
>       hjstate->hj_InnerHashKeys = (List *)
>           ExecInitExpr((Expr *) hashNode->hashkeys,
> !                      (PlanState *) hjstate);
>       ((HashState *) innerPlanState(hjstate))->hashkeys =
>           hjstate->hj_InnerHashKeys;
>
> *** src/backend/optimizer/plan/createplan.c.orig    Wed Aug 27 08:44:12 2003
> --- src/backend/optimizer/plan/createplan.c    Tue Nov 25 14:00:42 2003
> ***************
> *** 1093,1104 ****
>       hashclauses = order_qual_clauses(root, hashclauses);
>
>       /*
> !      * Extract the inner hash keys (right-hand operands of the
> !      * hashclauses) to put in the Hash node.
>        */
>       innerhashkeys = NIL;
>       foreach(hcl, hashclauses)
> !         innerhashkeys = lappend(innerhashkeys, get_rightop(lfirst(hcl)));
>
>       /* We don't want any excess columns in the hashed tuples */
>       disuse_physical_tlist(inner_plan, best_path->jpath.innerjoinpath);
> --- 1093,1106 ----
>       hashclauses = order_qual_clauses(root, hashclauses);
>
>       /*
> !      * Extract the inner hash keys (right-hand operands of the hashclauses)
> !      * to put in the Hash node.  Must do a deep copy in case there are
> !      * subplans in the hash keys.
>        */
>       innerhashkeys = NIL;
>       foreach(hcl, hashclauses)
> !         innerhashkeys = lappend(innerhashkeys,
> !                                 copyObject(get_rightop(lfirst(hcl))));
>
>       /* We don't want any excess columns in the hashed tuples */
>       disuse_physical_tlist(inner_plan, best_path->jpath.innerjoinpath);