PostgreSQL Bugs - Incorrect column identifer using AS in SELECT statement on a VIEW.

Author

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

Hayden James

2005-11-23, 7:24 am

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

2005-11-23, 7:24 am

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)

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".
>
>

Tom Lane

2005-11-23, 1:33 pm

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tom Lane

2005-11-23, 8:24 pm

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 ****
}

/* ----------------
- * ExecAssignResultT
ypeFromOuterPlan
- * ----------------
- */
- void
- ExecAssignResultType
FromOuterPlan(PlanSt
ate *planstate)
- {
- PlanState *outerPlan;
- TupleDesc tupDesc;
-
- outerPlan = outerPlanState(plans
tate);
- tupDesc = ExecGetResultType(ou
terPlan);
-
- ExecAssignResultTyp
e(planstate, tupDesc, false);
- }
-
- /* ----------------
* ExecAssignResultT
ypeFromTL
* ----------------
*/
--- 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
*/
! ExecAssignResultTyp
eFromOuterPlan(&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
*/
! ExecAssignResultTyp
eFromTL(&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
*/
! ExecAssignResultTyp
eFromOuterPlan(&limitstate->ps);
limitstate->ps.ps_ProjInfo = NULL;

return limitstate;
--- 327,333 ----
* limit nodes do no projections, so initialize projection info for this
* node appropriately
*/
! ExecAssignResultTyp
eFromTL(&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.
*/
! ExecAssignResultTyp
eFromOuterPlan(&matstate->ss.ps);
ExecAssignScanTypeFr
omOuterPlan(&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.
*/
! ExecAssignResultTyp
eFromTL(&matstate->ss.ps);
ExecAssignScanTypeFr
omOuterPlan(&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
*/
! ExecAssignResultTyp
eFromOuterPlan(&setopstate->ps);
setopstate->ps.ps_ProjInfo = NULL;

/*
--- 258,264 ----
* setop nodes do no projections, so initialize projection info for this
* node appropriately
*/
! ExecAssignResultTyp
eFromTL(&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.
*/
! ExecAssignResultTyp
eFromOuterPlan(&sortstate->ss.ps);
ExecAssignScanTypeFr
omOuterPlan(&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.
*/
! ExecAssignResultTyp
eFromTL(&sortstate->ss.ps);
ExecAssignScanTypeFr
omOuterPlan(&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
*/
! ExecAssignResultTyp
eFromOuterPlan(&uniquestate->ps);
uniquestate->ps.ps_ProjInfo = NULL;

/*
--- 150,156 ----
* unique nodes do no projections, so initialize projection info for this
* node appropriately
*/
! ExecAssignResultTyp
eFromTL(&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 ExecAssignExprContex
t(EState *estate, PlanState *planstate);
extern void ExecAssignResultType
(PlanState *planstate,
TupleDesc tupDesc, bool shouldFree);
- extern void ExecAssignResultType
FromOuterPlan(PlanSt
ate *planstate);
extern void ExecAssignResultType
FromTL(PlanState *planstate);
extern TupleDesc ExecGetResultType(Pl
anState *planstate);
extern ProjectionInfo *ExecBuildProjection
Info(List *targetList,
--- 218,223 ----

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links