UNION ALL returns NULL columns

[sri harsha]

Hi ,
     
       Assume I have the following tables and execute UNION ALL with/without expressions in projection in (9.3 or 9.4)  and 9.5 . These are the following outputs.
           
 PG 9.3 or 9.4

test=# create foreign table test_union_1 ( a int , b int ) server cstore_server ;
CREATE FOREIGN TABLE
test=# insert into test_union_1 select 1,10;
INSERT 0 1
test=# insert into test_union_1 select 2,20;
INSERT 0 1
test=# insert into test_union_1 select 3,30;
INSERT 0 1
test=# create foreign table test_union_2 ( a int , b int ) server cstore_server ;
CREATE FOREIGN TABLE
test=# insert into test_union_2 select 4,40;
INSERT 0 1
test=# insert into test_union_2 select 5,50;
INSERT 0 1
test=# insert into test_union_2 select 6,60;
INSERT 0 1
test=# Select a,b from test_union_1 union all Select a,b from test_union_2;
 a | b 
---+----
 1 | 10
 2 | 20
 3 | 30
 4 | 40
 5 | 50
 6 | 60
(6 rows)

test=# Select a*1,b from test_union_1 union all Select a*1,b from test_union_2;
 ?column? | b 
----------+----
          | 10
          | 20
          | 30
          | 40
          | 50
          | 60
(6 rows)

test=# Select a*1,b from test_union_1 union Select a*1,b from test_union_2;
 ?column? | b 
----------+----
        1 | 10
        3 | 30
        6 | 60
        2 | 20
        4 | 40
        5 | 50
(6 rows)

PG 9.5

test=# create foreign table test_union_1 ( a int , b int ) server cstore_server ;
CREATE FOREIGN TABLE
test=# insert into test_union_1 select 1,10;
INSERT 0 1
test=# insert into test_union_1 select 2,20;
INSERT 0 1
test=# insert into test_union_1 select 3,30;
INSERT 0 1
test=# create foreign table test_union_2 ( a int , b int ) server cstore_server ;
CREATE FOREIGN TABLE
test=# insert into test_union_2 select 4,40;
INSERT 0 1
test=# insert into test_union_2 select 5,50;
INSERT 0 1
test=# insert into test_union_2 select 6,60;
INSERT 0 1
test=# Select a,b from test_union_1 union all Select a,b from test_union_2;
 a | b 
---+----
 1 | 10
 2 | 20
 3 | 30
 4 | 40
 5 | 50
 6 | 60
(6 rows)

test=# Select a*1,b from test_union_1 union all Select a*1,b from test_union_2;
 ?column? | b 
----------+----
        1 | 10
        3 | 30
        6 | 60
        2 | 20
        4 | 40
        5 | 50
(6 rows)

test=# Select a*1,b from test_union_1 union Select a*1,b from test_union_2;
 ?column? | b 
----------+----
        1 | 10
        3 | 30
        6 | 60
        2 | 20
        4 | 40
        5 | 50
(6 rows)


          This is because , in CSTORE , the projecting list is generated from fdw_private present in ForeignScanState . In 9.5, PG provides both the columns in fdw_private , but when it comes 9.3 or 9.4 only "COLUMN b" would be present in the fdw_private list.
Any inputs on how to solve this !! Is this a "POSTGRESQL-FOREIGN DATA WRAPPER" issue or a "CSTORE" one ??


Thanks,
Harsha  

[Murat Tuncer]

I do not know if you have to use a*1 notation.

you can possibly workaround on it by using a subquery.

 Select a*1, b from (Select a,b from test_union_1 union all Select a,b from test_union_2) all_result;

It looks like it an issue with postgresql, but as you said it is already fixed in 9.5. 

--
You received this message because you are subscribed to the Google Groups "cstore users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cstore-users...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--

Murat Tuncer
Software Engineer | Citus Data
mtu...@citusdata.com

[sri harsha]

Hi Murat ,

      Actually I made a mistake while testing the query . It is a CStore issue .

Query 1:
 
(SELECT A*1 , B FROM TEST_UNION_1 LIMIT 10) UNION ALL (SELECT A*1 , B FROM TEST_UNION_2 LIMIT 10);

Query 2:
 
(SELECT A*1 , B FROM TEST_UNION_1 ) UNION ALL (SELECT A*1 , B FROM TEST_UNION_2 );

Query 1 works fine but Query 2 returns NULL columns . This is because in "ColumnList" function has the following code snippet

if (neededColumn->varattno == columnIndex)

{

column = neededColumn;

break;

}

 
Query 1 --- varattno is 1,2 for a,b
Query 2 --- varattno is 141,2 for a,b

So CStore doesn't consider Column "a" and hence the NULL values.



Thanks,
Harsha

[Murat Tuncer]

We are tracking this issue at https://github.com/citusdata/cstore_fdw/issues/95 for further investigation and fix.