Horton

Re: Bug in row_number() optimization

Sergey Shinderuk <s.shinderuk@postgrespro.ru>

View thread View on postgr.es
From: Sergey Shinderuk <s.shinderuk@postgrespro.ru>
To: Richard Guo <guofenglinux@gmail.com>, David Rowley <dgrowleyml@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@postgresql.org>, David Rowley <drowley@postgresql.org>
Date: 2022-11-25T16:01:09Z
Lists: pgsql-hackers 
On 25.11.2022 15:46, Richard Guo wrote:
> Considering the 'Filter' is a strict function, marking it as
> NULL would do.  I think this is why this patch works.

What about user-defined operators? I created my own <= operator for int8 
which returns true on null input, and put it in a btree operator class. 
With my operator I get:

   depname  | empno | salary | enroll_date | c1 | rn | c2 | c3
-----------+-------+--------+-------------+----+----+----+----
  personnel |     5 |   3500 | 2007-12-10  |  2 |  1 |  2 |  2
  sales     |     3 |   4800 | 2007-08-01  |  3 |  1 |  3 |  3
  sales     |     4 |   4800 | 2007-08-08  |  3 |    |    |  3
(3 rows)

Admittedly, it's weird that (null <= 1) evaluates to true. But does it 
violate  the contract of the btree operator class or something? Didn't 
find a clear answer in the docs.

-- 
Sergey Shinderuk		https://postgrespro.com/

Commits

  1. Fix 32-bit build dangling pointer issue in WindowAgg

  2. Teach planner and executor about monotonic window funcs