sqlite3 fetchone() returns None or another value

Question

I am learning Python and found a behavior that struggling to explain by myself: Using sqlite3 module, I am executing an SQL query on an existing SQLite table to fetch a value. If the condition in the SQL WHERE clause is true I am expecting a real value, and if the condition is false I am processing the case with COALESCE and want to return a default value (‘-1’ in my case). However, based on the code example below, fetchone() is returning a None object, until I am applying an aggregation function (MAX() in my case) on the selected column.

result = cur.execute('''SELECT coalesce(pin, '-1') inaccuracies
                                    FROM card where number = 'some_incorrect_number' ''').fetchone()
print(result)
# None -- WHY NONE?

result = cur.execute('''SELECT coalesce(max(pin), '-1') 
                                    FROM card where number = 'some_incorrect_number' ''').fetchone()
print(result)
# ('-1',) -- A CORRECT RESULT

Could you explain why MAX() solved this issue and I got a needed result, while without MAX() I received a None ?

Sorry if some inaccuracies in the question. Thank you!

Answer 1

This query:

SELECT coalesce(pin, '-1') inaccuracies FROM card where number = 'some_incorrect_number'

returns an empty result (no rows) if the condition in the WHERE clause is not satisfied by any row of the table and this is the expected bahavior.

But, when you use aggregate functions like MAX(), MIN(), COUNT(), SUM(), AVG() or GROUP_CONCAT() the query always returns 1 row with null as the result of the function (or 0 in the case of COUNT()) if no match was found (for the condition of the WHERE clause) or even if there are no rows in the table.