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.
SELECT 'something',count(CURRENT_DATE) WHERE FALSE HAVING count(CURRENT_DATE)<>0
(also more info: modern-sql.com/use-case/select-without-from ) – Luuk Feb 13 '20 at 11:17