BUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE.

On Thu Jul 30, 2026 at 6:56 AM -03, PG Bug reporting form wrote:

The following bug has been logged on the website:

Bug reference: 19588
Logged by: Yuxiao Guo
Email address: dllggyx@outlook.com
PostgreSQL version: 18.4
Operating system: Ubuntu 20.04 x86-64, docker image postgres:18.4
Description:

The two forms are semantically equivalent, but I observed different results.
The first version returned 1.0, while the second version returned empty set.

PoC:

CREATE TABLE t0(c0 numeric);
INSERT INTO t0 VALUES (1.0), (1.00);

-- Query A, result: {1.0}
SELECT * FROM (
SELECT DISTINCT ON (c0) c0
FROM t0
ORDER BY c0, scale(c0) DESC
) t1
WHERE scale(c0) = 1;

-- Query B, result: empty set
WITH t1 AS MATERIALIZED (
SELECT DISTINCT ON (c0) c0
FROM t0
ORDER BY c0, scale(c0) DESC
)
SELECT * FROM t1 WHERE scale(c0) = 1;

Hi,

Thanks for the report. I've reproduced on master, and the second query
is the correct one, the result should be the empty set. The subquery
alone return 1.00, since 1.0 and 1.00 are equal and scale(c0) DESC
breaks the tie in favour of the larger scale. Query A returns 1.0
because the qual is pushed below the Unique node:

Subquery Scan on t1
-> Unique
-> Sort
Sort Key: t0.c0
-> Seq Scan on t0
Filter: (scale(t0.c0) = 1)

IIUC the root cause is that a type's equality need not be identity,
numeric equality ignores display scale, so a function over the grouping
column can distinguish values the grouping merged. Commit 44fb59fc605
fixed two other ways that assumption fails.

An idea for fixing it: The catalogs already record whether a type's
equality implies identity, since btree deduplication needs exactly that
guarantee and gets it from the optional BTEQUALIMAGE_PROC support
function, which numeric_ops, float8_ops, interval_ops and record_ops all
lack, precisely because equal values there can be byte-distinct. The
check added by 44fb59fc605 could consult it wherever a grouping column
is referenced other than as a direct operand of a comparison testing the
grouping's own equality and when equality is identity, every member of a
group is byte-identical and no expression can tell them apart, so the
reference is safe.

The side effect is that this reasons about the type rather than the
function, so it would block quals that were always safe, e.g round(n) =
5 respects numeric equality and could never split a group, but the
planner cannot tell it from scale(n) = 1 without proving something about
the function body, so it would no longer be pushed past the grouping,
which can cause performance issues for such cases.

This is the only way I can see to be correct here today. Recovering
those cases would need a notion of "this function preserves its input
type's equality", which AFAIK does not exist. Is there a better way to
attack this?

--
Matheus Alcantara
EDB: https://www.enterprisedb.com

Hi,

Thanks for looking into this.

An idea for fixing it: The catalogs already record whether a type's
equality implies identity, since btree deduplication needs exactly that
guarantee and gets it from the optional BTEQUALIMAGE_PROC support
function, which numeric_ops, float8_ops, interval_ops and record_ops all
lack, precisely because equal values there can be byte-distinct. The
check added by 44fb59fc605 could consult it wherever a grouping column
is referenced other than as a direct operand of a comparison testing the
grouping's own equality and when equality is identity, every member of a
group is byte-identical and no expression can tell them apart, so the
reference is safe.

I have been tinkering with the same idea of using equalimage_proc for
a WIP patch.

The side effect is that this reasons about the type rather than the
function, so it would block quals that were always safe, e.g round(n) =
5 respects numeric equality and could never split a group, but the
planner cannot tell it from scale(n) = 1 without proving something about
the function body, so it would no longer be pushed past the grouping,
which can cause performance issues for such cases.

But I found that the original thread for 44fb59fc605 [1]/messages/by-id/CAMbWs48q6nO7_nZNrQaqaWHFcYT3g95ONYco90+0Lvi2WJgqag@mail.gmail.com already
considered something like this and didn't go with it due to the same
performance impllications. Perhaps it is worth revisiting now that
there has been an actual report with this issue. The original thread
also notes that planning cost will increase for other types like
integer too. I think the planning cost issue might be mitigated by
adding a fast path for image-faithful types. But the performance hit
for expressions like round(n) seems unavoidable.

[1]: /messages/by-id/CAMbWs48q6nO7_nZNrQaqaWHFcYT3g95ONYco90+0Lvi2WJgqag@mail.gmail.com