Re: BUG #19649: Qual pushdown into GROUP BY subqueries ignores non-equivalence-preserving references to grouping col

On 05/09/2026 12:45, Andrey Rachitskiy wrote:
> I attach v4, it is v2 with fixed comment.
> Sorry for the noise, I'll be more attentive and take my time. 
>

Thanks for the quick turnaround.

My concern is with the approach, not the code itself. The key change in v4 is a
single line:

if (getBaseType(var->vartype) != JSONBOID)
return false;

This only addresses the specific type mentioned in the report. However, the
reporter could have demonstrated the same bug using numeric, without involving
jsonb at all. The same goes for float8. In core, the default btree opclasses
that make no image-equality promise are numeric, float8, interval, jsonb, record
and tsvector, among others. Group by any of those and wrap the column in any
expression, and the qual moves. This means we will see this issue reported
again, just with a different type mentioned.
What's more, I think your solution not even full. Just check something like the
following:

SELECT j, count(*) FROM t GROUP BY j HAVING starts_with(j::text, '1.');

So, how can we address the broader issue rather than just this specific case?

The property we need is already in the catalogue. Peter and Anastasia added
equalimage support functions in 612a1ab7672 for btree deduplication, and the
documented contract is exactly what we need. If that holds, no wrapping
expression can distinguish values that the grouping merged, whatever the wrapper is.

Even this approach is not free from issues, quite narrow ones though. If I
understand correctly, image equality is not quite the same as bitwise equality
for varlena, since TOAST compression is not applied consistently, so functions
like pg_column_size() can still be inconsistent. The attachment is a LLM
generated patch that demonstrates the idea, maybe in too much detail. One way or
another, we end up with worse query plans, sometimes for nothing.

Postgres is not the only one facing this issue. Let's try to find ideas in
others' experience.

The mainstream solution is to remove the coarse equality from the type system.
DuckDB canonicalises -0.0 and gives DECIMAL a fixed per-column scale. ClickHouse
allows COLLATE only in ORDER BY and fixes DECIMAL scale as well. Equality
becomes image equality, and the optimiser needs no guard at all.

The second is to declare the result unspecified [1]. SQLite reproduces our bug
through type affinity, and their answer is that the affinity of such a column is
indeterminate and the group representative is arbitrary, so any result is legal.
Some discussions in the Internet give me an idea that SQL Server restrict clause
pushdown in such cases.

I personally prefer the second approach, possibly with an image equality check.
GROUP BY already hands back an arbitrary member of the group. Postgres does not
promise which one, and any expression that can distinguish members of the class
is therefore reading something we never guaranteed.

[1] https://sqlite.org/forum/info/6dc048f81303cb97

--
regards, Andrei Lepikhov,
pgEdge