Message-ID: <7ab4aac3-4790-416f-98b8-2ec0707cceb5@gmail.com>
Date: Thu, 19 Sep 2024 18:47:30 +0200
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
Subject: Volatile functions under Memoize node
To: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>,
 Richard Guo <guofenglinux@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/7ab4aac3-4790-416f-98b8-2ec0707cceb5%40gmail.com>
Precedence: bulk

Hi,

Excuse me if I made noise in vain. After discovering the limits of the 
Memoize node, I realized that volatile functions are allowed under the 
Memoize. Example:

DROP TABLE IF EXISTS base, other CASCADE;
CREATE TABLE base (x numeric, y text, x1 integer);
INSERT INTO base (x,y,x1) SELECT 1, 'abccccccccccccc'||1,1 FROM 
generate_series(1,1E6) AS x;
CREATE TABLE other (x numeric, y text, x1 integer);
INSERT INTO other (x,y,x1) SELECT x, 'abccccccccccccc'||x,1 FROM 
generate_series(1,1E4) AS x;
VACUUM ANALYZE base, other;


EXPLAIN (COSTS OFF)
SELECT * FROM base WHERE base.x IN (
   SELECT o2.x FROM other o1 LEFT JOIN other o2 ON (o1.x=o2.x) LEFT JOIN 
other o3 ON (o2.x=o3.x+random())
   WHERE base.x = o1.x GROUP BY o2.x ORDER BY o2.x
);

/*
  Nested Loop
    ->  Seq Scan on base
    ->  Memoize
          Cache Key: base.x
          Cache Mode: binary
          ->  Subquery Scan on "ANY_subquery"
                Filter: (base.x = "ANY_subquery".x)
                ->  Group
                      Group Key: o2.x
                      ->  Sort
                            Sort Key: o2.x
                            ->  Nested Loop Left Join
                                  Join Filter: ((o2.x)::double precision 
= ((o3.x)::double precision + random()))
                                  ->  Nested Loop Left Join
                                        ->  Seq Scan on other o1
                                              Filter: (base.x = x)
                                        ->  Seq Scan on other o2
                                              Filter: (x = base.x)
                                  ->  Seq Scan on other o3
*/

In my mind, any volatile function in any expression should reject the 
usage of Memoize, am I wrong? I haven't discovered this case deeply yet, 
but maybe someone has a quick and short answer to this question.

-- 
regards, Andrei Lepikhov