------=_Part_1646_22548838.1132730407079
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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

------=_Part_1646_22548838.1132730407079
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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.&nbsp; For example, if I create the following objects:<br>
<br>
CREATE TABLE Test1( col1 VARCHAR(200) );<br>
CREATE VIEW Test2 AS SELECT col1 FROM Test1 ORDER BY col1;<br>
<br>
then I do a SELECT col1 AS something FROM Test2;&nbsp; The column identifie=
r comes back as &quot;col1&quot; instead of &quot;something&quot;.<br>
<br>


------=_Part_1646_22548838.1132730407079--

------=_Part_1654_3354235.1132730696889
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

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 (AT) gmail (DOT) com> wrote:
------=_Part_1654_3354235.1132730696889
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Also forgot to mention that this only started occuring in PostgreSQL 8.1. H=
ere is my SELECT version() output:<br>
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&n bsp;&nbsp;&nbsp;&nbsp;&nb=
sp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp ;&nbsp;
version <br>
---------------------------------------------------------------------------=
----------------------------<br>
&nbsp;PostgreSQL 8.1.0 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) =
4.0.1 20050727 (Red Hat 4.0.1-5)<br>
<br><div><span class=3D"gmail_quote">On 11/23/05, <b class=3D"gmail_sendern=
ame">Hayden James</b> &lt;<a href=3D"mailto:hayden.james (AT) gmail (DOT) com">hayden.=
james (AT) gmail (DOT) com</a>&gt; wrote:</span><blockquote class=3D"gmail_quote" styl=
e=3D"border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; =
padding-left: 1ex;">
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.&nbsp; For example, if I create the following objects:<br>
<br>
CREATE TABLE Test1( col1 VARCHAR(200) );<br>
CREATE VIEW Test2 AS SELECT col1 FROM Test1 ORDER BY col1;<br>
<br>
then I do a SELECT col1 AS something FROM Test2;&nbsp; The column identifie=
r comes back as &quot;col1&quot; instead of &quot;something&quot;.<br>
<br>


</blockquote></div><br>

------=_Part_1654_3354235.1132730696889--

Hayden James <hayden.james (AT) gmail (DOT) com> writes:
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

Hayden James <hayden.james (AT) gmail (DOT) com> writes:
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 ----

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