BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: pavelsivash(at)gmail(dot)com
Subject: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
Date: 2020-08-19 10:31:49
Message-ID: 16585-9d8c340d23ade8c1@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 16585
Logged by: Paul Sivash
Email address: pavelsivash(at)gmail(dot)com
PostgreSQL version: 12.4
Operating system: x86_64-pc-linux-gnu
Description:

Hello! There is a problem with filtering COALESCE field which has constant
from nested subselect with GROUPING SETS as first element.

Example:

WITH table1 AS (
SELECT 2 AS city_id, 5 AS cnt
UNION ALL
SELECT 2 AS city_id, 1 AS cnt
UNION ALL
SELECT 3 AS city_id, 2 AS cnt
UNION ALL
SELECT 3 AS city_id, 7 AS cnt
),

fin AS (
SELECT
coalesce(country_id, city_id) AS location_id,
total
FROM (
SELECT
1 as country_id,
city_id,
sum(cnt) as total
FROM table1
GROUP BY GROUPING SETS (1,2)
) base
)

SELECT *
FROM fin
WHERE location_id = 1;

As you can see in the end I want to keep only rows with location_id = 1 but
the result gives me all available rows. This happens because Postgres sees
that I filter COALESCE field which has "country_id" as first element and
"country_id" is previously set as constant - 1. But the thing is that using
GROUPING SETS turns "country_id" to NULL in some rows and this behaviour is
wrong.

When I change final filter to "location_id = 2" it returns 0 rows for the
same reason.

Thank you in advance!

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Rowley 2020-08-19 11:34:19 Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
Previous Message Jiří Fejfar 2020-08-19 06:08:42 Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails