Postgres Pro
Downloads
Home   >   mailing lists

Thread: UNION ALL - Var attno

UNION ALL - Var attno

From
sri harsha
Date:


Hi,

   Assume the following query ,

(SELECT a * 1 , b FROM TABLE_1) UNION ALL (SELECT a *1 , b FROM TABLE_2);

In this query , attribute number of the VARs are 141 and 2 respectively !! What is the reason for this ??

I am trying to implement a FDW , so i need attribute numbers to fetch the respective columns from my data store . Due to this change in attribute numbers in the case of UNION ALL , this query doesn't provide the necessary output .

But in the plan its given as 1 and 2 respectively . So is there a mapping which PG maintains to convert from 141 to the original attribute number ??


Thanks,
Harsha

Re: UNION ALL - Var attno

From
Tom Lane
Date:
sri harsha <sriharsha9992@gmail.com> writes:
>    Assume the following query ,
> (SELECT a * 1 , b FROM TABLE_1) UNION ALL (SELECT a *1 , b FROM TABLE_2);

> In this query , attribute number of the VARs are 141 and 2 respectively !!

I doubt it.

Maybe you're looking at something that's not a Var, possibly an OpExpr,
but assuming it's a Var?  The fact that 141 is the pg_proc OID of int4mul
lends considerable weight to this suspicion ...
        regards, tom lane

Re: UNION ALL - Var attno

From
sri harsha
Date:

Its not an OpExpr . It is a VAR , got it from "reltargetlist" in base relation ( RelOptInfo) . Can you shed some light on where the conversion from 141 to "original" attribute number takes place ??


Regards,
Harsha

On Fri, Apr 29, 2016 at 10:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
sri harsha <sriharsha9992@gmail.com> writes:
>    Assume the following query ,
> (SELECT a * 1 , b FROM TABLE_1) UNION ALL (SELECT a *1 , b FROM TABLE_2);

> In this query , attribute number of the VARs are 141 and 2 respectively !!

I doubt it.

Maybe you're looking at something that's not a Var, possibly an OpExpr,
but assuming it's a Var?  The fact that 141 is the pg_proc OID of int4mul
lends considerable weight to this suspicion ...

                        regards, tom lane

Re: UNION ALL - Var attno

From
Amit Langote
Date:
On Fri, Apr 29, 2016 at 2:42 PM, sri harsha <sriharsha9992@gmail.com> wrote:
>
> Its not an OpExpr . It is a VAR , got it from "reltargetlist" in base
> relation ( RelOptInfo) . Can you shed some light on where the conversion
> from 141 to "original" attribute number takes place ??

As Tom said, you must be looking at an OPEXPR's opfuncid value.
Because that's what I see as being 141.

Thanks,
Amit

Re: UNION ALL - Var attno

From
Ashutosh Bapat
Date:


On Fri, Apr 29, 2016 at 11:12 AM, sri harsha <sriharsha9992@gmail.com> wrote:

Its not an OpExpr . It is a VAR , got it from "reltargetlist" in base relation ( RelOptInfo) . Can you shed some light on where the conversion from 141 to "original" attribute number takes place ??

If you try to print the node as *(Node *) node in a debugger, it will tell you the type of node. What does that print?
 
On Fri, Apr 29, 2016 at 10:03 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
sri harsha <sriharsha9992@gmail.com> writes:
>    Assume the following query ,
> (SELECT a * 1 , b FROM TABLE_1) UNION ALL (SELECT a *1 , b FROM TABLE_2);

> In this query , attribute number of the VARs are 141 and 2 respectively !!

I doubt it.

Maybe you're looking at something that's not a Var, possibly an OpExpr,
but assuming it's a Var?  The fact that 141 is the pg_proc OID of int4mul
lends considerable weight to this suspicion ...

                        regards, tom lane




--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: UNION ALL - Var attno

From
Tom Lane
Date:
sri harsha <sriharsha9992@gmail.com> writes:
> Its not an OpExpr . It is a VAR , got it from "reltargetlist" in base
> relation ( RelOptInfo) .

Read the comment:
*        reltargetlist - List of Var and PlaceHolderVar nodes for the values*                        we need to output
fromthis relation.*                        List is in no particular order, but all rels of an*
appendrelset must use corresponding orders.*                        NOTE: in an appendrel child relation, may contain*
                     arbitrary expressions pulled up from a subquery!
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

In general, it's seldom a good idea to assume that a Node is of a specific
type without having confirmed that with an IsA() check.
        regards, tom lane