agora inbox for pgsql-bugs@postgresql.org  

help / color / mirror / Atom feed

From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: dllggyx@outlook.com
Subject: BUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE.
Date: Thu, 30 Jul 2026 09:56:45 +0000
Message-ID: <19588-e32b82433b3660ea@postgresql.org> (raw)

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;

view thread (2+ messages)  latest in thread

Message-ID: <19588-e32b82433b3660ea@postgresql.org>
Permalink:  ../19588-e32b82433b3660ea@postgresql.org/
Also on:    postgresql.org/message-id/19588-e32b82433b3660ea@postgresql.org

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 using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, dllggyx@outlook.com
  Subject: Re: BUG #19588: Semantically equivalent DISTINCT ON query returns different result when wrapped in MATERIALIZED CTE.
  In-Reply-To: <19588-e32b82433b3660ea@postgresql.org>

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

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox