agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE.
2+ messages / 2 participants
[nested] [flat]
* BUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE.
@ 2026-07-30 09:56 PG Bug reporting form <noreply@postgresql.org>
2026-08-05 13:54 ` Re: BUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE. Nitin Motiani <nitinmotiani@google.com>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-07-30 09:56 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: dllggyx@outlook.com
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;
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE.
2026-07-30 09:56 BUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE. PG Bug reporting form <noreply@postgresql.org>
@ 2026-08-05 13:54 ` Nitin Motiani <nitinmotiani@google.com>
0 siblings, 0 replies; 2+ messages in thread
From: Nitin Motiani @ 2026-08-05 13:54 UTC (permalink / raw)
To: Matheus Alcantara <matheusssilv97@gmail.com>; +Cc: dllggyx@outlook.com; pgsql-bugs@lists.postgresql.org
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] 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] https://www.postgresql.org/message-id/CAMbWs48q6nO7_nZNrQaqaWHFcYT3g95ONYco90%2B0Lvi2WJgqag%40mail.g...
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-08-05 13:54 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-07-30 09:56 BUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE. PG Bug reporting form <noreply@postgresql.org>
2026-08-05 13:54 ` Nitin Motiani <nitinmotiani@google.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox