Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tq9EH-000B9s-DP for pgsql-bugs@arkaria.postgresql.org; Thu, 06 Mar 2025 11:17:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tq9EG-007lME-4Q for pgsql-bugs@arkaria.postgresql.org; Thu, 06 Mar 2025 11:17:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tq9EF-007lM6-RX for pgsql-bugs@lists.postgresql.org; Thu, 06 Mar 2025 11:17:27 +0000 Received: from mail-wr1-x42b.google.com ([2a00:1450:4864:20::42b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tq9EC-001HhC-0B for pgsql-bugs@lists.postgresql.org; Thu, 06 Mar 2025 11:17:27 +0000 Received: by mail-wr1-x42b.google.com with SMTP id ffacd0b85a97d-390ec449556so570944f8f.1 for ; Thu, 06 Mar 2025 03:17:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1741259844; x=1741864644; darn=lists.postgresql.org; h=subject:from:cc:content-language:to:user-agent:mime-version:date :message-id:from:to:cc:subject:date:message-id:reply-to; bh=9RMoVT/fsy9RkEFq1xrpHJ+jypFPA3wQKi2lP7LBjNI=; b=BZucVQUvRMkmMOEFZKofCiPztPBjWeLrdd3TwQ/dBnWHQ/CKw04P7dpbuJpSv6kbxe k6HTGV+WU2+HA0F2XE6KwvUeHCUV1pu1V62LDO1XYmPBcuW7Ic9iW2ltILjYImTS5lzH wbSw8h+/kBpQlozpWJsPTaOps17pM0rK+bRFWWxdouWujA8Yx9t3lLxlEM545Ygkf3F2 lczXoAPR2Io1EMy3sIsV1tkZSctjxlNVUlf4k3haLywhlkgGT/MHgUCq68dqEDzvsjyi gR4TiW5F9OsH8KzD2tqan7zGalIX7+X+Zln6YIg3FjlqPKuwT2b6CyMCxZmL/ZKYU6K6 eseQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741259844; x=1741864644; h=subject:from:cc:content-language:to:user-agent:mime-version:date :message-id:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=9RMoVT/fsy9RkEFq1xrpHJ+jypFPA3wQKi2lP7LBjNI=; b=uNcrp1qmHSmg3+nbyNFUcQ1eXNHYFOi0DPJfDOgb/+4HupdaP6+KYEDloL63ZZwSww bS6CQzy+wIN+EG33hrIAidYcQ63TGGcEZBeACrVv8nE20nE60rkqcj1IyVSEeb6sF2nS ZCFxj+sVY8HbbFITJYJgkJ4FD3ulBqXOZ5ivnkpSkPWQN9548XmK5/H/kcSopHxs768S YhbL2frq77Q1StVDZSUFnlcAh27wtTVJoBgX3TcKFAzzQbvzg80N00xi731utCsuANjG niK7Yjg3FaMR1Ue65FeEl8wijp88pRziHSHfqYGWilztGFc+YVr7C3r9E0IWw6Ne6qEI SnRQ== X-Gm-Message-State: AOJu0Yyc47KFnSuKfNtXM7/xpHoH0c8JF8y1NgsdqUnInkGG1rZPB/Mt qmbPv5bzkmakOPKuViJoKwjO3sW4ElrcCVM1uUvjKikFD4gH2oxSqezdGw== X-Gm-Gg: ASbGncsENWE+m3HKdnvt+rJUGUuGvLSk8nSRvq70q6TD5ZUmlAQxdwvSRlCr4Z51uBB nZAwLUDD9A7/mnm3aNwYyPECz/WiScl9966Xk9bgIh8DNHmFhxneAWbYtPu0mxcG0H4qGtoZxLB OaGXZN/uF0AL2vqfTGu5YxuRYW+U5IqOjZWdf0XhNEpAUlkC/3vIIAkZKyKIl0Bi00fsP1r4DeS 77MZLtHLBdgal0bhpaJJYb/u70nQ/xDFeT/2mME7phWfdsckuYEEVXnQ/fU2ggUIlsYa/4fwE5B pXcA0On4bli74cI+UdMuXbhihF8kWpPl6oZx70ALN/hdiF8q8CEWhknu3lld1CvAPkkOqDCTDOl bNTnfWBArHC7XMUk= X-Google-Smtp-Source: AGHT+IGSF1npMXoNSEyrVHeKm2awsP0mkvwTGASdufRK6kOw47mkH+yILKPXf90RkuwW6oGS4IXMJQ== X-Received: by 2002:a05:6000:4109:b0:391:2c67:7999 with SMTP id ffacd0b85a97d-3912c677bdfmr1973646f8f.48.1741259844097; Thu, 06 Mar 2025 03:17:24 -0800 (PST) Received: from [192.168.0.211] (84.123.232.230.dyn.user.ono.com. [84.123.232.230]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-3912c1031fdsm1678910f8f.89.2025.03.06.03.17.23 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 06 Mar 2025 03:17:23 -0800 (PST) Content-Type: multipart/mixed; boundary="------------pJIYZMEBeoFBj00hbGfEP06G" Message-ID: <531bcdbc-e734-4db8-b1d1-18af8bdf9eab@gmail.com> Date: Thu, 6 Mar 2025 12:17:22 +0100 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird To: pgsql-bugs@lists.postgresql.org Content-Language: en-US Cc: Richard Guo From: Andrei Lepikhov Subject: Memoize in between of two JOIN nodes List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------pJIYZMEBeoFBj00hbGfEP06G Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Playing with memoisation, I found the case where the Memoize is put over a JOIN node (see attachment). I recall a discussion we had with Richard in which he mentioned [1] that this feature is still not implemented and is hard to design. I'm not sure, but may it be a sign of a potential bug? [1] https://www.postgresql.org/message-id/CAMbWs4_CQR9ikyAhjH0CwzCdp%2BFcRAeoVsi5Oohd7s%3Dc5RZABg%40mail.gmail.com -- regards, Andrei Lepikhov --------------pJIYZMEBeoFBj00hbGfEP06G Content-Type: application/sql; name="reproduction.sql" Content-Disposition: attachment; filename="reproduction.sql" Content-Transfer-Encoding: base64 RFJPUCBUQUJMRSBJRiBFWElTVFMgdDEsdDIsdDMgQ0FTQ0FERTsKQ1JFQVRFIFRBQkxFIHQx ICh4IG51bWVyaWMsIHkgbnVtZXJpYywgeiBudW1lcmljKTsKSU5TRVJUIElOVE8gdDEgKHgs eSx6KSBTRUxFQ1QgZ3MgJSAxMDAsIGdzLCBncyAlIDEwMAogRlJPTSBnZW5lcmF0ZV9zZXJp ZXMgKDEsMUU1KSBBUyBnczsKQ1JFQVRFIElOREVYIE9OIHQxKHgpOwpDUkVBVEUgVEFCTEUg dDIgKHggbnVtZXJpYywgeSBudW1lcmljLCB6IG51bWVyaWMpOwpJTlNFUlQgSU5UTyB0MiAo eCx5LHopIFNFTEVDVCBncywgZ3MgJSAxMDAwLCBncyAlIDEwMAogIEZST00gZ2VuZXJhdGVf c2VyaWVzICgxLDEwMDAwKSBBUyBnczsKQ1JFQVRFIElOREVYIE9OIHQyKHkpOwpDUkVBVEUg VEFCTEUgdDMgKHggbnVtZXJpYywgeSBudW1lcmljKTsKSU5TRVJUIElOVE8gdDMgKHgseSkg U0VMRUNUIGdzJTIsIGdzJTMgRlJPTSBnZW5lcmF0ZV9zZXJpZXMgKDEsMTApIEFTIGdzOwpW QUNVVU0gQU5BTFlaRSB0MSx0Mix0MzsKU0VUIGVuYWJsZV9tZXJnZWpvaW4gPSAnb2ZmJzsK U0VUIGVuYWJsZV9oYXNoam9pbiA9ICdvZmYnOwpTRVQgZW5hYmxlX21hdGVyaWFsID0gJ29m Zic7ClNFVCBlbmFibGVfc2Vxc2NhbiA9ICdvZmYnOwpTRVQgZW5hYmxlX2JpdG1hcHNjYW4g PSAnb2ZmJzsKRVhQTEFJTiAoQU5BTFlaRSwgQ09TVFMgT0ZGLCBUSU1JTkcgT0ZGLCBCVUZG RVJTIE9GRiwgU1VNTUFSWSBPRkYpClNFTEVDVCAqIEZST00gdDMgTEVGVCBKT0lOIExBVEVS QUwgKAogU0VMRUNUIHQyLnggRlJPTSB0MiBXSEVSRSBOT1QgRVhJU1RTICgKICAgU0VMRUNU IDEgRlJPTSB0MSBXSEVSRSB0MS54ID0gdDMueCBBTkQgdDEuej10Mi56KSBBTkQKICAgdDIu eSA9IHQzLnkpIE9OIHRydWU7Ci8qCi0tIEkgc2VlOgogTmVzdGVkIExvb3AgTGVmdCBKb2lu ICAoY29zdD0wLjcxLi4yMjI3OC41NiByb3dzPTEwIHdpZHRoPTEzKSAoYWN0dWFsIHJvd3M9 NzMuMDAgbG9vcHM9MSkKICAgLT4gIFNlcSBTY2FuIG9uIHQzICAoY29zdD0wLjAwLi4xLjEw IHJvd3M9MTAgd2lkdGg9OCkgKGFjdHVhbCByb3dzPTEwLjAwIGxvb3BzPTEpCiAgICAgICAg IERpc2FibGVkOiB0cnVlCiAgIC0+ICBNZW1vaXplICAoY29zdD0wLjcxLi42MDIwLjg4IHJv d3M9MSB3aWR0aD01KSAoYWN0dWFsIHJvd3M9Ny4wMCBsb29wcz0xMCkKICAgICAgICAgQ2Fj aGUgS2V5OiB0My54LCB0My55CiAgICAgICAgIENhY2hlIE1vZGU6IGJpbmFyeQogICAgICAg ICBIaXRzOiA0ICBNaXNzZXM6IDYgIEV2aWN0aW9uczogMCAgT3ZlcmZsb3dzOiAwICBNZW1v cnkgVXNhZ2U6IDJrQgogICAgICAgICAtPiAgTmVzdGVkIExvb3AgQW50aSBKb2luICAoY29z dD0wLjcwLi42MDIwLjg3IHJvd3M9MSB3aWR0aD01KSAoYWN0dWFsIHJvd3M9Ni42NyBsb29w cz02KQogICAgICAgICAgICAgICBKb2luIEZpbHRlcjogKHQxLnogPSB0Mi56KQogICAgICAg ICAgICAgICBSb3dzIFJlbW92ZWQgYnkgSm9pbiBGaWx0ZXI6IDY2NjcKICAgICAgICAgICAg ICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgdDJfeV9pZHggb24gdDIgIChjb3N0PTAuMjkuLjQ0 LjEwIHJvd3M9MTAgd2lkdGg9OSkgKGFjdHVhbCByb3dzPTEwLjAwIGxvb3BzPTYpCiAgICAg ICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICh5ID0gdDMueSkKICAgICAgICAgICAgICAg LT4gIEluZGV4IFNjYW4gdXNpbmcgdDFfeF9pZHggb24gdDEgIChjb3N0PTAuNDIuLjIyNTcu MjYgcm93cz0xMDAwIHdpZHRoPTQpIChhY3R1YWwgcm93cz02NjcuMDAgbG9vcHM9NjApCiAg ICAgICAgICAgICAgICAgICAgIEluZGV4IENvbmQ6ICh4ID0gdDMueCkKKi8= --------------pJIYZMEBeoFBj00hbGfEP06G--