Incorrect column identifer using AS in SELECT statement on a VIEW.
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. For example, if I create the following objects:
CREATE TABLE Test1( col1 VARCHAR(200) );
CREATE VIEW Test2 AS SELECT col1 FROM Test1 ORDER BY col1;
then I do a SELECT col1 AS something FROM Test2; The column identifier
comes back as "col1" instead of "something".
Also forgot to mention that this only started occuring in PostgreSQL 8.1.
Here is my SELECT version() output:
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 8.1.0 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC)
4.0.120050727 (Red Hat
4.0.1-5)
Show quoted text
On 11/23/05, Hayden James <hayden.james@gmail.com> wrote:
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. For example, if I create the following objects:CREATE TABLE Test1( col1 VARCHAR(200) );
CREATE VIEW Test2 AS SELECT col1 FROM Test1 ORDER BY col1;then I do a SELECT col1 AS something FROM Test2; The column identifier
comes back as "col1" instead of "something".
Hayden James <hayden.james@gmail.com> writes:
then I do a SELECT col1 AS something FROM Test2; The column identifier
comes back as "col1" instead of "something".
Hmm, we fixed a problem just like this last month ... seems we missed
some cases though ...
regards, tom lane
Hayden James <hayden.james@gmail.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 ----