Query problem using case with sum from subquery

Hi,

I am running into a problem with trying to do a CASE statement within a query using summed values from a subquery. Please see SQL example and exception log from server below. (Build is from CVS with debuginfo enabled). Hope you can help.

Thanks,

Ranga

java.sql.SQLException: ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
        at com.edb.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2933)
        at com.edb.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2548)
        at com.edb.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:782)
        at com.edb.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:519)
        at com.edb.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:422)
        at com.edb.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:272)
        at com.edb.gridsql.engine.NodeProducerThread.executeQuery(NodeProducerThread.java:698)
        at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:615)
        at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
        at java.lang.Thread.run(Thread.java:637)
2010-04-29 12:05:56,762 - ERROR Throwing throwable:
com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
        at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
        at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
        at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
        at java.lang.Thread.run(Thread.java:637)
2010-04-29 12:05:56,762 - ERROR Catching throwable:
com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
        at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
        at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
        at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
        at java.lang.Thread.run(Thread.java:637)
2010-04-29 12:05:56,762 - ERROR Throwing throwable:
com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
        at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
        at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
        at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
        at java.lang.Thread.run(Thread.java:637)
2010-04-29 12:05:56,773 - ERROR Catching throwable:

2010-04-29 12:05:56,773 - ERROR Catching throwable:
com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
        at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
        at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
        at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
        at java.lang.Thread.run(Thread.java:637)
2010-04-29 12:05:56,774 - ERROR Throwing throwable:
com.edb.gridsql.exception.XDBMessageMonitorException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
        at com.edb.gridsql.engine.MessageMonitor.checkMessages(MessageMonitor.java:245)
        at com.edb.gridsql.engine.MultinodeExecutor.executeStep(MultinodeExecutor.java:931)
        at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:533)
        at com.edb.gridsql.queryproc.QueryProcessor.executeQueryExecPlan(QueryProcessor.java:190)
        at com.edb.gridsql.queryproc.QueryProcessor.execute(QueryProcessor.java:1071)
        at com.edb.gridsql.parser.SqlSelect.execute(SqlSelect.java:438)
        at com.edb.gridsql.engine.ExecutableRequest.execute(ExecutableRequest.java:244)
        at com.edb.gridsql.engine.XDBSessionContext.executeRequest(XDBSessionContext.java:1037)
        at com.edb.gridsql.engine.ServerStatement.execute(ServerStatement.java:201)
        at com.edb.gridsql.engine.ServerStatement.describe(ServerStatement.java:145)
        at com.edb.gridsql.engine.XDBSessionContext.describeStatement(XDBSessionContext.java:914)
        at com.edb.gridsql.protocol.PgProtocolSession.run(PgProtocolSession.java:659)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:637)
Caused by: com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
        at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
        at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
        at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
        ... 1 more

2010-04-29 12:05:56,774 - ERROR Catching throwable:
com.edb.gridsql.exception.XDBServerException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
QUERY: SELECT "TempTableGenerated3"."f2" as "f2", ( CASE   WHEN ( ("TempTableGenerated3"."sum1" = 0) )  THEN ( 'Zero' )   else 'NonZero' end) as "sumdesc" FROM "t1" "TempTableGenerated3"  group by "TempTableGenerated3"."f2"
        at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:538)
        at com.edb.gridsql.queryproc.QueryProcessor.executeQueryExecPlan(QueryProcessor.java:190)
        at com.edb.gridsql.queryproc.QueryProcessor.execute(QueryProcessor.java:1071)
        at com.edb.gridsql.parser.SqlSelect.execute(SqlSelect.java:438)
        at com.edb.gridsql.engine.ExecutableRequest.execute(ExecutableRequest.java:244)
        at com.edb.gridsql.engine.XDBSessionContext.executeRequest(XDBSessionContext.java:1037)
        at com.edb.gridsql.engine.ServerStatement.execute(ServerStatement.java:201)
        at com.edb.gridsql.engine.ServerStatement.describe(ServerStatement.java:145)
        at com.edb.gridsql.engine.XDBSessionContext.describeStatement(XDBSessionContext.java:914)
        at com.edb.gridsql.protocol.PgProtocolSession.run(PgProtocolSession.java:659)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
        at java.lang.Thread.run(Thread.java:637)
Caused by: com.edb.gridsql.exception.XDBMessageMonitorException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
        at com.edb.gridsql.engine.MessageMonitor.checkMessages(MessageMonitor.java:245)
        at com.edb.gridsql.engine.MultinodeExecutor.executeStep(MultinodeExecutor.java:931)
        at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:533)
        ... 12 more
Caused by: com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61
        at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
        at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
        at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
        ... 1 more
