[SOLVED] Unexpected behavior for count on empty table

Unexpected behavior for count on empty table

I have two tables table_a and table_b in a PostgreSQL 13 database, having UUIDs primary key columns.

table_a has multiple entries, whereas table_b is empty (no entry). The following query returns the expected result, namely entry_count_a larger than 0:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a FROM table_a ta

However, the following query returns 0 for both entry_counts:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, COUNT(DISTINCT tb.uuid) AS entry_count_b FROM table_a ta, table_b tb

What is the correct way to write the query, so that entry_count_a contains the correct (expected) value > 0, whereas entry_count_b is 0?

Bonus question: Why does Postgres behave this way?

Solution

Your current query is equivalent to the following one:

SELECT COUNT(DISTINCT ta.uuid) AS entry_count_a, 
       COUNT(DISTINCT tb.uuid) AS entry_count_b 
FROM       table_a ta
CROSS JOIN table_b tb

When you apply the cartesian product between two tables, you multiply their cardinality. You get no rows because one of the two tables has cardinality 0, hence 0*n, for any n, is always 0.

If you want to display the two counts correctly, you could use two subqueries as follows:

SELECT
    (SELECT COUNT(DISTINCT uuid) FROM table_a) AS entry_count_a,
    (SELECT COUNT(DISTINCT uuid) FROM table_b) AS entry_count_b

Check the demo here.