Specifying GROUPING SETS ( [,...n ]) as the GROUP BY list is equivalent to a UNION ALL of queries, each with one of the grouping sets as its GROUP BY list. Aggregates on floating-point numbers might return slightly different results.

The following statements are equivalent:

SELECT customer, year, SUM(sales)
FROM T
GROUP BY GROUPING SETS ((customer), (year))


SELECT customer, NULL as year, SUM(sales)
FROM T
GROUP BY customer
UNION ALL
SELECT NULL as customer, year, SUM(sales)
FROM T
GROUP BY year

liam.stirling (9/23/2008)

---

I thought "The following statements are equivalent" meant they were the same.

Silly me!

Tao Klerks (9/23/2008)

---

Sorry to whine, but this question is a little disappointing - It's educational on at least two counts (grouping sets - new feature, and possible gotcha on floating point numbers), but ultimately what does it mean for two queries to be "the same"?

Do you judge based on the results? The query plan? The syntax?

Most people most of the time would be referring to "logical" equivalence - is the returned data set exactly the same regardless of the data set? And the answer to that question is, sadly, "It cannot be determined based on the information provided", as we would need to know the data structure of the table.

In case anyone reads this far, I do have a question: Do we know under what exact circumstances floating-point numbers (presumably Real and Float) will return different results? Can we assume Decimal types are exempt from this uncertainty?