BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
The Post Office <noreply@postgresql.org>
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: pavelsivash@gmail.com
Date: 2020-08-19T10:31:49Z
Lists: pgsql-bugs
The following bug has been logged on the website:
Bug reference: 16585
Logged by: Paul Sivash
Email address: pavelsivash@gmail.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!
Commits
-
Avoid pushing quals down into sub-queries that have grouping sets.
- de627adaad3a 13.0 landed
- d3701bc8a25b 9.6.20 landed
- b439adcabbf9 11.10 landed
- 7edd36eae1fd 9.5.24 landed
- 6fa403e61241 10.15 landed
- 6b701eaaa90e 12.5 landed
- 4d346def1555 14.0 landed