Postgresql – WHERE clause not preventing return of row but changing values for aggregate function (here: count)

aggregatecountpostgresqlSecurity

I have recently discovered a PostgreSQL behavior which is strange and problematic in my opinion.

Consider this simple query

SELECT 
   'something confidential'
WHERE FALSE;

The nonsense clause WHERE FALSE resembles here a very strict permission check.
That works as expected: Nothing is returned.

Now, imagine you add an additional column e.g. a count function.
This gives is this query:

SELECT 
   'something confidential'
   ,count(CURRENT_DATE)
WHERE FALSE;

(The parameter of count could be anything. CURRENT_DATE is just a random example.)

Now, we get

something confidential | 0

I guess, this is somehow on purpose, in the sense that SELECT count(CURRENT_DATE); returns 1 if the condition is truthy and 0 if the condition is falsy.

However, I consider this as problematic in situations where you are not aware of it.
So, my questions are

  1. How come? What is the background of this behavior?
  2. How can I make such a query return zero rows instead of one row with value 0 for count?
  3. Is there a way directly in the query for preventing accidentally returning rows you don't want to return by adding aggregate functions? (I.e. apart from external tests etc.)

I'm using PostgreSQL 12.1, but the behavior is the same for older versions.

Best Answer

This is standard SQL behaviour for pretty much all databases.

There are three ways how rows are returned:

  1. without aggregate functions, the query returns one row per row;
  2. with aggregate functions, but without grouping, the query returns exactly one row;
  3. with aggregate functions and grouping, the query returns one row for each group (i.e., for each unique combination of values in the grouped columns).

In other words, aggregating without grouping behaves as if there is a single group, i.e., as if you had written GROUP BY () (a GROUP BY clause with an empty list results in a single group 'from nowhere', similar to how a query without FROM results in a single row 'from nowhere').

To prevent returning this single-group row, you can

  • add an explicit GROUP BY, so that you end up in case 3. above (after the WHERE has filtered out all rows, there is no value from which a group could be created):

    SELECT 
   'something confidential',
   count(CURRENT_DATE)
WHERE FALSE
GROUP BY 1;  -- refers to the first column

  • move the aggregation into a subquery, so that the permission check affects the output of that:

    SELECT *
FROM (SELECT
          'something confidential',
          count(CURRENT_DATE)
     ) AS subquery
WHERE FALSE;

  • move the permission check into the HAVING clause (if this is possible), so that the output for the single group is thrown away:

    SELECT 
   'something confidential',
   count(CURRENT_DATE)
HAVING FALSE;
Related Question