pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
12+ messages / 5 participants
[nested] [flat]
Commits from this thread:
2020-08-22 4d346def15 6b701eaaa9 b439adcabb d3701bc8a2 7edd36eae1 6fa403e612 de627adaad Avoid pushing quals down into sub-queries that have grouping sets.
* BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
@ 2020-08-19 10:31 PG Bug reporting form <noreply@postgresql.org>
2020-08-19 11:34 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS David Rowley <dgrowleyml@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: PG Bug reporting form @ 2020-08-19 10:31 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: pavelsivash@gmail.com
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!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
2020-08-19 10:31 BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS PG Bug reporting form <noreply@postgresql.org>
@ 2020-08-19 11:34 ` David Rowley <dgrowleyml@gmail.com>
2020-08-20 21:25 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: David Rowley @ 2020-08-19 11:34 UTC (permalink / raw)
To: pavelsivash@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>; Andrew Gierth <andrew@tao11.riddles.org.uk>; David Fetter <david@fetter.org>
On Wed, 19 Aug 2020 at 23:12, PG Bug reporting form
<noreply@postgresql.org> wrote:
> 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.
hmm yeah, certainly a bug. On a very quick look, it looks like the
CTE inlining code is to blame as it works ok if the fin CTE is
materialized (as it would have been before 608b167f9). i.e:
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 MATERIALIZED (
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;
I see with the materialized version the CTE has a qual. This is the
qual that appears to go missing in the non-materialized version:
CTE Scan on fin (cost=0.28..0.39 rows=1 width=12)
Filter: (location_id = 1)
David
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
2020-08-19 10:31 BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS PG Bug reporting form <noreply@postgresql.org>
2020-08-19 11:34 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS David Rowley <dgrowleyml@gmail.com>
@ 2020-08-20 21:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-20 21:51 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andrew Gierth <andrew@tao11.riddles.org.uk>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2020-08-20 21:25 UTC (permalink / raw)
To: Andrew Gierth <andrew@tao11.riddles.org.uk>; +Cc: David Rowley <dgrowleyml@gmail.com>; pavelsivash@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> The problem here is that something is assuming that the country_id is
> still constant 1 despite its participation in grouping sets rendering it
> sometimes null.
Yeah. Your version of the query is initially simplified, by one level
of subquery pullup, into
select coalesce(country_id, city_id) AS location_id,
total
from (select 1 as country_id,
city_id,
sum(cnt) as total
from (values (2,5),(2,1),(3,2),(3,7)) as table1(city_id,cnt)
group by grouping sets (1,2)) base
where coalesce(country_id, city_id) = 1;
We can't pull up the remaining subquery because it has GROUP BY.
But what we will try to do instead is to push down the outer
WHERE clause into the subquery (cf. set_subquery_pathlist and
subroutines). That code sees no reason not to do so, so
it converts this into
select coalesce(country_id, city_id) AS location_id,
total
from (select 1 as country_id,
city_id,
sum(cnt) as total
from (values (2,5),(2,1),(3,2),(3,7)) as table1(city_id,cnt)
group by grouping sets (1,2)
having coalesce(1, city_id) = 1
) base;
and then const-folding proves the HAVING to be constant-true.
> Most likely, that constant column needs to either be treated as not
> constant, or something should be replacing it with a PHV - I'd have to
> dig into the code a bit to see what's actually going wrong.
PHVs don't save us here because those are only added when pulling up
a subquery, which is not what's happening.
As a stopgap measure, I think what we have to do is teach
check_output_expressions that subquery output columns are
unsafe to reference if they are not listed in all grouping
sets (do I have that condition right?).
The scheme I've been thinking about for clarifying the nullability
semantics of Vars might eventually provide a nicer answer for this,
but we haven't got it today.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
2020-08-19 10:31 BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS PG Bug reporting form <noreply@postgresql.org>
2020-08-19 11:34 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS David Rowley <dgrowleyml@gmail.com>
2020-08-20 21:25 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-08-20 21:51 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2020-08-21 02:08 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andy Fan <zhihui.fan1213@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andrew Gierth @ 2020-08-20 21:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: David Rowley <dgrowleyml@gmail.com>; pavelsivash@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
Tom> As a stopgap measure, I think what we have to do is teach
Tom> check_output_expressions that subquery output columns are unsafe
Tom> to reference if they are not listed in all grouping sets (do I
Tom> have that condition right?).
Unless I'm missing something, it should be safe to reference output
columns that are not mentioned in any grouping set, or which are
mentioned in all grouping sets (after all expansions); but unsafe to
reference columns mentioned in some grouping sets but not others (since
these will be forced to null in the output for the sets in which they
don't appear).
--
Andrew (irc:RhodiumToad)
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
2020-08-19 10:31 BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS PG Bug reporting form <noreply@postgresql.org>
2020-08-19 11:34 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS David Rowley <dgrowleyml@gmail.com>
2020-08-20 21:25 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Tom Lane <tgl@sss.pgh.pa.us>
2020-08-20 21:51 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andrew Gierth <andrew@tao11.riddles.org.uk>
@ 2020-08-21 02:08 ` Andy Fan <zhihui.fan1213@gmail.com>
2020-08-21 02:44 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 12+ messages in thread
From: Andy Fan @ 2020-08-21 02:08 UTC (permalink / raw)
To: Andrew Gierth <andrew@tao11.riddles.org.uk>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; David Rowley <dgrowleyml@gmail.com>; pavelsivash@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Aug 21, 2020 at 5:51 AM Andrew Gierth <andrew@tao11.riddles.org.uk>
wrote:
> >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>
> Tom> As a stopgap measure, I think what we have to do is teach
> Tom> check_output_expressions that subquery output columns are unsafe
> Tom> to reference if they are not listed in all grouping sets (do I
> Tom> have that condition right?).
>
> Unless I'm missing something, it should be safe to reference output
> columns that are not mentioned in any grouping set,
I think such columns usually are aggregation expr, If we want to push down
a qual which reference to an aggregation expr, we have to push down
to having cause, However I am not sure such pushing down really helps.
> or which are
> mentioned in all grouping sets (after all expansions);
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
2020-08-19 10:31 BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS PG Bug reporting form <noreply@postgresql.org>
2020-08-19 11:34 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS David Rowley <dgrowleyml@gmail.com>
2020-08-20 21:25 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Tom Lane <tgl@sss.pgh.pa.us>
2020-08-20 21:51 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andrew Gierth <andrew@tao11.riddles.org.uk>
2020-08-21 02:08 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andy Fan <zhihui.fan1213@gmail.com>
@ 2020-08-21 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-21 03:50 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andy Fan <zhihui.fan1213@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Tom Lane @ 2020-08-21 02:44 UTC (permalink / raw)
To: Andy Fan <zhihui.fan1213@gmail.com>; +Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>; David Rowley <dgrowleyml@gmail.com>; pavelsivash@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Andy Fan <zhihui.fan1213@gmail.com> writes:
> On Fri, Aug 21, 2020 at 5:51 AM Andrew Gierth <andrew@tao11.riddles.org.uk>
> wrote:
>> Unless I'm missing something, it should be safe to reference output
>> columns that are not mentioned in any grouping set,
> I think such columns usually are aggregation expr, If we want to push down
> a qual which reference to an aggregation expr, we have to push down
> to having cause, However I am not sure such pushing down really helps.
Well, they can either be aggregates, or functions of the grouping
columns. You're right that there's not much we can do (today) with
restrictions on aggregate outputs, but there can be value in pushing
down restrictions on the other sort.
As an example, consider the regression database's tenk1 table, and
for argument's sake add
regression=# create index on tenk1 (abs(hundred));
CREATE INDEX
Then we can get
regression=# explain select * from (select hundred, ten, abs(hundred) a, count(*) c from tenk1 group by 1,2) ss where a = 42;
QUERY PLAN
------------------------------------------------------------------------------------
HashAggregate (cost=225.98..227.18 rows=96 width=20)
Group Key: tenk1.hundred, tenk1.ten
-> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=8)
Recheck Cond: (abs(hundred) = 42)
-> Bitmap Index Scan on tenk1_abs_idx (cost=0.00..5.04 rows=100 width=0)
Index Cond: (abs(hundred) = 42)
(6 rows)
which is a lot cheaper than the pure seqscan you get with no pushed-down
condition.
One thing that I find curious is that if I alter this example to use
grouping sets, say
regression=# explain select * from (select hundred, ten, abs(hundred) a, count(*) c from tenk1 group by grouping sets (1,2)) ss where a = 42;
QUERY PLAN
-----------------------------------------------------------------
HashAggregate (cost=495.00..546.65 rows=2 width=20)
Hash Key: tenk1.hundred
Hash Key: tenk1.ten
Filter: (abs(tenk1.hundred) = 42)
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=8)
(5 rows)
i.e. it's not seeing the abs() condition as pushable below the
aggregation. I'm not quite sure if that's a necessary restriction
or a missed optimization.
regards, tom lane
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
2020-08-19 10:31 BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS PG Bug reporting form <noreply@postgresql.org>
2020-08-19 11:34 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS David Rowley <dgrowleyml@gmail.com>
2020-08-20 21:25 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Tom Lane <tgl@sss.pgh.pa.us>
2020-08-20 21:51 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andrew Gierth <andrew@tao11.riddles.org.uk>
2020-08-21 02:08 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andy Fan <zhihui.fan1213@gmail.com>
2020-08-21 02:44 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-08-21 03:50 ` Andy Fan <zhihui.fan1213@gmail.com>
2020-08-21 04:15 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andy Fan <zhihui.fan1213@gmail.com>
0 siblings, 1 reply; 12+ messages in thread
From: Andy Fan @ 2020-08-21 03:50 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>; David Rowley <dgrowleyml@gmail.com>; pavelsivash@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Aug 21, 2020 at 10:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andy Fan <zhihui.fan1213@gmail.com> writes:
> > On Fri, Aug 21, 2020 at 5:51 AM Andrew Gierth <
> andrew@tao11.riddles.org.uk>
> > wrote:
> >> Unless I'm missing something, it should be safe to reference output
> >> columns that are not mentioned in any grouping set,
>
> > I think such columns usually are aggregation expr, If we want to push
> down
> > a qual which reference to an aggregation expr, we have to push down
> > to having cause, However I am not sure such pushing down really helps.
>
> Well, they can either be aggregates, or functions of the grouping
> columns. You're right that there's not much we can do (today) with
> restrictions on aggregate outputs, but there can be value in pushing
> down restrictions on the other sort.
>
> As an example, consider the regression database's tenk1 table, and
> for argument's sake add
>
> regression=# create index on tenk1 (abs(hundred));
> CREATE INDEX
>
> Then we can get
>
> regression=# explain select * from (select hundred, ten, abs(hundred) a,
> count(*) c from tenk1 group by 1,2) ss where a = 42;
> QUERY PLAN
>
>
> ------------------------------------------------------------------------------------
> HashAggregate (cost=225.98..227.18 rows=96 width=20)
> Group Key: tenk1.hundred, tenk1.ten
> -> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=8)
> Recheck Cond: (abs(hundred) = 42)
> -> Bitmap Index Scan on tenk1_abs_idx (cost=0.00..5.04 rows=100
> width=0)
> Index Cond: (abs(hundred) = 42)
> (6 rows)
>
> which is a lot cheaper than the pure seqscan you get with no pushed-down
> condition.
>
> One thing that I find curious is that if I alter this example to use
> grouping sets, say
>
> regression=# explain select * from (select hundred, ten, abs(hundred) a,
> count(*) c from tenk1 group by grouping sets (1,2)) ss where a = 42;
> QUERY PLAN
> -----------------------------------------------------------------
> HashAggregate (cost=495.00..546.65 rows=2 width=20)
> Hash Key: tenk1.hundred
> Hash Key: tenk1.ten
> Filter: (abs(tenk1.hundred) = 42)
> -> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=8)
> (5 rows)
>
> i.e. it's not seeing the abs() condition as pushable below the
> aggregation. I'm not quite sure if that's a necessary restriction
> or a missed optimization.
>
> regards, tom lane
>
Both of the queries can push down the qual "a = 42" to the
subquery->havingQual
since we have group-by clause, this method unify the process for
aggregation call
and non-aggregation expr. . so it become to
select .. from (select .. from tenk1 group .. having (abs(hundred) = 2);
later in the subquery_planner, we will try to pull the having clause to
where clause.
then the Q2 failed to do so.
/*
* In some cases we may want to transfer a HAVING clause into
WHERE. We
* cannot do so if the HAVING clause contains aggregates
(obviously) or
* volatile functions (since a HAVING clause is supposed to be
executed
* only once per group). We also can't do this if there are any
nonempty
* grouping sets; moving such a clause into WHERE would potentially
change
* the results, if any referenced column isn't present in all the
grouping
* sets. (If there are only empty grouping sets, then the HAVING
clause
* must be degenerate as discussed below.)
*/
I'm still trying to understand the comment, though.
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS
2020-08-19 10:31 BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS PG Bug reporting form <noreply@postgresql.org>
2020-08-19 11:34 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS David Rowley <dgrowleyml@gmail.com>
2020-08-20 21:25 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Tom Lane <tgl@sss.pgh.pa.us>
2020-08-20 21:51 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andrew Gierth <andrew@tao11.riddles.org.uk>
2020-08-21 02:08 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andy Fan <zhihui.fan1213@gmail.com>
2020-08-21 02:44 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Tom Lane <tgl@sss.pgh.pa.us>
2020-08-21 03:50 ` Re: BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS Andy Fan <zhihui.fan1213@gmail.com>
@ 2020-08-21 04:15 ` Andy Fan <zhihui.fan1213@gmail.com>
0 siblings, 0 replies; 12+ messages in thread
From: Andy Fan @ 2020-08-21 04:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Andrew Gierth <andrew@tao11.riddles.org.uk>; David Rowley <dgrowleyml@gmail.com>; pavelsivash@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Fri, Aug 21, 2020 at 11:50 AM Andy Fan <zhihui.fan1213@gmail.com> wrote:
>
>
> On Fri, Aug 21, 2020 at 10:44 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> Andy Fan <zhihui.fan1213@gmail.com> writes:
>> > On Fri, Aug 21, 2020 at 5:51 AM Andrew Gierth <
>> andrew@tao11.riddles.org.uk>
>> > wrote:
>> >> Unless I'm missing something, it should be safe to reference output
>> >> columns that are not mentioned in any grouping set,
>>
>> > I think such columns usually are aggregation expr, If we want to push
>> down
>> > a qual which reference to an aggregation expr, we have to push down
>> > to having cause, However I am not sure such pushing down really helps.
>>
>> Well, they can either be aggregates, or functions of the grouping
>> columns. You're right that there's not much we can do (today) with
>> restrictions on aggregate outputs, but there can be value in pushing
>> down restrictions on the other sort.
>>
>> As an example, consider the regression database's tenk1 table, and
>> for argument's sake add
>>
>> regression=# create index on tenk1 (abs(hundred));
>> CREATE INDEX
>>
>> Then we can get
>>
>> regression=# explain select * from (select hundred, ten, abs(hundred) a,
>> count(*) c from tenk1 group by 1,2) ss where a = 42;
>> QUERY PLAN
>>
>>
>> ------------------------------------------------------------------------------------
>> HashAggregate (cost=225.98..227.18 rows=96 width=20)
>> Group Key: tenk1.hundred, tenk1.ten
>> -> Bitmap Heap Scan on tenk1 (cost=5.06..225.23 rows=100 width=8)
>> Recheck Cond: (abs(hundred) = 42)
>> -> Bitmap Index Scan on tenk1_abs_idx (cost=0.00..5.04
>> rows=100 width=0)
>> Index Cond: (abs(hundred) = 42)
>> (6 rows)
>>
>> which is a lot cheaper than the pure seqscan you get with no pushed-down
>> condition.
>>
>> One thing that I find curious is that if I alter this example to use
>> grouping sets, say
>>
>> regression=# explain select * from (select hundred, ten, abs(hundred) a,
>> count(*) c from tenk1 group by grouping sets (1,2)) ss where a = 42;
>> QUERY PLAN
>> -----------------------------------------------------------------
>> HashAggregate (cost=495.00..546.65 rows=2 width=20)
>> Hash Key: tenk1.hundred
>> Hash Key: tenk1.ten
>> Filter: (abs(tenk1.hundred) = 42)
>> -> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=8)
>> (5 rows)
>>
>> i.e. it's not seeing the abs() condition as pushable below the
>> aggregation. I'm not quite sure if that's a necessary restriction
>> or a missed optimization.
>>
>> regards, tom lane
>>
>
> Both of the queries can push down the qual "a = 42" to the
> subquery->havingQual
> since we have group-by clause, this method unify the process for
> aggregation call
> and non-aggregation expr. . so it become to
>
> select .. from (select .. from tenk1 group .. having (abs(hundred) = 2);
>
>
> later in the subquery_planner, we will try to pull the having clause to
> where clause.
> then the Q2 failed to do so.
>
> /*
> * In some cases we may want to transfer a HAVING clause into
> WHERE. We
> * cannot do so if the HAVING clause contains aggregates
> (obviously) or
> * volatile functions (since a HAVING clause is supposed to be
> executed
> * only once per group). We also can't do this if there are any
> nonempty
> * grouping sets; moving such a clause into WHERE would
> potentially change
> * the results, if any referenced column isn't present in all the
> grouping
> * sets. (If there are only empty grouping sets, then the HAVING
> clause
> * must be degenerate as discussed below.)
> */
>
> I'm still trying to understand the comment, though.
>
>
>
This should be a correct behavior, we should not push down in the Q2
case. Here is an example:
regression=# create table tgs(a int, b int);
CREATE TABLE
regression=# insert into tgs values(1, 1), (1, 2), (2, 2);
INSERT 0 3
regression=# select * from (select a, b, count(*) from tgs group by
grouping sets((a), (b))) t where b = 1;
a | b | count
---+---+-------
| 1 | 1
(1 row)
regression=# select * from (select a, b, count(*) from tgs group by
grouping sets((a), (b)) having b = 1) t;
a | b | count
---+---+-------
| 1 | 1
(1 row)
regression=# select * from (select a, b, count(*) from tgs where b = 1
group by grouping sets((a), (b)) ) t;
a | b | count
---+---+-------
1 | | 1
| 1 | 1
(2 rows)
At the same time, our optimizer is smart enough to handle the below case
(only 1 set in group sets, which equals
group by).
regression=# explain select * from (select a, b, count(*) from tgs group by
grouping sets((a, b)) ) t where b = 1;
QUERY PLAN
-----------------------------------------------------------------
GroupAggregate (cost=38.44..38.66 rows=11 width=16)
Group Key: tgs.a, tgs.b
-> Sort (cost=38.44..38.47 rows=11 width=8)
Sort Key: tgs.a
-> Seq Scan on tgs (cost=0.00..38.25 rows=11 width=8)
Filter: (b = 1)
(6 rows)
--
Best Regards
Andy Fan
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2020-08-21 04:15 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-08-19 10:31 BUG #16585: Wrong filtering on a COALESCE field after using GROUPING SETS PG Bug reporting form <noreply@postgresql.org>
2020-08-19 11:34 ` David Rowley <dgrowleyml@gmail.com>
2020-08-20 21:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-20 21:51 ` Andrew Gierth <andrew@tao11.riddles.org.uk>
2020-08-21 02:08 ` Andy Fan <zhihui.fan1213@gmail.com>
2020-08-21 02:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-08-21 03:50 ` Andy Fan <zhihui.fan1213@gmail.com>
2020-08-21 04:15 ` Andy Fan <zhihui.fan1213@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox