I agree with your analysis, and will try to work on such an implementation.
Note that, as far as I can tell, SELECT NULL, COUNT FROM T is not actually legal in Derby.
However, the roughly similar queries SELECT CAST(NULL AS INT), COUNT FROM T and
SELECT 'NULL', COUNT FROM T are legal, and, as you say, return the result set
I'm not sure where I got this "semantically equivalent to a union" terminology from when I wrote
the wiki page. It's certainly true that the original Jim Gray "Data Cube" paper from 1996 uses the
technique of defining ROLLUP in terms of an equivalent UNION query, but I don't think that's
how the actual SQL Standard ended up defining ROLLUP. However, I find the SQL Standard hard to read.
In the SQL Standard, ROLLUP is defined in terms of an equivalent GROUPING SETS query, and
GROUPING SETS includes the (new) syntax "GROUP BY ()". NOTE 137 of the SQL 2003 spec, in section 7.9, says:
The result of the transform is to replace RL with a <grouping sets specification> that contains
a <grouping set> for every initial sublist of the <ordinary grouping set list> of the <rollup list>,
obtained by dropping <ordinary grouping set>s from the right, one by one, and concatenating
each <ordinary grouping set list> so obtained. The <empty grouping set> is regarded as the
shortest such initial sublist.
So GROUP BY ROLLUP(a) is (internally) transformed into GROUP BY GROUPING SETS( (a), () )
Then, elsewhere in 7.9, in the "General Rules" section, it says:
If there are no grouping columns, then the result of the <group by clause> is the grouped table
consisting of T as its only group.
So I think that the SQL Standard says that
SELECT a, count FROM T GROUP BY ROLLUP(a)
should be equivalent to:
SELECT a, COUNT FROM T GROUP BY (a)
SELECT CAST ( NULL AS INT), COUNT FROM T GROUP BY ()
So, anyway, I think that the wiki page is somewhat inaccurate in this respect, and doesn't truly
obey the SQL Standard, because as you say the wiki page says that the last subquery in the
union has no group by, but the SQL Standard says that the last subquery in the union has GROUP BY ().
I still think that your analysis is correct, and will try to make the implementation comply.