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  (David Rowley <dgrowleyml@gmail.com>)
Список 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 по дате отправления:

Предыдущее
От: Jiří Fejfar
Дата:
Сообщение: Re: BUG #16583: merge join on tables with different DB collation behind postgres_fdw fails
Следующее
От: David Rowley
Дата:
Сообщение: Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS