Re: Incorrect column identifer using AS in SELECT statement on a VIEW.

Hayden James <hayden(dot)james(at)gmail(dot)com> writes:
> If I create a normal table and a normal view that queries that table I get
> incorrect results when I query the view using the AS alias in the select
> statement.

Turns out it depends on the plan used for the view, but in the
particular case you were exercising there was indeed a problem.
Patch attached if you need it immediately.

regards, tom lane

*** src/backend/executor/execUtils.c.orig Tue Nov 22 16:06:21 2005
--- src/backend/executor/execUtils.c Wed Nov 23 14:06:10 2005
***************
*** 436,457 ****
}

/* ----------------
- * ExecAssignResultTypeFromOuterPlan
- * ----------------
- */
- void
- ExecAssignResultTypeFromOuterPlan(PlanState *planstate)
- {
- PlanState *outerPlan;
- TupleDesc tupDesc;
-
- outerPlan = outerPlanState(planstate);
- tupDesc = ExecGetResultType(outerPlan);
-
- ExecAssignResultType(planstate, tupDesc, false);
- }
-
- /* ----------------
* ExecAssignResultTypeFromTL
* ----------------
*/
--- 436,441 ----
*** src/backend/executor/nodeHash.c.orig Tue Nov 22 16:06:22 2005
--- src/backend/executor/nodeHash.c Wed Nov 23 14:06:10 2005
***************
*** 164,170 ****
* initialize tuple type. no need to initialize projection info because
* this node doesn't do projections
*/
! ExecAssignResultTypeFromOuterPlan(&hashstate->ps);
hashstate->ps.ps_ProjInfo = NULL;

return hashstate;
--- 164,170 ----
* initialize tuple type. no need to initialize projection info because
* this node doesn't do projections
*/
! ExecAssignResultTypeFromTL(&hashstate->ps);
hashstate->ps.ps_ProjInfo = NULL;

return hashstate;
*** src/backend/executor/nodeLimit.c.orig Fri Oct 14 22:59:44 2005
--- src/backend/executor/nodeLimit.c Wed Nov 23 14:06:11 2005
***************
*** 327,333 ****
* limit nodes do no projections, so initialize projection info for this
* node appropriately
*/
! ExecAssignResultTypeFromOuterPlan(&limitstate->ps);
limitstate->ps.ps_ProjInfo = NULL;

return limitstate;
--- 327,333 ----
* limit nodes do no projections, so initialize projection info for this
* node appropriately
*/
! ExecAssignResultTypeFromTL(&limitstate->ps);
limitstate->ps.ps_ProjInfo = NULL;

return limitstate;
*** src/backend/executor/nodeMaterial.c.orig Fri Oct 14 22:59:44 2005
--- src/backend/executor/nodeMaterial.c Wed Nov 23 14:06:11 2005
***************
*** 195,201 ****
* initialize tuple type. no need to initialize projection info because
* this node doesn't do projections.
*/
! ExecAssignResultTypeFromOuterPlan(&matstate->ss.ps);
ExecAssignScanTypeFromOuterPlan(&matstate->ss);
matstate->ss.ps.ps_ProjInfo = NULL;

--- 195,201 ----
* initialize tuple type. no need to initialize projection info because
* this node doesn't do projections.
*/
! ExecAssignResultTypeFromTL(&matstate->ss.ps);
ExecAssignScanTypeFromOuterPlan(&matstate->ss);
matstate->ss.ps.ps_ProjInfo = NULL;

*** src/backend/executor/nodeSetOp.c.orig Fri Oct 14 22:59:45 2005
--- src/backend/executor/nodeSetOp.c Wed Nov 23 14:06:11 2005
***************
*** 258,264 ****
* setop nodes do no projections, so initialize projection info for this
* node appropriately
*/
! ExecAssignResultTypeFromOuterPlan(&setopstate->ps);
setopstate->ps.ps_ProjInfo = NULL;

/*
--- 258,264 ----
* setop nodes do no projections, so initialize projection info for this
* node appropriately
*/
! ExecAssignResultTypeFromTL(&setopstate->ps);
setopstate->ps.ps_ProjInfo = NULL;

/*
*** src/backend/executor/nodeSort.c.orig Fri Oct 14 22:59:45 2005
--- src/backend/executor/nodeSort.c Wed Nov 23 14:06:12 2005
***************
*** 193,199 ****
* initialize tuple type. no need to initialize projection info because
* this node doesn't do projections.
*/
! ExecAssignResultTypeFromOuterPlan(&sortstate->ss.ps);
ExecAssignScanTypeFromOuterPlan(&sortstate->ss);
sortstate->ss.ps.ps_ProjInfo = NULL;

--- 193,199 ----
* initialize tuple type. no need to initialize projection info because
* this node doesn't do projections.
*/
! ExecAssignResultTypeFromTL(&sortstate->ss.ps);
ExecAssignScanTypeFromOuterPlan(&sortstate->ss);
sortstate->ss.ps.ps_ProjInfo = NULL;

*** src/backend/executor/nodeUnique.c.orig Tue Nov 22 16:06:22 2005
--- src/backend/executor/nodeUnique.c Wed Nov 23 14:06:12 2005
***************
*** 150,156 ****
* unique nodes do no projections, so initialize projection info for this
* node appropriately
*/
! ExecAssignResultTypeFromOuterPlan(&uniquestate->ps);
uniquestate->ps.ps_ProjInfo = NULL;

/*
--- 150,156 ----
* unique nodes do no projections, so initialize projection info for this
* node appropriately
*/
! ExecAssignResultTypeFromTL(&uniquestate->ps);
uniquestate->ps.ps_ProjInfo = NULL;

/*
*** src/include/executor/executor.h.orig Fri Oct 14 23:00:27 2005
--- src/include/executor/executor.h Wed Nov 23 14:06:04 2005
***************
*** 218,224 ****
extern void ExecAssignExprContext(EState *estate, PlanState *planstate);
extern void ExecAssignResultType(PlanState *planstate,
TupleDesc tupDesc, bool shouldFree);
- extern void ExecAssignResultTypeFromOuterPlan(PlanState *planstate);
extern void ExecAssignResultTypeFromTL(PlanState *planstate);
extern TupleDesc ExecGetResultType(PlanState *planstate);
extern ProjectionInfo *ExecBuildProjectionInfo(List *targetList,
--- 218,223 ----