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
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
- 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.