| 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: | Whole Thread | Raw Message | 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!
| 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 |