BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
От | PG Bug reporting form |
---|---|
Тема | BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS |
Дата | |
Msg-id | 16585-9d8c340d23ade8c1@postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
|
Список | 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!
В списке pgsql-bugs по дате отправления: