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

  1. Avoid pushing quals down into sub-queries that have grouping sets.