1

Refer following query:

SELECT SUM(n) AS SUMVAL FROM (values(3),(4)) AS tbl(n) WHERE 1=0

Since I am providing a predicate that is false, i would have assumed it to return a empty result set(0 rows). But it returns a row with NULL. Is it a bug? Or is there a valid explanation for this behavior.

If I remove the aggregate function, I get a empty result set as expected:

SELECT n AS VAL FROM (values(3),(4)) AS tbl(n) WHERE 1=0

And just to make it clear, if I remove the false predicate, then the query will return 2 rows with values 3 and 4 for column 'VAL'

SELECT n AS VAL FROM (values(3),(4)) AS tbl(n)

But if I am using aggregate functions with GROUP BY clause, as shown in query below, i get expected result(empty rowset)

SELECT id, SUM(n) AS SUMVAL FROM (values(1,3.0),(2,4.0)) AS tbl(id,n) WHERE 1=0 GROUP BY id
umbersar
  • 1,821
  • 3
  • 24
  • 34

2 Answers2

1

I came across this recently as it caused a major OOPS in a piece of code.

Here's my go at it.

COUNT is not aggregating properties of the rowset data, but about the rowset, itself. So 0 makes sense. SUM (as well as MAX, MIN, etc) is aggregating properties of the rowset data. With no rows to aggregate, the value is undefined, but does exist. For instance, the SUM of nothing is nothing (not zero). The MAX of nothing is nothing.

Now, the second case, where there's a GROUP BY. Aggregates in a GROUP BY are sort of like a foreach loop. It's asking, for each group defined by the GROUP BY, what are the aggregate values? When there are no rows to group, the loop never executes, so nothing is returned. There are no groups, and since it returns one row per group, it returns nothing.

Marc L. Allen
  • 410
  • 2
  • 8
  • Thanks for giving it a go but I do not think this is an valid explanation. If the predicate is false, the expected result (IMO) is empty result set. It seems to me to be a quirk to return 0 or NULL. – umbersar Jun 23 '18 at 00:47
  • So, you’d prefer Count(*) to never return 0? All those times you do a count when there are no matching rows returns nothing? It may be a quirk, but SQL actually specs it that way, I believe. – Marc L. Allen Jun 23 '18 at 00:49
  • thinking again about it...your explanation is making sense – umbersar Jun 24 '18 at 07:38
-1

SELECT SUM(n) AS SUMVAL FROM (values(3),(4)) AS tbl(n) WHERE 1=0

As in the case above selecting only the aggregated value returns NULL when the predicate is false because we dont have any rows that satisfy the predicate condition.

SELECT id, SUM(n) AS SUMVAL FROM (values(1,3.0),(2,4.0)) AS tbl(id,n) WHERE 1=0 GROUP BY id

But in the above it returns empty result set because we try to sum up the values based on a specific item which is not even exists (in your example false predicate). This is the normal behavior of sql server.

Arockia Nirmal
  • 737
  • 1
  • 6
  • 20