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
I'm very sorry to have bothered you. I thought this was a pg problem, but
it's not. It appears to be a bug in a GUI wrapper I'm using to issue the
queries. When using the psql client, everything works as expected. I will
file a separate bug report for the GUI. Please disregard this bug report.
On Fri, May 25, 2018 at 12:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
=?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