Thread

1. Fix HAVING-to-WHERE pushdown with mismatched operator families

   Richard Guo <guofenglinux@gmail.com> — 2026-05-26T08:48:24Z

   Continued with the rabbit-hole around equality-relation issues, here
   is another one in the HAVING-to-WHERE pushdown path.
   
   f76686ce7 stopped the planner from pushing a HAVING clause to WHERE
   when the clause's collation disagreed with the GROUP BY's
   nondeterministic collation.  The same shape of bug exists with
   operator families: when a HAVING clause uses a comparison operator
   from a different opfamily than the GROUP BY's eqop, pushing it to
   WHERE can produce wrong results.
   
   create type t_rec as (a numeric, b int);
   create table t_having (id int, r t_rec);
   
   insert into t_having values
     (1, row(100, 1)::t_rec),
     (2, row(100.0, 1)::t_rec),
     (3, row(2, 2)::t_rec);
   
   -- wrong result: count should be 2
   select r, count(*) from t_having group by r having r *= row(100, 1)::t_rec;
       r    | count
   ---------+-------
    (100,1) |     1
   (1 row)
   
   The fix in the attached patch mirrors f76686ce7's structure.  We
   detect the conflict before flatten_group_exprs while the HAVING clause
   still contains GROUP Vars, and record the indices of unsafe clauses in
   a Bitmapset that's consulted by the existing pushdown loop.
   
   This issue can be reproduced as far back as v14.  However, the fix
   relies on RTE_GROUP to identify grouping expressions via GROUP Vars on
   pre-flatten havingQual.  As with f76686ce7, I'm inclined to back-patch
   to v18 only.
   
   Thoughts?
   
   - Richard
   

2. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

   Thom Brown <thom@linux.com> — 2026-05-26T14:05:48Z

   On Tue, 26 May 2026 at 09:48, Richard Guo <guofenglinux@gmail.com> wrote:
   >
   > Continued with the rabbit-hole around equality-relation issues, here
   > is another one in the HAVING-to-WHERE pushdown path.
   >
   > f76686ce7 stopped the planner from pushing a HAVING clause to WHERE
   > when the clause's collation disagreed with the GROUP BY's
   > nondeterministic collation.  The same shape of bug exists with
   > operator families: when a HAVING clause uses a comparison operator
   > from a different opfamily than the GROUP BY's eqop, pushing it to
   > WHERE can produce wrong results.
   >
   > create type t_rec as (a numeric, b int);
   > create table t_having (id int, r t_rec);
   >
   > insert into t_having values
   >   (1, row(100, 1)::t_rec),
   >   (2, row(100.0, 1)::t_rec),
   >   (3, row(2, 2)::t_rec);
   >
   > -- wrong result: count should be 2
   > select r, count(*) from t_having group by r having r *= row(100, 1)::t_rec;
   >     r    | count
   > ---------+-------
   >  (100,1) |     1
   > (1 row)
   >
   > The fix in the attached patch mirrors f76686ce7's structure.  We
   > detect the conflict before flatten_group_exprs while the HAVING clause
   > still contains GROUP Vars, and record the indices of unsafe clauses in
   > a Bitmapset that's consulted by the existing pushdown loop.
   >
   > This issue can be reproduced as far back as v14.  However, the fix
   > relies on RTE_GROUP to identify grouping expressions via GROUP Vars on
   > pre-flatten havingQual.  As with f76686ce7, I'm inclined to back-patch
   > to v18 only.
   >
   > Thoughts?
   
   Makes sense to me, but out of curiosity, while digging into these
   opfamily mismatches, have you noticed if this same record_ops vs
   record_image_ops inequality poses any risks to other optimisation
   paths like window function pushdowns or partition pruning? And
   apologies if that has already been discussed, but I couldn't find
   mention of it.
   
   Thom
   
   
   
   

3. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

   Richard Guo <guofenglinux@gmail.com> — 2026-05-26T23:04:17Z

   On Tue, May 26, 2026 at 11:06 PM Thom Brown <thom@linux.com> wrote:
   > Makes sense to me, but out of curiosity, while digging into these
   > opfamily mismatches, have you noticed if this same record_ops vs
   > record_image_ops inequality poses any risks to other optimisation
   > paths like window function pushdowns or partition pruning? And
   > apologies if that has already been discussed, but I couldn't find
   > mention of it.
   
   Thanks for raising these points.  For partition pruning,
   match_clause_to_partition_key() already checks both collation and
   opfamily compatibility, so I don't think it has similar issues.  I'm
   not sure what is meant by "window function pushdowns", but your
   question prompted me to look around, and I did notice that pushing
   restriction clauses down into a subquery suffers from a similar
   problem, specifically, when the subquery has DISTINCT, DISTINCT ON, or
   a window PARTITION BY clause.
   
   create type t_rec as (a numeric);
   create table t (a t_rec, b int);
   insert into t values (row(1.0), 10), (row(1.00), 20);
   
   -- wrong result: should be 0 rows
   select * from
     (select distinct on (a) a, b from t order by a, b) s
   where a *= row(1.00)::t_rec;
      a    | b
   --------+----
    (1.00) | 20
   (1 row)
   
   -- wrong result: rk should be 2
   select * from
     (select a, b, rank() over (partition by a order by b) as rk from t) s
   where a *= row(1.00)::t_rec;
      a    | b  | rk
   --------+----+----
    (1.00) | 20 |  1
   (1 row)
   
   In addtition, collation mismatch can also cause wrong results in this
   area.
   
   create collation ci (provider = icu, locale = 'und-u-ks-level2',
   deterministic = false);
   create table t1 (a text collate ci, b int);
   insert into t1 values ('abc', 1), ('ABC', 2);
   
   -- wrong result: should be 0 rows
   select * from
     (select distinct on (a) a, b from t1 order by a, b) s
   where a = 'ABC' collate "C";
     a  | b
   -----+---
    ABC | 2
   (1 row)
   
   -- wrong result: rk should be 2
   select * from
     (select a, b, rank() over (partition by a order by b) as rk from t1) s
   where a = 'ABC' collate "C";
     a  | b | rk
   -----+---+----
    ABC | 2 |  1
   (1 row)
   
   - Richard
   
   
   
   

4. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

   Richard Guo <guofenglinux@gmail.com> — 2026-05-28T09:11:25Z

   On Wed, May 27, 2026 at 8:04 AM Richard Guo <guofenglinux@gmail.com> wrote:
   > Thanks for raising these points.  For partition pruning,
   > match_clause_to_partition_key() already checks both collation and
   > opfamily compatibility, so I don't think it has similar issues.  I'm
   > not sure what is meant by "window function pushdowns", but your
   > question prompted me to look around, and I did notice that pushing
   > restriction clauses down into a subquery suffers from a similar
   > problem, specifically, when the subquery has DISTINCT, DISTINCT ON, or
   > a window PARTITION BY clause.
   
   I think all these issues belong to the same class of bug: the planner
   moves a qual clause across a grouping layer, and the result is wrong
   when the qual's equivalence relation disagrees with the grouping's,
   either an opfamily mismatch or a nondeterministic-collation mismatch.
   This includes HAVING-to-WHERE pushdown, as well as qual pushdown into
   a subquery past its DISTINCT, DISTINCT ON, window PARTITION BY, or
   set-operation grouping layer.
   
   v2 attached tries to fix the full bug class through a shared walker
   expression_has_grouping_conflict that detects either kind of conflict
   in an expression tree.  The walker takes a callback that maps each
   Var to the grouping equality operator for its column (or InvalidOid
   for non-grouping Vars).  See the commit message for details.
   
   - Richard
   

5. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

   Thom Brown <thom@linux.com> — 2026-05-28T10:47:04Z

   On Wed, 27 May 2026, 00:04 Richard Guo, <guofenglinux@gmail.com> wrote:
   
   > On Tue, May 26, 2026 at 11:06 PM Thom Brown <thom@linux.com> wrote:
   > > Makes sense to me, but out of curiosity, while digging into these
   > > opfamily mismatches, have you noticed if this same record_ops vs
   > > record_image_ops inequality poses any risks to other optimisation
   > > paths like window function pushdowns or partition pruning? And
   > > apologies if that has already been discussed, but I couldn't find
   > > mention of it.
   >
   > Thanks for raising these points.  For partition pruning,
   > match_clause_to_partition_key() already checks both collation and
   > opfamily compatibility, so I don't think it has similar issues.  I'm
   > not sure what is meant by "window function pushdowns", but your
   > question prompted me to look around, and I did notice that pushing
   > restriction clauses down into a subquery suffers from a similar
   > problem, specifically, when the subquery has DISTINCT, DISTINCT ON, or
   > a window PARTITION BY clause.
   >
   
   Yeah, sorry, that wording wasn't clear. I just meant pushing a qual down
   past a window function's PARTITION BY, which is the one case the planner
   allows. Looks like that's exactly the rank() case you turned up, so you've
   answered it better than I could have asked it. The v2 approach of treating
   the whole thing as one bug class looks like the right call to me.
   
   Regards
   
   Thom
   
   >
   

6. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

   Florin Irion <irionr@gmail.com> — 2026-06-18T15:34:03Z

   Il giorno gio 28 mag 2026 alle ore 11:11 Richard Guo <guofenglinux@gmail.com>
   ha scritto:
   
   > On Wed, May 27, 2026 at 8:04 AM Richard Guo <guofenglinux@gmail.com>
   > wrote:
   > > Thanks for raising these points.  For partition pruning,
   > > match_clause_to_partition_key() already checks both collation and
   > > opfamily compatibility, so I don't think it has similar issues.  I'm
   > > not sure what is meant by "window function pushdowns", but your
   > > question prompted me to look around, and I did notice that pushing
   > > restriction clauses down into a subquery suffers from a similar
   > > problem, specifically, when the subquery has DISTINCT, DISTINCT ON, or
   > > a window PARTITION BY clause.
   >
   > I think all these issues belong to the same class of bug: the planner
   > moves a qual clause across a grouping layer, and the result is wrong
   > when the qual's equivalence relation disagrees with the grouping's,
   > either an opfamily mismatch or a nondeterministic-collation mismatch.
   > This includes HAVING-to-WHERE pushdown, as well as qual pushdown into
   > a subquery past its DISTINCT, DISTINCT ON, window PARTITION BY, or
   > set-operation grouping layer.
   >
   > v2 attached tries to fix the full bug class through a shared walker
   > expression_has_grouping_conflict that detects either kind of conflict
   > in an expression tree.  The walker takes a callback that maps each
   > Var to the grouping equality operator for its column (or InvalidOid
   > for non-grouping Vars).  See the commit message for details.
   >
   > - Richard
   
   
   Hi,
   
   The patch fixes DISTINCT/window/set-op subqueries and HAVING, but does it
   miss the analogous case for GROUP BY subqueries as the pushdown target?
   
   When an outer qual is pushed into a GROUP BY subquery it lands in
   havingQual (correct), but find_having_conflicts then misses the conflict
   because the pushed qual carries base-table Vars, not GROUP Vars — so the
   clause gets silently moved to WHERE, filtering before aggregation.
   
   Reproducer:
   ```
     CREATE TYPE t_rec AS (x numeric);
     CREATE TABLE t_grp (a t_rec);
     INSERT INTO t_grp VALUES (ROW(1.0)), (ROW(1.00)), (ROW(2));
   
     -- record_ops (default) considers 1.0 and 1.00 equal; record_image_ops
   does not.
     -- Expected: one row (1.0), count = 2
     -- Got:      one row (1.0), count = 1  (wrong)
     SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
     WHERE a *= ROW(1.0)::t_rec;
   ```
   
   EXPLAIN shows the *= filter pushed inside the aggregate scan rather than
   sitting above it as a Subquery Scan filter.
   
   Cheers,
   Florin
   -- 
       * Florin Irion  *
   *    https://www.enterprisedb.com <https://www.enterprisedb.com/>*
   

7. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

   Richard Guo <guofenglinux@gmail.com> — 2026-06-19T06:43:44Z

   On Fri, Jun 19, 2026 at 12:34 AM Florin Irion <irionr@gmail.com> wrote:
   > When an outer qual is pushed into a GROUP BY subquery it lands in havingQual (correct), but find_having_conflicts then misses the conflict because the pushed qual carries base-table Vars, not GROUP Vars — so the clause gets silently moved to WHERE, filtering before aggregation.
   
   I don't think so.  The pushed qual carries the GROUP Vars, not the
   base-table Vars.
   
   > Reproducer:
   > ```
   >   CREATE TYPE t_rec AS (x numeric);
   >   CREATE TABLE t_grp (a t_rec);
   >   INSERT INTO t_grp VALUES (ROW(1.0)), (ROW(1.00)), (ROW(2));
   >
   >   -- record_ops (default) considers 1.0 and 1.00 equal; record_image_ops does not.
   >   -- Expected: one row (1.0), count = 2
   >   -- Got:      one row (1.0), count = 1  (wrong)
   >   SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
   >   WHERE a *= ROW(1.0)::t_rec;
   > ```
   
   I ran your reproducer, and I got the Expected result:
   
   SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
     WHERE a *= ROW(1.0)::t_rec;
      a   | count
   -------+-------
    (1.0) |     2
   (1 row)
   
   Curious how you got the wrong result with this patch.
   
   > EXPLAIN shows the *= filter pushed inside the aggregate scan rather than sitting above it as a Subquery Scan filter.
   
   Here is the EXPLAIN I got:
   
   EXPLAIN (COSTS OFF)
   SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
     WHERE a *= ROW(1.0)::t_rec;
                 QUERY PLAN
   ---------------------------------------
    HashAggregate
      Group Key: t_grp.a
      Filter: (t_grp.a *= '(1.0)'::t_rec)
      ->  Seq Scan on t_grp
   (4 rows)
   
   So the filter stays in HAVING instead of being pushed to Scan, which
   is expected.  I wonder how you get a plan with the filter being pushed
   to scan.  Can you show your output of EXPLAIN?
   
   - Richard
   
   
   
   

8. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

   Florin Irion <irionr@gmail.com> — 2026-06-19T12:10:26Z

   Il giorno ven 19 giu 2026 alle ore 08:43 Richard Guo <guofenglinux@gmail.com>
   ha scritto:
   
   > On Fri, Jun 19, 2026 at 12:34 AM Florin Irion <irionr@gmail.com> wrote:
   > > When an outer qual is pushed into a GROUP BY subquery it lands in
   > havingQual (correct), but find_having_conflicts then misses the conflict
   > because the pushed qual carries base-table Vars, not GROUP Vars — so the
   > clause gets silently moved to WHERE, filtering before aggregation.
   >
   > I don't think so.  The pushed qual carries the GROUP Vars, not the
   > base-table Vars.
   >
   > > Reproducer:
   > > ```
   > >   CREATE TYPE t_rec AS (x numeric);
   > >   CREATE TABLE t_grp (a t_rec);
   > >   INSERT INTO t_grp VALUES (ROW(1.0)), (ROW(1.00)), (ROW(2));
   > >
   > >   -- record_ops (default) considers 1.0 and 1.00 equal; record_image_ops
   > does not.
   > >   -- Expected: one row (1.0), count = 2
   > >   -- Got:      one row (1.0), count = 1  (wrong)
   > >   SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
   > >   WHERE a *= ROW(1.0)::t_rec;
   > > ```
   >
   > I ran your reproducer, and I got the Expected result:
   >
   > SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
   >   WHERE a *= ROW(1.0)::t_rec;
   >    a   | count
   > -------+-------
   >  (1.0) |     2
   > (1 row)
   >
   > Curious how you got the wrong result with this patch.
   >
   > > EXPLAIN shows the *= filter pushed inside the aggregate scan rather than
   > sitting above it as a Subquery Scan filter.
   >
   > Here is the EXPLAIN I got:
   >
   > EXPLAIN (COSTS OFF)
   > SELECT * FROM (SELECT a, count(*) FROM t_grp GROUP BY a) s
   >   WHERE a *= ROW(1.0)::t_rec;
   >               QUERY PLAN
   > ---------------------------------------
   >  HashAggregate
   >    Group Key: t_grp.a
   >    Filter: (t_grp.a *= '(1.0)'::t_rec)
   >    ->  Seq Scan on t_grp
   > (4 rows)
   >
   > So the filter stays in HAVING instead of being pushed to Scan, which
   > is expected.  I wonder how you get a plan with the filter being pushed
   > to scan.  Can you show your output of EXPLAIN?
   >
   > - Richard
   >
   
   Huh, I'm not able to reproduce it anymore either,
   probably while reviewing it, I played a bit with some things and this test
   was using some bogus install.
   Sorry for the noise.
   
   
   Cheers,
   Florin
   
   -- 
       * Florin Irion  *
   *    https://www.enterprisedb.com <https://www.enterprisedb.com/>*
   

9. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

   Zsolt Parragi <zsolt.parragi@percona.com> — 2026-06-20T21:46:19Z

   Hello!
   
   I tested the patch, and I see two possible issues with it
   
   1. It seems to only look for direct operands:
   
   create type t_rec as (a numeric, b int);
   create table t_having (id int, r t_rec);
   insert into t_having values
     (1, row(100,1)::t_rec),
     (2, row(100.0,1)::t_rec),
     (3, row(2,2)::t_rec);
   select r, count(*) from t_having group by r having r *=
   row(100,1)::t_rec; -- 2, correct
   select r, count(*) from t_having group by r having
   row((r).a,(r).b)::t_rec *= row(100,1)::t_rec; -- 1, incorrect
   
   2. unknown operators (non btree/hash) seem to behave incorrectly, they
   default to non-conflicting but they should conflict?
   
   CREATE FUNCTION num_image_eq(numeric, numeric) RETURNS bool
     LANGUAGE sql IMMUTABLE AS $$ SELECT $1::text = $2::text $$;
   CREATE OPERATOR === (LEFTARG = numeric, RIGHTARG = numeric, FUNCTION =
   num_image_eq);
   CREATE TABLE g_hole (g numeric, v int);
   INSERT INTO g_hole VALUES (100, 1), (100.0, 2), (100.00, 3);
   SELECT g, count(*), sum(v) FROM g_hole GROUP BY g;   -- 100 | 3 | 6
   SELECT g, count(*), sum(v) FROM g_hole GROUP BY g HAVING g === 100.0;
   -- 100 | 1 | 2
   
   
   
   

10. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Richard Guo <guofenglinux@gmail.com> — 2026-06-23T01:20:32Z

    On Sun, Jun 21, 2026 at 6:46 AM Zsolt Parragi <zsolt.parragi@percona.com> wrote:
    > 1. It seems to only look for direct operands:
    >
    > create type t_rec as (a numeric, b int);
    > create table t_having (id int, r t_rec);
    > insert into t_having values
    >   (1, row(100,1)::t_rec),
    >   (2, row(100.0,1)::t_rec),
    >   (3, row(2,2)::t_rec);
    > select r, count(*) from t_having group by r having r *=
    > row(100,1)::t_rec; -- 2, correct
    > select r, count(*) from t_having group by r having
    > row((r).a,(r).b)::t_rec *= row(100,1)::t_rec; -- 1, incorrect
    
    This is a known limitation, and has been documented in the comment of
    comparison_has_grouping_eqop_conflict():
    
     * Only direct Var operands (after stripping RelabelType wrappers) are checked.
     * A grouping-column Var wrapped in a function or other expression can slip a
     * different-opfamily comparison past this check.  Such cases are rare enough
     * that the recursive operand search that would catch them isn't justified.
    
    The difficulty is that cathing such cases requires semantic analysis
    of the function.  Consider WHERE length(grouping_var) > 42 where
    grouping_var is text.  equality_ops_are_compatible(int4gt, texteq)
    returns false and the walker would flag conflict, which is wrong as
    that's a safe pushdown.
    
    On the other hand, consider WHERE f(grouping_var) = some_const with
    the same opfamily as the grouping.  It passes the check.  But if f
    doesn't preserve the equivalence (e.g., f exposes bytewise differences
    that record_ops = considers equal), pushing it inside can still flip
    the result.  The outer op's opfamily matches the grouping eqop, so
    there's nothing for our walker to detect.  The bug is "function fails
    to preserve the equivalence", which would require semantic analysis
    of f.
    
    So given that such cases are rare^2 in practice, I don't think it's
    worth all the effort to detect.  I'd rather document the limitation as
    the current patch does.
    
    > 2. unknown operators (non btree/hash) seem to behave incorrectly, they
    > default to non-conflicting but they should conflict?
    >
    > CREATE FUNCTION num_image_eq(numeric, numeric) RETURNS bool
    >   LANGUAGE sql IMMUTABLE AS $$ SELECT $1::text = $2::text $$;
    > CREATE OPERATOR === (LEFTARG = numeric, RIGHTARG = numeric, FUNCTION =
    > num_image_eq);
    > CREATE TABLE g_hole (g numeric, v int);
    > INSERT INTO g_hole VALUES (100, 1), (100.0, 2), (100.00, 3);
    > SELECT g, count(*), sum(v) FROM g_hole GROUP BY g;   -- 100 | 3 | 6
    > SELECT g, count(*), sum(v) FROM g_hole GROUP BY g HAVING g === 100.0;
    > -- 100 | 1 | 2
    
    This is also documented in the comment of
    comparison_has_grouping_eqop_conflict():
    
     * Operators not in any btree/hash opfamily are skipped
     * (see the header comment on op_is_safe_index_member).
    
    For operators not in btree/hash opfamily, we have no way to know their
    equivalence relation, so we can't prove a conflict.
    
    - Richard
    
    
    
    

11. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Zsolt Parragi <zsolt.parragi@percona.com> — 2026-06-23T15:52:18Z

    I spent some more time thinking about my previous comments, as shortly
    after sending them I realized that it isn't as simple as I originally
    thought. I agree that a complete solution requires semantic analysis
    and is unrealistic.
    
    I ended up with a prototype that might slightly improve how these
    scenarios are handled. I attached two small patches containing it.
    These should handle most incorrect result issues, on the other hand
    they might result in some missed pushdowns. However, unless I'm
    mistaken, common cases should still be pushed down correctly as
    before?
    
    > The difficulty is that cathing such cases requires semantic analysis
    > of the function
    > ...
    > (e.g., f exposes bytewise differences
    > that record_ops = considers equal)
    
    Instead of a full semantic analysis, wouldn't it be a good heuristic
    to rely on btree's equalimage, or something similar?
    If there's a wrapped non-image-faithful grouping column in a having
    clause, we don't push it down, as we can't be sure about its use.
    
    This works correctly for the above examples, but it still results in
    some missed opportunities, for example `GROUP BY n HAVING abs(n) >
    100`, where n is a numeric column.
    There's also the question of collations which I simply ignored in this poc.
    
    > For operators not in btree/hash opfamily, we have no way to know their
    > equivalence relation, so we can't prove a conflict.
    
    Yes, my suggestion was to instead of saying "if we can't provide a
    conflict, it's safe" we could say "if we can't provide that it's safe,
    it is a conflict".
    
    -       if (!OidIsValid(opno) || !op_is_safe_index_member(opno))
    +       if (!OidIsValid(opno) || get_op_rettype(opno) != BOOLOID)
    
    By itself, this is way too eager and rejects cases it shouldn't, but
    by depending on the same equalimage logic as above, that seems
    (mostly) fixable to me?
    
    (my original second example was incorrect, the language sql operator
    get's inlined so it only showcases the first issue differently. a
    proper example for that requires a plpgsql or other non sql function)
    

12. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Richard Guo <guofenglinux@gmail.com> — 2026-06-24T02:52:40Z

    On Wed, Jun 24, 2026 at 12:52 AM Zsolt Parragi
    <zsolt.parragi@percona.com> wrote:
    > I spent some more time thinking about my previous comments, as shortly
    > after sending them I realized that it isn't as simple as I originally
    > thought. I agree that a complete solution requires semantic analysis
    > and is unrealistic.
    >
    > I ended up with a prototype that might slightly improve how these
    > scenarios are handled. I attached two small patches containing it.
    > These should handle most incorrect result issues, on the other hand
    > they might result in some missed pushdowns. However, unless I'm
    > mistaken, common cases should still be pushed down correctly as
    > before?
    
    Thanks for the patches and for digging into these two loose ends.  I
    considered your idea, but I'm inclined not to apply it, for a few
    reasons.
    
    My main concern is that it can cause plan regressions.  It treats any
    wrapper over a non-image-faithful grouping column as unsafe, but it
    cannot tell a wrapper that exposes the hidden distinction from one
    that provably cannot split a group.  This mean queries with "GROUP BY
    on a numeric/float/record column" would get slower even if they do not
    have the unsafe-pushdown issue.
    
    There's also the planning cost.  The equalimage probe does a
    SearchSysCacheList1 plus get_opfamily_proc plus an actual proc call
    for every operand of every comparison considered for pushdown.  That
    cost is paid even for integer or deterministic-text grouping columns,
    in which case we'd be spending the overhead just to reconfirm the
    common safe case.
    
    Stepping back: these unsafe-pushdown issues have been in the tree for
    many years with no field reports.  The current patch aims to fix the
    most common cases with the least overhead, and to stay conservative
    where covering a rare case would mean a lot more planning work and the
    risk of a performance regression.
    
    The equalimage idea itself is a nice observation, and if we ever
    decide the wrapped case is common enough to be worth addressing, it's
    probably the right tool.  For now, I'm inclined to leave the two loose
    ends documented as known limitations.
    
    - Richard
    
    
    
    

13. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Tender Wang <tndrwang@gmail.com> — 2026-06-29T02:31:30Z

    Hi Richard,
    
    Richard Guo <guofenglinux@gmail.com> 于2026年5月28日周四 17:11写道：
    >
    > On Wed, May 27, 2026 at 8:04 AM Richard Guo <guofenglinux@gmail.com> wrote:
    > > Thanks for raising these points.  For partition pruning,
    > > match_clause_to_partition_key() already checks both collation and
    > > opfamily compatibility, so I don't think it has similar issues.  I'm
    > > not sure what is meant by "window function pushdowns", but your
    > > question prompted me to look around, and I did notice that pushing
    > > restriction clauses down into a subquery suffers from a similar
    > > problem, specifically, when the subquery has DISTINCT, DISTINCT ON, or
    > > a window PARTITION BY clause.
    >
    > I think all these issues belong to the same class of bug: the planner
    > moves a qual clause across a grouping layer, and the result is wrong
    > when the qual's equivalence relation disagrees with the grouping's,
    > either an opfamily mismatch or a nondeterministic-collation mismatch.
    > This includes HAVING-to-WHERE pushdown, as well as qual pushdown into
    > a subquery past its DISTINCT, DISTINCT ON, window PARTITION BY, or
    > set-operation grouping layer.
    >
    > v2 attached tries to fix the full bug class through a shared walker
    > expression_has_grouping_conflict that detects either kind of conflict
    > in an expression tree.  The walker takes a callback that maps each
    > Var to the grouping equality operator for its column (or InvalidOid
    > for non-grouping Vars).  See the commit message for details.
    >
    I applied the v2 patch; it fixed the reported issue in [1].
    But it failed on Chengpeng Yan's reported case.
    
    SELECT x, c
    FROM (
    SELECT x, count(*) OVER (PARTITION BY x) AS c
    FROM t
    ) s
    WHERE ascii(x) = 97;
    NOTICE:  using standard form "und-u-ks-level2" for ICU locale
    "@colStrength=secondary"
    CREATE COLLATION
    CREATE TABLE
    INSERT 0 2
      x  | c
    -----+---
     abc | 1
    (1 row)
    
    
    
    [1] https://www.postgresql.org/message-id/19534-9cdf4693c42033da%40postgresql.org
    
    
    
    -- 
    Thanks,
    Tender Wang
    
    
    
    

14. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Tender Wang <tndrwang@gmail.com> — 2026-06-29T08:32:16Z

    Tender Wang <tndrwang@gmail.com> 于2026年6月29日周一 10:31写道：
    >
    > Hi Richard,
    > I applied the v2 patch; it fixed the reported issue in [1].
    > But it failed on Chengpeng Yan's reported case.
    >
    > SELECT x, c
    > FROM (
    > SELECT x, count(*) OVER (PARTITION BY x) AS c
    > FROM t
    > ) s
    > WHERE ascii(x) = 97;
    > NOTICE:  using standard form "und-u-ks-level2" for ICU locale
    > "@colStrength=secondary"
    > CREATE COLLATION
    > CREATE TABLE
    > INSERT 0 2
    >   x  | c
    > -----+---
    >  abc | 1
    > (1 row)
    >
    
    Only comparing expr's inputcollid to varcollid doesn't seem enough here.
    We don't know whether the FunExpr inside uses the inputcollid; in this
    case, ascii() is collation-unaware.
    Now we don't have the infrastructure to know whether a function is
    collation-aware.
    
    If we find a function wrapped on the Var, we give up the qual pushed down.
    We still have the optimization opportunity that if the OpExpr contains
    a bare Var on one side.
    
    
    -- 
    Thanks,
    Tender Wang
    
    
    
    

15. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Chengpeng Yan <chengpeng_yan@outlook.com> — 2026-06-29T10:59:31Z

    Hi,
    
    > On May 28, 2026, at 17:11, Richard Guo <guofenglinux@gmail.com> wrote:
    > 
    > v2 attached tries to fix the full bug class through a shared walker
    > expression_has_grouping_conflict that detects either kind of conflict
    > in an expression tree.  The walker takes a callback that maps each
    > Var to the grouping equality operator for its column (or InvalidOid
    > for non-grouping Vars).  See the commit message for details.
    
    I haven't yet reviewed all details of the patches in this thread, so
    this is mostly a question about the direction.
    
    Following up on the BUG #19534 example Tender posted, I think the
    WindowAgg case has the same root cause, but the patch I posted only
    targets the nondeterministic PARTITION BY case. An ordinary qual can be
    treated as pushdown-safe because it references a PARTITION BY output
    column, but the qual is not necessarily constant over the partition
    equality class. For example, with a nondeterministic collation,
    `ascii(x) = 97` can distinguish values that the partition equality
    treats as equal.
    
    I posted a small patch for that specific window case in [1]. It takes a
    conservative approach: if an output column matches a window PARTITION BY
    key whose collation is nondeterministic, ordinary qual pushdown is not
    allowed for that column. It does not try to classify arbitrary
    expressions, and deterministic partition keys keep the existing
    behavior.
    
    The tradeoff would be that some otherwise-safe pushdowns for
    nondeterministic partition keys may be missed, but the scope is limited
    to that case. Would that tradeoff be acceptable?
    
    HAVING-to-WHERE seems to have the same underlying issue. If the above
    direction is reasonable for WindowAgg, I wonder whether a similar rule
    would also make sense there: if a HAVING clause references a
    nondeterministic GROUP BY key, keep it in HAVING instead of trying to
    prove that the clause is constant over the group equality class.
    
    Does that seem like a reasonable direction?
    
    [1] https://www.postgresql.org/message-id/544F3673-0EC9-4440-9FC5-4BB4F0AA3037%40outlook.com
    
    --
    Best regards,
    Chengpeng Yan
    
    
    
    
    
    

16. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Richard Guo <guofenglinux@gmail.com> — 2026-06-30T09:22:15Z

    On Mon, Jun 29, 2026 at 5:32 PM Tender Wang <tndrwang@gmail.com> wrote:
    > If we find a function wrapped on the Var, we give up the qual pushed down.
    > We still have the optimization opportunity that if the OpExpr contains
    > a bare Var on one side.
    
    After some consideration, I'm convinced this is the right solution.
    The previous patches detected an unsafe push with a walker that kept a
    stack of the inputcollids contributed by the ancestors of each
    grouping Var.  At a Var with a nondeterministic collation, it reported
    a conflict when any ancestor on the path applied a different collation.
    The intent was that a function over the column was fine as long as the
    same nondeterministic collation was carried all the way down.
    
    That approach is unsound.  It assumes that any function carrying the
    grouping collation also preserves the collation's notion of equality,
    which is simply not true.  As an example, consider:
    
      WHERE ascii(x)::text = '97' COLLATE ci;
    
    ascii() leaves the collatable domain and ::text brings it back, so the
    stack sees ci from top to bottom and allows the push, yet the
    comparison distinguishes values the grouping had merged.  The root
    issue is that we have no way to prove an arbitrary expression
    preserves that equality.
    
    The attached patch drops the stack for a simpler rule: a grouping
    column with a nondeterministic collation is safe to push only as a
    direct operand of a comparison under its own collation.
    
    This would make us fail to push some clauses that are in fact safe but
    that we cannot prove safe.  But I think those cases are very narrow:
    they require a nondeterministic grouping key with a function-wrapped
    qual on it, and a plain direct comparison such as x = 'foo' COLLATE ci
    still pushes, so the optimization loss is small and limited to
    nondeterministic collations.
    
    Thoughts?
    
    - Richard
    

17. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Tender Wang <tndrwang@gmail.com> — 2026-07-01T01:49:31Z

    Richard Guo <guofenglinux@gmail.com> 于2026年6月30日周二 17:22写道：
    > This would make us fail to push some clauses that are in fact safe but
    > that we cannot prove safe.  But I think those cases are very narrow:
    > they require a nondeterministic grouping key with a function-wrapped
    > qual on it, and a plain direct comparison such as x = 'foo' COLLATE ci
    > still pushes, so the optimization loss is small and limited to
    > nondeterministic collations.
    >
    > Thoughts?
    
    It works for me.
    I ran the issued query on the v3 patch, and it returned the correct result.
    Before looking at the v3 patch in detail, I found another query as below:
    postgres=# explain  SELECT x, y, part_sum
    FROM (
      SELECT x, y, sum(y) OVER (PARTITION BY x COLLATE case_sensitive) AS part_sum
      FROM t_window_ci
    ) s
    WHERE x = 'abc' COLLATE case_sensitive
    ORDER BY x, y;
                                           QUERY PLAN
    ----------------------------------------------------------------------------------------
     Sort  (cost=126.35..126.36 rows=6 width=44)
       Sort Key: s.x COLLATE case_insensitive, s.y
       ->  Subquery Scan on s  (cost=88.26..126.27 rows=6 width=44)
             Filter: (s.x = 'abc'::text COLLATE case_sensitive)
             ->  WindowAgg  (cost=88.26..110.40 rows=1270 width=76)
                   Window: w1 AS (PARTITION BY ((t_window_ci.x)::text))
                   ->  Sort  (cost=88.17..91.35 rows=1270 width=68)
                         Sort Key: ((t_window_ci.x)::text) COLLATE case_sensitive
                         ->  Seq Scan on t_window_ci  (cost=0.00..22.70
    rows=1270 width=68)
    (9 rows)
    
    In the above query, the partition by clause and the where clause use
    the same collation.
    It's safe to push qual down into the subquery. But now on HEAD, it fails.
    Is it worth fixing?
    
    
    -- 
    Thanks,
    Tender Wang
    
    
    
    

18. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Richard Guo <guofenglinux@gmail.com> — 2026-07-01T02:04:35Z

    On Wed, Jul 1, 2026 at 10:49 AM Tender Wang <tndrwang@gmail.com> wrote:
    > Before looking at the v3 patch in detail, I found another query as below:
    > postgres=# explain  SELECT x, y, part_sum
    > FROM (
    >   SELECT x, y, sum(y) OVER (PARTITION BY x COLLATE case_sensitive) AS part_sum
    >   FROM t_window_ci
    > ) s
    > WHERE x = 'abc' COLLATE case_sensitive
    > ORDER BY x, y;
    >                                        QUERY PLAN
    > ----------------------------------------------------------------------------------------
    >  Sort  (cost=126.35..126.36 rows=6 width=44)
    >    Sort Key: s.x COLLATE case_insensitive, s.y
    >    ->  Subquery Scan on s  (cost=88.26..126.27 rows=6 width=44)
    >          Filter: (s.x = 'abc'::text COLLATE case_sensitive)
    >          ->  WindowAgg  (cost=88.26..110.40 rows=1270 width=76)
    >                Window: w1 AS (PARTITION BY ((t_window_ci.x)::text))
    >                ->  Sort  (cost=88.17..91.35 rows=1270 width=68)
    >                      Sort Key: ((t_window_ci.x)::text) COLLATE case_sensitive
    >                      ->  Seq Scan on t_window_ci  (cost=0.00..22.70
    > rows=1270 width=68)
    > (9 rows)
    >
    > In the above query, the partition by clause and the where clause use
    > the same collation.
    > It's safe to push qual down into the subquery. But now on HEAD, it fails.
    > Is it worth fixing?
    
    IIUC, this has nothing to do with what we are trying to fix here.  For
    this query, the qual cannot be pushed down either even without this
    patch.  So I don't feel motivated to touch it in this patch.
    
    - Richard
    
    
    
    

19. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Tender Wang <tndrwang@gmail.com> — 2026-07-01T05:58:48Z

    Richard Guo <guofenglinux@gmail.com> 于2026年6月30日周二 17:22写道：
    >
    > On Mon, Jun 29, 2026 at 5:32 PM Tender Wang <tndrwang@gmail.com> wrote:
    > > If we find a function wrapped on the Var, we give up the qual pushed down.
    > > We still have the optimization opportunity that if the OpExpr contains
    > > a bare Var on one side.
    > This would make us fail to push some clauses that are in fact safe but
    > that we cannot prove safe.  But I think those cases are very narrow:
    > they require a nondeterministic grouping key with a function-wrapped
    > qual on it, and a plain direct comparison such as x = 'foo' COLLATE ci
    > still pushes, so the optimization loss is small and limited to
    > nondeterministic collations.
    >
    > Thoughts?
    
    I look through the v3 patch.  I didn't find an obvious issue. +1
    
    > IIUC, this has nothing to do with what we are trying to fix here.  For
    > this query, the qual cannot be pushed down either even without this
    > patch.  So I don't feel motivated to touch it in this patch.
    
    Yeah, the previous query I reported has nothing to do with the topic
    we're talking about here.
    Ignore the noise.
    
    
    -- 
    Thanks,
    Tender Wang
    
    
    
    

20. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Richard Guo <guofenglinux@gmail.com> — 2026-07-06T07:32:37Z

    On Wed, Jul 1, 2026 at 2:59 PM Tender Wang <tndrwang@gmail.com> wrote:
    > I look through the v3 patch.  I didn't find an obvious issue. +1
    
    Thanks all for the reviews.  I've committed this patch and backported
    it to v18.
    
    - Richard
    
    
    
    

21. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Fujii Masao <masao.fujii@gmail.com> — 2026-07-06T22:43:50Z

    On Mon, Jul 6, 2026 at 4:32 PM Richard Guo <guofenglinux@gmail.com> wrote:
    >
    > On Wed, Jul 1, 2026 at 2:59 PM Tender Wang <tndrwang@gmail.com> wrote:
    > > I look through the v3 patch.  I didn't find an obvious issue. +1
    >
    > Thanks all for the reviews.  I've committed this patch and backported
    > it to v18.
    
    In HEAD, I found what looks like another case of the same issue. Is the
    cause essentially the same as the one fixed by your commit?
    
        --------------------------
        CREATE TEMP TABLE t (id int, num numeric);
        INSERT INTO t VALUES (1, numeric '1.0'), (2, numeric '1.00');
    
        SELECT * FROM (
            SELECT DISTINCT ON (num) id, num FROM t ORDER BY num, id
        ) s WHERE scale(num) = 2;
        --------------------------
    
    This query should return no rows. The subquery selects the first row
    (id = 1, num = 1.0), and the outer WHERE clause should then filter
    it out because scale(1.0) = 1, not 2.
    
    But, in HEAD it returns:
    
         id | num
        ----+------
          2 | 1.00
        (1 row)
    
    Regards,
    
    -- 
    Fujii Masao
    
    
    
    

22. Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

    Richard Guo <guofenglinux@gmail.com> — 2026-07-07T00:46:34Z

    On Tue, Jul 7, 2026 at 7:44 AM Fujii Masao <masao.fujii@gmail.com> wrote:
    > In HEAD, I found what looks like another case of the same issue. Is the
    > cause essentially the same as the one fixed by your commit?
    >
    >     --------------------------
    >     CREATE TEMP TABLE t (id int, num numeric);
    >     INSERT INTO t VALUES (1, numeric '1.0'), (2, numeric '1.00');
    >
    >     SELECT * FROM (
    >         SELECT DISTINCT ON (num) id, num FROM t ORDER BY num, id
    >     ) s WHERE scale(num) = 2;
    >     --------------------------
    >
    > This query should return no rows. The subquery selects the first row
    > (id = 1, num = 1.0), and the outer WHERE clause should then filter
    > it out because scale(1.0) = 1, not 2.
    
    This is a known limitation, and was previously reported by Zsolt
    Parragi at [1].  I explained why this patch doesn't address this in
    [2] and [3].  The limitation is also documented in the comment:
    
     * This leaves one case uncaught: with a deterministic collation, a function
     * over the column can still feed a finer comparison than the direct-operand
     * check sees, for example record_image_ops over a rebuilt record, or scale()
     * over numeric where two equal values differ in scale.  Catching it would
     * require knowing that a type's equality is bitwise, which we do not test
     * here.
    
    (Note that your specific repro is explicitly mentioned in this
    comment; see the part about "scale() over numeric where two equal
    values differ in scale".)
    
    I'm not entirely sure whether this edge case is common enough to
    warrant the complexity and overhead required to fix it.  If it does
    turn out to be a frequent issue, we might be able to address it down
    the road using Zsolt Parragi's equalimage approach.
    
    Thoughts?
    
    [1] https://postgr.es/m/CAN4CZFP4PrDi9-OKbFXTe9M1VEZHtj0nBxTiwM_p7fmZ9C2Xyw@mail.gmail.com
    [2] https://postgr.es/m/CAMbWs49tXgHvyD-7PwMShtHeoYUqxxM9i-=FKazLrQLPT_APTA@mail.gmail.com
    [3] https://postgr.es/m/CAMbWs48q6nO7_nZNrQaqaWHFcYT3g95ONYco90+0Lvi2WJgqag@mail.gmail.com
    
    - Richard