Thread: BUG #15208: COALESCE with CTE returns NULL

The following bug has been logged on the website:

Bug reference:      15208
Logged by:          Rick Gabriel
Email address:      klaxian@gmail.com
PostgreSQL version: 10.4
Operating system:   Ubuntu Linux 16.04
Description:

When using COALESCE with an aggregate subquery to a CTE, NULL is returned
instead of the first non-null value in the argument list. Proof-of-concept
examples are below. I would expect COALESCE to return 0 in these cases. Am I
missing something? Thanks.

WITH test_cte AS (SELECT 1 AS id, 2 AS qty)
SELECT COALESCE(SUM(qty), 0) FROM test_cte WHERE id=2;

WITH test_cte AS (SELECT 1 AS id, 2 AS qty)
SELECT COALESCE((SELECT SUM(qty) FROM test_cte WHERE id=2), 0);

Hello
I can not reproduce

psql (10.4 (Debian 10.4-1.pgdg80+1))
Type "help" for help.

postgres=# WITH test_cte AS (SELECT 1 AS id, 2 AS qty)
SELECT COALESCE(SUM(qty), 0) FROM test_cte WHERE id=2;
 coalesce 
----------
        0
(1 row)

postgres=# WITH test_cte AS (SELECT 1 AS id, 2 AS qty)
SELECT COALESCE((SELECT SUM(qty) FROM test_cte WHERE id=2), 0);
 coalesce 
----------
        0
(1 row)

Can you give more complete example?

regards, Sergei

=?utf-8?q?PG_Bug_reporting_form?= <noreply@postgresql.org> writes:
> When using COALESCE with an aggregate subquery to a CTE, NULL is returned
> instead of the first non-null value in the argument list. Proof-of-concept
> examples are below. I would expect COALESCE to return 0 in these cases. Am I
> missing something? Thanks.

Um ... I get zero from each of your test cases, in all PG versions back
to 9.3.

            regards, tom lane