ERROR: each UNION query must have the same number of columns

[Alvin Peng]

select * from
(select stime, val, 0::bigint as val2, t1, t2 from tableA
union
select stime, val, val2, ''::text as t1, ''::text as t2 from tableB) t
order by stime;
got "ERROR: each UNION query must have the same number of columns",

while
select stime, val, 0::bigint as val2, t1, t2 from tableA
union
select stime, val, val2, ''::text as t1, ''::text as t2 from tableB
is ok.

seems the query plan has some bug? Below are logs:

###################

xtest=> show table tableA;
 COLUMN_NAME | SQL_DATA_TYPE | TYPE_NAME | IS_NULLABLE | KEY | DEFAULT
-------------+---------------+-----------+-------------+-----+---------
 id | 4 | INTEGER | YES | NO |
 stime | 93 | TIMESTAMP | YES | NO |
 val | -5 | BIGINT | YES | NO |
 t1 | 2005 | CLOB | YES | NO |
 t2 | 2005 | CLOB | YES | NO |
(5 rows)

xtest=> show table tableB;
 COLUMN_NAME | SQL_DATA_TYPE | TYPE_NAME | IS_NULLABLE | KEY | DEFAULT
-------------+---------------+-----------+-------------+-----+---------
 id | 4 | INTEGER | YES | NO |
 stime | 93 | TIMESTAMP | YES | NO |
 val | -5 | BIGINT | YES | NO |
 val2 | -5 | BIGINT | YES | NO |
(4 rows)

xtest=> select * from tableA;
 id | stime | val | t1 | t2
----+-------------------------+-------+-------+-------
  1 | 2013-10-16 16:08:30.203 | 12345 | text1 | text2
(1 row)

xtest=> select * from tableB;
 id | stime | val | val2
----+-------------------------+-------+------------
  1 | 2013-10-16 16:09:05.078 | 67890 | 1234567890
(1 row)

xtest=> select stime, val, 0::bigint as val2, t1, t2 from tableA union select stime, val, val2, ''::text as t1, ''::text as t2 from tableB;
          stime | val | val2 | t1 | t2
-------------------------+-------+------------+-------+-------
 2013-10-16 16:08:30.203 | 12345 | 0 | text1 | text2
 2013-10-16 16:09:05.078 | 67890 | 1234567890 | |
(2 rows)

xtest=> select * from (select stime, val, 0::bigint as val2, t1, t2 from tableA union select stime, val, val2, ''::text as t1, ''::text as t2 from tableB) t order by stime;
ERROR: each UNION query must have the same number of columns

###################

The same SQL executes without problem in PostgreSQL:

__xtest__N3=# select *from (select stime, val, 0::bigint as val2, t1, t2 from tableA union select stime, val, val2, ''::text as t1, ''::text as t2 from tableB) a order by stime;
          stime | val | val2 | t1 | t2
-------------------------+-------+------------+-------+-------
 2013-10-16 16:08:30.203 | 12345 | 0 | text1 | text2
 2013-10-16 16:09:05.078 | 67890 | 1234567890 | |
(2 rows)

[Alvin Peng (pengalvin)]

Below SQL is OK:

xtest=> show table tableC;
 COLUMN_NAME | SQL_DATA_TYPE | TYPE_NAME | IS_NULLABLE | KEY | DEFAULT
-------------+---------------+-----------+-------------+-----+---------
 id | 4 | INTEGER | YES | NO |
 stime | 93 | TIMESTAMP | YES | NO |
 val | -5 | BIGINT | YES | NO |
(3 rows)

xtest=> select * from tableC;
 id | stime | val
----+-------------------------+-------
  1 | 2013-10-16 16:56:20.953 | 11111
(1 row)
xtest=> select * from (select stime, val,val2,''::text as t1, ''::text as t2 from tableB
union
select stime, val,0::bigint as val2,''::text as t1, ''::text as t2 from tableC) t order by stime;
          stime | val | val2 | t1 | t2
-------------------------+-------+------------+----+----
 2013-10-16 16:09:05.078 | 67890 | 1234567890 | |
 2013-10-16 16:56:20.953 | 11111 | 0 | |
(2 rows)

[Alvin Peng (pengalvin)]

For the execution plan, when creating temp table, some column is not added:

######################
xtest=> select * from (select stime, t1, t2 from tableA union select stime, ''::text as t1, ''::text as t2 from tableB) t order by stime;
ERROR: each UNION query must have the same number of columns

xtest=> explain verbose select * from (select stime, t1, t2 from tableA union select stime, ''::text as t1, ''::text as t2 from tableB) t order by stime;
                                                       Query Plan

--------------------------------------------------------------------------------------------------------------------
----

  subplan
  -------

  UNION subplan
  -------

  Step: 0
  -------
  Select: SELECT distinct "t"."stime" AS "stime","t"."t1" AS "t1","t"."t2" AS "t2" FROM "tablea" "t"

  end UNION subplan
  -----------

  UNION subplan
  -------

  Step: 0
  -------
  Select: SELECT distinct "tableb"."stime" AS "stime",cast('' as TEXT) AS "t1" FROM "tableb"

  end UNION subplan
  -----------

  Step: 0
  -------
  Select: SELECT * FROM "TMPTT103_1" UNION SELECT * FROM "TMPTT104_2"

  end subplan
  -----------

  Step: 0
  -------
  Select: SELECT "TMPTT102_2"."stime" AS "stime","TMPTT102_2"."t1" AS "t1","TMPTT102_2"."t2" AS "t2" FROM "TMPTT102_