2010-04-29 12:05:56,774 - ERROR Throwing throwable:
com.edb.gridsql.exception.XDBServerException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: column TempTableGenerated3.sum1 does not exist
  Position: 61

Hi Ranga,

FYI
We have just fixed the bug and checked in changes to CVS.
Thanks for reporting it.

Andrei

Hi Andrei,

I picked up the changes and built and tested again - The original query I posted works. However, I am running into another issue - (which I can work around but I thought I would let you know)

GridSQL -> create table t1 (f1 varchar(10), f2 int, f3 int);

insert into t1 values ('group1', 100, 10);
insert into t1 values ('group1', 100, 10);
insert into t1 values ('group2', null, 200);
insert into t1 values ('group1', null, 200);
OK

GridSQL -> GridSQL -> GridSQL -> 1 row(s) affected

GridSQL -> 1 row(s) affected

GridSQL -> select * from t1;
+--------------------+
|   f1   |  f2 |  f3 |
+--------------------+
| group1 | 100 |  10 |
| group1 | 100 |  10 |
| group2 |     | 200 |
| group1 |     | 200 |
+--------------------+
4 row(s).

--
-- And this works 
--
GridSQL -> select SUBQ.f1, sum(SUBQ.f2) as f2sum, sum(SUBQ.f3) / (case when sum(SUBQ.f2) = 0 then null else sum(SUBQ.f2) end) as sumF3WithNullCheck 
    FROM  t1 SUBQ  
    group by SUBQ.f1 
GridSQL -> GridSQL -> GridSQL -> ;
+-------------------------------------+
|   f1   | f2sum | sumf3withnullcheck |
+-------------------------------------+
| group2 |       |                    |
| group1 |   200 |                  1 |
+-------------------------------------+
2 row(s).

--
-- But if I try to do the select from a sub-query it doesn't work 
--
GridSQL -> select SUBQ.f1, sum(SUBQ.f2) as f2sum, sum(SUBQ.f3) / (case when sum(SUBQ.f2) = 0 then null else sum(SUBQ.f2) end) as sumF3WithNullCheck 
    FROM  (select f1, f2, f3 from t1) SUBQ  
    group by SUBQ.f1 
GridSQL -> GridSQL -> GridSQL -> ;
SQLException: ERROR: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: column TMPTT6_2.f3 does not exist
  Position: 56
QUERY: SELECT "XCOL1" as "f1", SUM("XCOL2") as "f2sum", (sum( "TMPTT6_2"."f3")  / ( CASE   WHEN ( (sum( "TMPTT6_2"."f2")  = 0) )  THEN ( null )   else sum( "TMPTT6_2"."f2")  end)) as "sumf3withnullcheck" FROM "TMPTT6_2"  group by "XCOL1", "XCOL1"

I can work around by avoiding the sub-query (the query itself is generated by a program) - Here is the exception seen in the console log.

2010-05-05 19:46:41,405 - ERROR Catching throwable: 
com.edb.gridsql.exception.XDBServerException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: column TMPTT6_2.f3 does not exist
  Position: 56
QUERY: SELECT "XCOL1" as "f1", SUM("XCOL2") as "f2sum", (sum( "TMPTT6_2"."f3")  / ( CASE   WHEN ( (sum( "TMPTT6_2"."f2")  = 0) )  THEN ( null )   else sum( "TMPTT6_2"."f2")  end)) as "sumf3withnullcheck" FROM "TMPTT6_2"  group by "XCOL1", "XCOL1"
        at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:538)
        at com.edb.gridsql.queryproc.QueryProcessor.executeQueryExecPlan(QueryProcessor.java:190)
        at com.edb.gridsql.queryproc.QueryProcessor.execute(QueryProcessor.java:1071)
        at com.edb.gridsql.parser.SqlSelect.execute(SqlSelect.java:438)
        at com.edb.gridsql.engine.ExecutableRequest.execute(ExecutableRequest.java:244)
        at com.edb.gridsql.engine.XDBSessionContext.executeRequest(XDBSessionContext.java:1037)
        at com.edb.gridsql.engine.ServerStatement.execute(ServerStatement.java:201)         at com.edb.gridsql.engine.ServerStatement.describe(ServerStatement.java:145)
        at com.edb.gridsql.engine.XDBSessionContext.describeStatement(XDBSessionContext.java:914)
        at com.edb.gridsql.protocol.PgProtocolSession.run(PgProtocolSession.java:659)
        at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:885)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:907)
        at java.lang.Thread.run(Thread.java:619)
Caused by: com.edb.gridsql.exception.XDBMessageMonitorException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: column TMPTT6_2.f3 does not exist
  Position: 56
        at com.edb.gridsql.engine.MessageMonitor.checkMessages(MessageMonitor.java:245)
        at com.edb.gridsql.engine.MultinodeExecutor.executeStep(MultinodeExecutor.java:931)
        at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:533)
        ... 12 more
