pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Cc: Andrei Lepikhov <lepihov@gmail.com>
Cc: 303677365@qq.com, pgsql-bugs@lists.postgresql.org, Tender Wang <tndrwang@gmail.com>
Subject: Re: BUG #19649: Qual pushdown into GROUP BY subqueries ignores non-equivalence-preserving references to grouping col
Date: Tue, 22 Sep 2026 12:52:43 -0400
Message-ID: <1188432.1790095963@sss.pgh.pa.us> (raw)
In-Reply-To: <CAB8bMivMXBOL3Utbaw0wmsNo76fP5H6aeSvek-92jzPH8ZWqGA@mail.gmail.com>
References: <19649-2cabf1440793cc71@postgresql.org>
	<CAB8bMiss5S2SZonboQfbTMJsNCd+k_J8AbeS-vXheVUrR_TUhQ@mail.gmail.com>
	<60c6b1f7-f435-4ca7-992a-482417af5130@gmail.com>
	<CAB8bMivStAVVSPonHGeP0-Uxb5bd2mgLNyBunYxHG1s+UxnKOQ@mail.gmail.com>
	<CAB8bMivO2qD9obEMdPxDpQ+Zanq_e_fHA2c1tKe=YeRxjnbXWA@mail.gmail.com>
	<CAB8bMisiMCwe3EQoP_3_4acvfsqcDKfTOf5iHdqE8JGLcqvDBg@mail.gmail.com>
	<7c27d19d-3324-4977-a8b2-ea89af0eac79@gmail.com>
	<CAB8bMiuPNt8zYr3k46rDxJAgC6hJ7pX456aRf1++EPNeHtD=PA@mail.gmail.com>
	<CAB8bMivMXBOL3Utbaw0wmsNo76fP5H6aeSvek-92jzPH8ZWqGA@mail.gmail.com>

Andrey Rachitskiy <pl0h0yp1@gmail.com> writes:
> Refuse to push a non-operand reference to a grouping column below the
> grouping boundary unless the grouping equality is image equality
> (BTEQUALIMAGE_PROC).  That covers jsonb, numeric, float8 and similar
> types, and subsumes the old nondeterministic-collation check for
> wrapped references.

The more I think about it, the less I like this solution approach.
The trouble with it is that in exchange for making ill-posed queries
(with no real-world use-cases) seem to have stable semantics, you
are going to cause a disastrous performance regression for perfectly
fine queries that do have real-world use.  I can't see us taking
that tradeoff.  Especially when the apparently stable semantics are
in fact no such thing, so that there isn't any useful gain from
making the code act that way.

What we really need here, perhaps, is a way to mark functions that
can distinguish values seen as equal by their datatype's default
equality operator.  I'm not sure of a good way to do that.  If we
did have such a marking, frankly what I would do with it would be
less like "don't push down past aggregation" and more like "throw
an error that this query has undefined results".  Otherwise we're
just going to replace bug reports of this shape with bug reports
about the query's result being unstable.

			regards, tom lane





view thread (10+ messages)

Message-ID: <1188432.1790095963@sss.pgh.pa.us>
Permalink:  ../1188432.1790095963@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1188432.1790095963@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, pl0h0yp1@gmail.com, lepihov@gmail.com, tndrwang@gmail.com
  Subject: Re: BUG #19649: Qual pushdown into GROUP BY subqueries ignores non-equivalence-preserving references to grouping col
  In-Reply-To: <1188432.1790095963@sss.pgh.pa.us>
  References: <19649-2cabf1440793cc71@postgresql.org> <CAB8bMiss5S2SZonboQfbTMJsNCd+k_J8AbeS-vXheVUrR_TUhQ@mail.gmail.com> <60c6b1f7-f435-4ca7-992a-482417af5130@gmail.com> <CAB8bMivStAVVSPonHGeP0-Uxb5bd2mgLNyBunYxHG1s+UxnKOQ@mail.gmail.com> <CAB8bMivO2qD9obEMdPxDpQ+Zanq_e_fHA2c1tKe=YeRxjnbXWA@mail.gmail.com> <CAB8bMisiMCwe3EQoP_3_4acvfsqcDKfTOf5iHdqE8JGLcqvDBg@mail.gmail.com> <7c27d19d-3324-4977-a8b2-ea89af0eac79@gmail.com> <CAB8bMiuPNt8zYr3k46rDxJAgC6hJ7pX456aRf1++EPNeHtD=PA@mail.gmail.com> <CAB8bMivMXBOL3Utbaw0wmsNo76fP5H6aeSvek-92jzPH8ZWqGA@mail.gmail.com> <1188432.1790095963@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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