Thread: 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)

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