Re: inner query bug

Andrew Holm-Hansen <andrew(at)einer(dot)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);