Caused by: com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: column TMPTT6_2.f3 does not exist
  Position: 56        at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
        at com.edb.gridsql.engine.NodeProducerThread.processStep(NodeProducerThread.java:617)
        at com.edb.gridsql.engine.NodeProducerThread.run(NodeProducerThread.java:297)
        ... 1 more

Regards,

Ranga

Hi Andrei,

I grabbed the latest source this morning and tried out with that - I noticed that a related test case fails - The sum/case combination that I described earlier in this message is in some cases being done from a union of two or more tables. In that scenario, I need something like the provided test case below to work - (and it currently doesn't). Please see details below.

Thanks,

Ranga

GridSQL -> create table rol1 (f1 varchar(10), f2 int);
OK

GridSQL -> create table rol2 (f1 varchar(10), f3 int);
OK

GridSQL -> insert into rol1 values ('group1', 100);
insert into rol1 values ('group1', 10);
insert into rol2 values ('group1', 200);
insert into rol2 values ('group1', 30);1 row(s) affected

GridSQL -> 1 row(s) affected

GridSQL -> 
1 row(s) affected

GridSQL -> select * from rol1;
+--------------+
|   f1   |  f2 |
+--------------+
| group1 | 100 |
| group1 |  10 |
+--------------+
2 row(s).

GridSQL -> select * from rol2;
+--------------+
|   f1   |  f3 |
+--------------+
| group1 | 200 |
| group1 |  30 |
+--------------+
2 row(s).

--
-- this works directly on Postgres
-- 
GridSQL -> execute direct on all 'select bb.f1, sum(bb.f2col) as sumcol from (select f1, f2 as f2col, null as f3col from rol1 union all select f1, null as f2col, f3 as f3col from rol2) bb group by bb.f1';
+------------------------+
|   f1   | sumcol | Node |
+------------------------+
| group1 |    110 |    1 |
+------------------------+
1 row(s).

--
-- This doesn't work with GridSQL
--
GridSQL ->  select bb.f1, sum(bb.f2col) as sumcol from (select f1, f2 as f2col, null as f3col from rol1 union all select f1, null as f2col, f3 as f3col from rol2) bb group by bb.f1;
SQLException: ERROR: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near ")"
  Position: 68
QUERY: SELECT "bb"."f1" AS "f1","bb"."f2" AS "f2col",null AS "f3col" FROM "rol1" "bb"

This fails with the following exception in the log:

2010-05-06 13:28:09,875 - ERROR Catching throwable: 
java.sql.SQLException: ERROR: syntax error at or near ")"
  Position: 68
        at com.edb.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2933)
        at com.edb.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2548)
        at com.edb.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:782)
        at com.edb.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:519)
        at com.edb.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:422)
        at com.edb.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:318)
        at com.edb.gridsql.engine.NodeThread.doProcessSqlCommand(NodeThread.java:505)
        at com.edb.gridsql.engine.NodeThread.processSqlCommand(NodeThread.java:531)
        at com.edb.gridsql.engine.NodeThread.initStep(NodeThread.java:572)
        at com.edb.gridsql.engine.NodeThread.run(NodeThread.java:1234)
        at java.lang.Thread.run(Thread.java:637)
2010-05-06 13:28:09,890 - ERROR Throwing throwable: 
com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: syntax error at or near ")"
  Position: 68
        at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
        at com.edb.gridsql.engine.NodeThread.processSqlCommand(NodeThread.java:533)
        at com.edb.gridsql.engine.NodeThread.initStep(NodeThread.java:572)
        at com.edb.gridsql.engine.NodeThread.run(NodeThread.java:1234)
        at java.lang.Thread.run(Thread.java:637)
2010-05-06 13:28:09,890 - ERROR Catching throwable:
com.edb.gridsql.exception.XDBWrappedSQLException: java.sql.SQLException : ERROR: syntax error at or near ")"
  Position: 68
        at com.edb.gridsql.engine.NodeThread.handleSqlException(NodeThread.java:1829)
        at com.edb.gridsql.engine.NodeThread.processSqlCommand(NodeThread.java:533)
        at com.edb.gridsql.engine.NodeThread.initStep(NodeThread.java:572)
        at com.edb.gridsql.engine.NodeThread.run(NodeThread.java:1234)
        at java.lang.Thread.run(Thread.java:637)
