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
- How come? What is the background of this behavior?
- How can I make such a query return zero rows instead of one row with value 0 for
count
? - 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.
2 Answers 2
This is standard SQL behaviour for pretty much all databases.
There are three ways how rows are returned:
- without aggregate functions, the query returns one row per row;
- with aggregate functions, but without grouping, the query returns exactly one row;
- 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;
-
1regarding that GROUP BY some_constant value (which SQL does not allow to write explicitly): there is
GROUP BY ()
which is standard SQL. Supported by Postgres, SQL Server, Oracle, DB2: dbfiddle.uk/…ypercubeᵀᴹ– ypercubeᵀᴹ2020年02月13日 13:38:32 +00:00Commented Feb 13, 2020 at 13:38
This is expected behaviour (not only for Postgres). If we look at the logical order of evaluation for a query:
- FROM
- WHERE
- GROUP BY
- SELECT
I'll add a table (T) with one column (x) and one row in the example
We have:
SELECT COUNT(1) as CNT
FROM ( VALUES (1) ) AS t(x)
WHERE false
GROUP BY () -- empty set default
The result is one row with value 0. Then we add a constant 'something confidential' to that row and end up with:
SELECT 'something confidential', CNT
FROM
SELECT COUNT(1) as CNT
FROM ( VALUES (1) ) AS t(x)
WHERE false
GROUP BY () -- empty set default
) AS u
'something confidential', 0
Explore related questions
See similar questions with these tags.