union all with queries that have a different number of columns

Question

I've run into a case where a sqlite query I'm expecting to return an error is actually succeeding and I was wondering if anyone could point out why this query is valid.

CREATE TABLE test_table(
  k INTEGER,
  v INTEGER
);

INSERT INTO test_table( k, v ) VALUES( 4, 5 );

SELECT * FROM(
  SELECT * FROM(
    SELECT k, v FROM test_table WHERE 1 = 0
  )
  UNION ALL
  SELECT * FROM(
    SELECT rowid, k, v FROM test_table
  )
)

sqlfiddle of above

I would think that unioning two selects which have a different number of columns would return an error. If I remove the outermost SELECT * then I receive the error I'm expecting: SELECTs to the left and right of UNION ALL do not have the same number of result columns.

Answer 1

If your second query has less number of columns, you can do this:

select col1, col2, col3, col4, col5
from table A

union all

select col1, col2, col3, col4, NULL as col5, 
from table B

Instead of NULL, one can also use some string constant - 'KPI' as col5.

Answer 2

The answer to this seems to be straightforward: Yes, this is a quirk. I'd like to demonstrate this with a short example. But beforehand, let's consult the documentation:

Two or more simple SELECT statements may be connected together to form a compound SELECT using the UNION, UNION ALL, INTERSECT or EXCEPT operator. In a compound SELECT, all the constituent SELECTs must return the same number of result columns.

So the documentations says very clearly that two SELECTs must provide the same number of columns. However, as you said, the outermost SELECT strangely avoids this 'limitation'.

Example 1

SELECT * FROM(
    SELECT k, v FROM test_table
  UNION ALL
    SELECT k, v,rowid FROM test_table
);

Result:

k|v
4|5
4|5

The third column rowid gets simply omitted, as pointed out in the comments.

Example 2

We are only switching the order of the two select statements.

 SELECT * FROM(
    SELECT k, v, rowid FROM test_table
  UNION ALL
     SELECT k, v FROM test_table
  );

Result

k|v|rowid
4|5|1
4|5|

Now, sqlite does not omit the column but add a null value.

Conclusion

This brings me to my conclusion, that sqlite simply handles the UNION ALL differently if it is processed as a subquery.

PS: If you are just using UNION it fails at any scenario.

Answer 3

UNION ALL will return the results with null values in the extra columns.

A basic UNION will fail because UNION without the ALL has to have the same number of columns from both tables.

So:

SELECT column1, column2 FROM table a
UNION ALL 
SELECT column1, column2, column3 FROM table b

returns 3 columns with nulls in column 3.

and:

SELECT column1, column2 FROM table a
UNION 
SELECT column1, column2, column3 FROM table b

should fail because the number of columns do not match.

In conclusion you could add a blank column to the UNION so that you are selecting 3 columns from each table and it would still work.

EX:

SELECT column1, column2, '' AS column3 FROM table a
UNION  
SELECT column1, column2, column3 FROM table b