2010-05-06 13:28:09,890 - ERROR Throwing throwable:
com.edb.gridsql.exception.XDBMessageMonitorException: Node 1 has aborted execution, cause is: java.sql.SQLException : ERROR: syntax error at or near ")"
  Position: 68
        at com.edb.gridsql.engine.MessageMonitor.waitForMessages(MessageMonitor.java:185)
        at com.edb.gridsql.engine.MultinodeExecutor.executeStep(MultinodeExecutor.java:852)
        at com.edb.gridsql.queryproc.QueryProcessor.executeQueryStep(QueryProcessor.java:533)

Hi Ranga,

Sorry, last checkin introduced a regression.
Now it is fixed.
Sorry for the inconvenience.

Thanks,
Andrei

Hi Andrei,

I tested with the latest and I'm super thrilled that the query in all its complexity works now! - See full example below.

However, I am running into a related parser issue - in this scenario - The query works fine via gs-cmdline.sh - However, fails via JDBC when I specify the value range for AGLIM.f2col using parameters to a prepared statement. I can work around it by hardcoding the values but would prefer to use the PreparedStatement with parameters so that hopefully the repeated parsing of the queries can be avoided.

See code example below:

System.out.println("testRollupQuery: Doing loadBlob with passed in connection.");
        
        String sqlQuery = "SELECT * FROM ( SELECT f1, ((SUM(f2))) AS f2col, ((((SUM(f3)))) / (CASE WHEN ((SUM(f2))) = 0 THEN NULL ELSE ((SUM(f2))) END)) AS f3val FROM (SELECT t1.f1 AS f1, t1.f2 AS f2, t1.f3 AS f3 FROM t1 ) SUBQ GROUP BY f1 ) AGLIM WHERE (AGLIM.f2col >= 0 AND AGLIM.f2col < 10000) ORDER BY f2col DESC limit 4";
        
        Statement stmt = conn.createStatement();
        
        ResultSet rs = stmt.executeQuery(sqlQuery);
        
        // This works fine - with no parameters
        while (rs.next()) {
            System.out.println("F1 = " + rs.getString(1) + ", " + "F2 = " + rs.getString(2)
            		 +  "F3 = " + rs.getString(3));
        }
        rs.close();

stmt.close();
        
        String queryWithParam  = "SELECT * FROM ( SELECT f1, ((SUM(f2))) AS f2col, ((((SUM(f3)))) / (CASE WHEN ((SUM(f2))) = 0 THEN NULL ELSE ((SUM(f2))) END)) AS f3val FROM (SELECT t1.f1 AS f1, t1.f2 AS f2, t1.f3 AS f3 FROM t1 ) SUBQ GROUP BY f1 ) AGLIM WHERE (AGLIM.f2col >= ? AND AGLIM.f2col < ?) ORDER BY f2col DESC limit 4";
        
        PreparedStatement pStmt = conn.prepareStatement(queryWithParam);
        
        pStmt.setObject(1, new Integer(0));
        pStmt.setObject(2, new Integer(10000));

// Same query with parameters fails -         
        rs = pStmt.executeQuery();
        while (rs.next()) {
            System.out.println("F1 = " + rs.getString(1) + ", " + "F2 = " + rs.getString(2)
            		 +  "F3 = " + rs.getString(3));
        }
        
        rs.close();
        pStmt.close();

Output from the program:

F1 = group1, F2 = 200F3 = 1.1000000000000001
F1 = group2, F2 = 0F3 = null
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: Table aglim has not been found in database ciqdb
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
	at com.carrieriq.m2m.platform.mmp3.db.PostgresDbTest.testRollupQuery(PostgresDbTest.java:284)
	at com.carrieriq.m2m.platform.mmp3.db.PostgresDbTest.main(PostgresDbTest.java:58)

Output in console log:

Hi Andrei,

Other than the JDBC param invocation issue mentioned above, one additional issue remains - the union subquery does not work as described a couple of messages above - I mean a query with a union in the subquery fails as below. - I have attached an exception trace a couple of post above.

Regards,

Ranga

Hi Ranga,

We have checked in few bug fixes, that should resolve problems you reported here.
Note, for some queries GridSQL can not determine data types of expression, causing query to fail.
The query select bb.f1, sum(bb.f2col) as sumcol from (select f1, f2 as f2col, null as f3col from rol1 union all select f1, null as f2col, f3 as f3col from rol2) bb group by bb.f1; is the case.
To workaround this problem explicit typecast should be used:
select bb.f1, sum(bb.f2col) as sumcol from (select f1, f2 as f2col, null::int as f3col from rol1 union all select f1, null::int as f2col, f3 as f3col from rol2) bb group by bb.f1;

Thanks,
Andrei

Hi Andrei,

I updated to the latest from the source and tried - and with your suggestion about the type cast I was able to run the union query successfully. Thanks for that - though I ran into a 10 param limit issue - I posted another message about it.

Regards,

Ranga