Weird behaviour

[borka66]

Hi,
Can somebody explain why this throws an error

select x, sum(y)
from (
select 1 as x, (select 2 from dual) as y from dual
) a
group by x;

ERROR 4818: Subqueries in the SELECT or ORDER BY are not supported if the subquery is not part of the GROUP BY

and this one not?

select x, sum(y)
from (
select 1 as x, (select 2 from dual) as y from dual
union all select 0, 0 from dual where 1 = 2
) a
group by x;
x | sum
---+-----
1 | 2
(1 row)

This is pretty interesting if I may rely on this workaround.
Vertica v7.0.1-0.
10x!

[JimKnicely]

Try:

Code: Select all

select x, sum(y)
from (
select 1 as x, (select 2 from dual) as y from dual order by 1
) a
group by x;

[borka66]

I like your soltion. Much easier to code.
Could you explain why these workarounds solve the error?