COLUMN_COUNT_DOES_NOT_MATCH error when using UNION ALL between two queries that have a subquery

[GoogleCodeExporter]

Seems that the database counts columns from the subquery instead from the 
actual query that will be used in the union. Here is an example:

This query returns COLUMN_COUNT_DOES_NOT_MATCH error:
SELECT  '123', 1 FROM (select '123' a from SYSTEM_RANGE(1,1) )
union all
select '123', 1 from SYSTEM_RANGE(1,1)
What is the expected output? What do you see instead?

However, if the subquery has the same column count as the second query, it 
works fine:
SELECT  '123', 1 FROM (select '123' a, 1 b from SYSTEM_RANGE(1,1) )
union all
select '123', 1 from SYSTEM_RANGE(1,1)

Version is h2-1.2.133 on windows 7 x64

Do you know a workaround?
Yes, add dummy columns into subquery.

How important/urgent is the problem for you?
Not so urgent.

In your view, is this a defect or a feature request?
Defect

Original issue reported on code.google.com by feign.et...@gmail.com on 8 Jun 2010 at 9:25

[GoogleCodeExporter]

Hi,

Sorry for the delay. The problem is that the query is:
select 1, 2 from (select * from dual) union all select 3, 4 from dual;
is parsed as:
select 1, 2 from ((select * from dual) union all select 3, 4 from dual);
This is a bug, I will fix it in the next release. The workaround is:
(select 1, 2 from (select * from dual)) union all select 3, 4 from dual;

Regards,
Thomas

Original comment by thomas.t...@gmail.com on 28 Jun 2010 at 6:38

• Changed state: Accepted

[GoogleCodeExporter]

No, problem. I found a workaround and got my app working
Just wanted you to know about the bug.

Cheers,
Domagoj

Original comment by feign.et...@gmail.com on 28 Jun 2010 at 6:44

[GoogleCodeExporter]

It is now fixed in the trunk, and will be available in version 1.2.139.

Original comment by thomas.t...@gmail.com on 28 Jun 2010 at 6:54

[GoogleCodeExporter]

Fixed in version 1.2.139.

Original comment by thomas.t...@gmail.com on 10 Jul 2010 at 2:10

• Changed state: Fixed