2"
   Drop:
  TMPTT102_2

 ----------------
  Execution Plan
 ----------------

  Relation Subplan:

  Union Subplan:

  ExecutionStep
  -------------
  producerCount = 6
  consumerCount = 6
  isExtraStep = false
  isFinalStep = false
  destNodeList = 1 2 3 4 5 6

  aStepDetail
  -----------

  requestId = 0
  StepNo = 1
  isProducer = true
  isConsumer = true
 queryString =
 SELECT distinct t.stime as stime,
         t.t1 as t1,
         t.t2 as t2
 FROM tablea t
  targetTable = TMPTT103_1
  targetSchema = CREATE TEMP TABLE "TMPTT103_1" ( "stime" TIMESTAMP, "t1" TEXT, "t2" TEXT) WITHOUT OIDS
  DropList =
  destType = DEST_TYPE_HASH
  hashColumnPosition = 1
  groupHashColumns =
  combineOnCoordFirst = false
  consumerNodeList = 1 2 3 4 5 6

  nodeUsageTable
  --------------
   nodeId = 1
   isProducer = true
   isConsumer = true
   nodeId = 2
   isProducer = true
   isConsumer = true
   nodeId = 3
   isProducer = true
   isConsumer = true
   nodeId = 4
   isProducer = true
   isConsumer = true
   nodeId = 5
   isProducer = true
   isConsumer = true
   nodeId = 6
   isProducer = true
   isConsumer = true

  Union Subplan:

  ExecutionStep
  -------------
  producerCount = 6
  consumerCount = 6
  isExtraStep = false
  isFinalStep = false
  destNodeList = 1 2 3 4 5 6

  aStepDetail
  -----------

  requestId = 0
  StepNo = 1
  isProducer = true
  isConsumer = true
 queryString =
 SELECT distinct tableb.stime as stime,
         cast ('' as text) as t1
 FROM tableb
  targetTable = TMPTT104_2
  targetSchema = CREATE TEMP TABLE "TMPTT104_2" ( "stime" TIMESTAMP, "t1" TEXT) WITHOUT OIDS

############## NOTE!! TABLE "TMPTT104_2" does not have "t2" column!!###################

  DropList =
  destType = DEST_TYPE_HASH
  hashColumnPosition = 1
  groupHashColumns =
  combineOnCoordFirst = false
  cons...

[Alvin Peng (pengalvin)]

Seems the cause is: when casting:

''::text as t1, ''::text as t2

the constant '' is the same for t1 and t2, so the second one is ignored.

if use another string for t2, for example ' ' (there is a space betwwn ''):

''::text as t1, ' '::text as t2

then the sql is ok.

Stado -> select * from (select t1,t2 from tableA union select '' as t1, '' as t2 from tableB) t order by t1;
SQLException: ERROR: ERROR: each UNION query must have the same number of columns

Stado -> select * from (select t1,t2 from tableA union select '' as t1, ' ' as t2 from tableB) t order by t1;
+---------------+
| t1 | t2 |
+---------------+
| | |
| text1 | text2 |
+---------------+
2 row(s).

[Alvin Peng (pengalvin)]

In Leaf.java,

/**
     * Checks to see if we already have the projection in our select list
     *
     * @param columnString - the projection to check
     * @return

     */
    public boolean isProjection(final String projectionString) {
        // See if it already is in Leaf.
        if (projectionString == null) {
            return false;
        }
        return selectColumnMap.get(projectionString) != null;
    }

I guess, there methods that call this "isProjection()" may have bugs.

If we have two same expression, like "cast (-1 as integer) ", then the second one is ignored.
Bug cast (-1 as integer) and cast (-2 as integer) is without problem.

[Alvin Peng (pengalvin)]

modify file: src/org/postgresql/stado/planner/QueryPlan.java
line 4132,

change

                boolean isSelColumn = aLeaf.isProjection(aSqlExpression.getExprString());

to

                boolean isSelColumn = aLeaf.isProjection(aSqlExpression.getExprString())
                                   && !aSqlExpression.isConstantExpr();

Check whether the expression is constantExpr or not. If it is constantExpr, added it to the CREATE TABLE even if the same value is already added.

This can fix the bug described above.

There are some other codes that call the "isProjection" method, but seems they are not related to this.

[Alvin Peng (pengalvin)]

Stado -> select * from (select t1,t2 from tableA union select '' as t1, '' as t2 from tableB) t order by t1;
+---------------+
| t1 | t2 |
+---------------+
| | |
| text1 | text2 |
+---------------+
2 row(s).

Stado -> select * from (select stime, t1, t2 from tableA union select stime, ''::text as t1, ''::text as t2 from tableB) t order by stime;
+-----------------------------------------+
| stime | t1 | t2 |
+-----------------------------------------+
| 2013-10-16 16:08:30.203 | text1 | text2 |
| 2013-10-16 16:09:05.078 | | |
+-----------------------------------------+
2 row(s).

Now the query is ok to run.

[Alvin Peng (pengalvin)]

without the "order by", the SQL would get "ERROR: Can not prepare request: null" exception.
Now with this patch, this issue seems to be fixed as well.

[Andrei Martsinchyk (andrei-martsinchyk)]

The fix was committed, thanks.