Re: 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(at)outlook(dot)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