pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: imchifan@163.com
Subject: BUG #19713: WindowAgg qual pushdown gives wrong partition count when scale(numeric) distinguishes equal values
Date: Tue, 22 Sep 2026 16:01:34 +0000
Message-ID: <19713-635b8656d07464df@postgresql.org> (raw)

The following bug has been logged on the website:

Bug reference:      19713
Logged by:          Qifan Liu
Email address:      imchifan@163.com
PostgreSQL version: 18.6
Operating system:   Linux/amd64
Description:        

An outer scale(numeric) predicate is pushed below WindowAgg even though
numeric values 1.0 and 1.00 are equal under PARTITION BY semantics while
scale() distinguishes them. The pushed predicate removes one partition
member and produces an incorrect window count of 1 instead of 2.

Steps to reproduce
------------------
Run the following input with psql:

\set ON_ERROR_STOP on

BEGIN;

CREATE TEMP TABLE bugseer_postgres_00010_window_scale (x numeric);
INSERT INTO bugseer_postgres_00010_window_scale VALUES (1.0), (1.00);

CREATE FUNCTION bugseer_postgres_00010_identity_numeric_window(numeric)
RETURNS numeric
LANGUAGE plpgsql VOLATILE STRICT
AS 'BEGIN RETURN $1; END';

EXPLAIN (COSTS OFF)
SELECT n
FROM
(
  SELECT x, count(*) OVER (PARTITION BY x) AS n
  FROM bugseer_postgres_00010_window_scale
) s
WHERE scale(x) = 1;

EXPLAIN (COSTS OFF)
SELECT n
FROM
(
  SELECT x, count(*) OVER (PARTITION BY x) AS n
  FROM bugseer_postgres_00010_window_scale
) s
WHERE scale(bugseer_postgres_00010_identity_numeric_window(x)) = 1;

WITH optimized AS
(
  SELECT n
  FROM
  (
    SELECT x, count(*) OVER (PARTITION BY x) AS n
    FROM bugseer_postgres_00010_window_scale
  ) s
  WHERE scale(x) = 1
), baseline AS
(
  SELECT n
  FROM
  (
    SELECT x, count(*) OVER (PARTITION BY x) AS n
    FROM bugseer_postgres_00010_window_scale
  ) s
  WHERE scale(bugseer_postgres_00010_identity_numeric_window(x)) = 1
)
SELECT (SELECT min(n) FROM optimized) AS optimized_n,
       (SELECT min(n) FROM baseline) AS baseline_n,
       (SELECT min(n) FROM optimized) = (SELECT min(n) FROM baseline)
         AS invariant_holds;

ROLLBACK;

Actual result
-------------
The plain predicate appears as a sequential-scan filter below WindowAgg,
while the value-preserving volatile form remains above WindowAgg:

 optimized_n | baseline_n | invariant_holds
-------------+------------+-----------------
           1 |          2 | f
(1 row)

Expected result
---------------
Filtering must preserve every member equal under the window partition
semantics. Both forms should report partition count 2, and invariant_holds
should be true.

Additional information
----------------------
The issue was reproduced on PostgreSQL 20devel, PostgreSQL 18.6, and
PostgreSQL 17.11.
This issue may be similar to the three reports below [1–2], but I am unsure
whether they share the same root cause.
[1] [Fujii Masao’s scale(numeric)
report](https://www.postgresql.org/message-id/CAHGQGwGnPNX=nK+nXL0ihh+8WObQnFWjnPi5uvxBFvbtqN9WmQ@mail.gmail...)
[2] [Richard Guo’s explanation of the known
limitation](https://www.postgresql.org/message-id/CAMbWs4_=1kH=J2qDmdQm1NrM0oVPXO8dETOSaUfMUYatfbDbRA@mail.gmail...)







view thread (2+ messages)  latest in thread

Message-ID: <19713-635b8656d07464df@postgresql.org>
Permalink:  ../19713-635b8656d07464df@postgresql.org/
Also on:    postgresql.org/message-id/19713-635b8656d07464df@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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, imchifan@163.com
  Subject: Re: BUG #19713: WindowAgg qual pushdown gives wrong partition count when scale(numeric) distinguishes equal values
  In-Reply-To: <19713-635b8656d07464df@postgresql.org>
  References: <19713-635b8656d07464df@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox