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 1qrDi4-003CMi-6Z for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Oct 2023 08:39:52 +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 1qrDi1-00Fp2m-6A for pgsql-hackers@arkaria.postgresql.org; Fri, 13 Oct 2023 08:39:49 +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 1qrDi0-00Fp2a-Ou for pgsql-hackers@lists.postgresql.org; Fri, 13 Oct 2023 08:39:49 +0000 Received: from forward500c.mail.yandex.net ([178.154.239.208]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qrDhw-000Jk1-Pl for pgsql-hackers@postgresql.org; Fri, 13 Oct 2023 08:39:48 +0000 Received: from mail-nwsmtp-smtp-production-main-84.iva.yp-c.yandex.net (mail-nwsmtp-smtp-production-main-84.iva.yp-c.yandex.net [IPv6:2a02:6b8:c0c:1e2b:0:640:6530:0]) by forward500c.mail.yandex.net (Yandex) with ESMTP id 5A17360C1A; Fri, 13 Oct 2023 11:39:43 +0300 (MSK) Received: by mail-nwsmtp-smtp-production-main-84.iva.yp-c.yandex.net (smtp/Yandex) with ESMTPSA id fdbQ5W0UseA0-C1y99sIe; Fri, 13 Oct 2023 11:39:42 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex.ru; s=mail; t=1697186382; bh=wlJhE0+THSCkMB6/+PvX6fnKjt19SIkqklyOVBnuaGo=; h=In-Reply-To:Cc:Date:References:To:Subject:Message-ID:From; b=Ny8A75tYQXZy9b5PS3WFKDGuYdicfYgvhUQuHu2Y3aS4GIW11eaDbtjVK4DR9eU9t HmLMUb1uUhPnlRqIq8dhiZj/rs1jRj092by1VycvmGtGEyMLRbzVgDiXmK3R7pClCR cHlCq9U8r+Gb3ZYuRoQvzj0llw2uVIrogRvUSo7g= Authentication-Results: mail-nwsmtp-smtp-production-main-84.iva.yp-c.yandex.net; dkim=pass header.i=@yandex.ru Content-Type: multipart/mixed; boundary="------------YxGjakEm5pyx7zWEfxDvOt3y" Message-ID: <00de47dd-7a38-4225-a47e-65a8ba6de8d3@yandex.ru> Date: Fri, 13 Oct 2023 11:39:41 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK Content-Language: en-US To: Andy Fan Cc: pgsql-hackers , Tom Lane , vignesh C , Richard Guo , Andrey Lepikhov References: <247851.1668293143@sss.pgh.pa.us> <602d6b44-c734-4f7f-8999-99b6cac9bf17@yandex.ru> From: Alena Rybakina In-Reply-To: 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. --------------YxGjakEm5pyx7zWEfxDvOt3y Content-Type: multipart/alternative; boundary="------------0XhsdmM09X7YKhm5etJnzCtK" --------------0XhsdmM09X7YKhm5etJnzCtK Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 13.10.2023 10:04, Andy Fan wrote: > > It seems to me that the expressions "=" and "IN" are equivalent > here due to the fact that the aggregated subquery returns only one > value, and the result with the "IN" operation can be considered as > the intersection of elements on the left and right. In this query, > we have some kind of set on the left, among which there will be > found or not only one element on the right. > > > Yes, they are equivalent at the final result, but there are some > differences at the execution level.  the '=' case will be transformed > to a Subplan whose subPlanType is EXPR_SUBLINK, so if there > is more than 1 rows is returned in the subplan, error will be raised. > > select * from tenk1 where >   ten =  (select ten from tenk1 i where i.two = tenk1.two ); > > ERROR:  more than one row returned by a subquery used as an expression > > However the IN case would not. > select * from tenk1 where >   ten =  (select ten from tenk1 i where i.two = tenk1.two ) is OK. > > I think the test case you added is not related to this feature. the > difference is there even without the patch.  so I kept the code > you changed, but not for the test  case. Yes, I understand and agree with you that we should delete the last queries, except to one. The query below have a different result compared to master, and it is correct. Without your patch: explain (costs off) +SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);                                  QUERY PLAN -----------------------------------------------------------------------------  Nested Loop Left Join    ->  Seq Scan on tenk1 a    ->  Materialize          ->  Seq Scan on tenk2 b                Filter: (SubPlan 2)                SubPlan 2                  ->  Result                        InitPlan 1 (returns $1)                          ->  Limit                                ->  Index Scan using tenk2_hundred on tenk2 c                                      Index Cond: (hundred IS NOT NULL)                                      Filter: (odd = b.odd) (12 rows) After your patch: postgres=# explain (costs off) SELECT * FROM tenk1 A LEFT JOIN tenk2 B ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);                            QUERY PLAN --------------------------------------------------------------   Nested Loop Left Join     ->  Seq Scan on tenk1 a     ->  Materialize           ->  Nested Loop                 ->  Seq Scan on tenk2 b *->  Subquery Scan on "ANY_subquery"                       Filter: (b.hundred = "ANY_subquery".min)*                       ->  Aggregate                             ->  Seq Scan on tenk2 c                                   Filter: (odd = b.odd) (10 rows) > >> I took the liberty of adding this to your patch and added >> myself as reviewer, if you don't mind. >> >> Sure, the patch after your modification looks better than the >> original. >> I'm not sure how the test case around "because of got one row" is >> relevant to the current changes.  After we reach to some agreement >> on the above discussion, I think v4 is good for committer to review! > > Thank you!) I am ready to discuss it. > > Actually I meant to discuss the "Unfortunately, I found a request..", > looks > we have reached an agreement there:) > Yes, we have) -- Regards, Alena Rybakina --------------0XhsdmM09X7YKhm5etJnzCtK Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 13.10.2023 10:04, Andy Fan wrote:

It seems to me that the expressions "=" and "IN" are equivalent here due to the fact that the aggregated subquery returns only one value, and the result with the "IN" operation can be considered as the intersection of elements on the left and right. In this query, we have some kind of set on the left, among which there will be found or not only one element on the right.


Yes, they are equivalent at the final result, but there are some 
differences at the execution level.  the '=' case will be transformed
to a Subplan whose subPlanType is EXPR_SUBLINK, so if there
is more than 1 rows is returned in the subplan, error will be raised.

select * from tenk1 where
  ten =  (select ten from tenk1 i where i.two = tenk1.two );

ERROR:  more than one row returned by a subquery used as an expression

However the IN case would not. 
select * from tenk1 where
  ten =  (select ten from tenk1 i where i.two = tenk1.two ) is OK.

I think the test case you added is not related to this feature. the 
difference is there even without the patch.  so I kept the code
you changed, but not for the test  case.
Yes, I understand and agree with you that we should delete the last queries, except to one.

The query below have a different result compared to master, and it is correct.


Without your patch:

explain (costs off)
+SELECT * FROM tenk1 A LEFT JOIN tenk2 B
ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Nested Loop Left Join
   ->  Seq Scan on tenk1 a
   ->  Materialize
         ->  Seq Scan on tenk2 b
               Filter: (SubPlan 2)
               SubPlan 2
                 ->  Result
                       InitPlan 1 (returns $1)
                         ->  Limit
                               ->  Index Scan using tenk2_hundred on tenk2 c
                                     Index Cond: (hundred IS NOT NULL)
                                     Filter: (odd = b.odd)
(12 rows)


After your patch:

postgres=# explain (costs off)
SELECT * FROM tenk1 A LEFT JOIN tenk2 B
ON B.hundred in (SELECT min(c.hundred) FROM tenk2 C WHERE c.odd = b.odd);

                           QUERY PLAN
--------------------------------------------------------------
  Nested Loop Left Join
    ->  Seq Scan on tenk1 a
    ->  Materialize
          ->  Nested Loop
                ->  Seq Scan on tenk2 b
                ->  Subquery Scan on "ANY_subquery"
                      Filter: (b.hundred = "ANY_subquery".min)
                      ->  Aggregate
                            ->  Seq Scan on tenk2 c
                                  Filter: (odd = b.odd)
(10 rows)


I took the liberty of adding this to your patch and added myself as reviewer, if you don't mind.

Sure, the patch after your modification looks better than the original. 
I'm not sure how the test case around "because of got one row" is
relevant to the current changes.  After we reach to some agreement
on the above discussion, I think v4 is good for committer to review!

Thank you!) I am ready to discuss it.
 
Actually I meant to discuss the "Unfortunately, I found a request..", looks
we have reached an agreement there:) 

Yes, we have)

-- 
Regards,
Alena Rybakina
--------------0XhsdmM09X7YKhm5etJnzCtK-- --------------YxGjakEm5pyx7zWEfxDvOt3y Content-Type: text/x-patch; charset=UTF-8; name="pull-up.diff" Content-Disposition: attachment; filename="pull-up.diff" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvc3Vic2VsZWN0Lm91dCBi L3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvc3Vic2VsZWN0Lm91dAppbmRleCAxN2RmNmI1 ZGM5Yy4uZTQxYjcyOGRmODMgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0 ZWQvc3Vic2VsZWN0Lm91dAorKysgYi9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL3N1YnNl bGVjdC5vdXQKQEAgLTIwMjgsMyArMjAyOCwyNyBAQCBPTiBBLmh1bmRyZWQgaW4gKFNFTEVD VCBjLmh1bmRyZWQgRlJPTSB0ZW5rMiBDIFdIRVJFIGMub2RkID0gYi5vZGQpOwogICAgICAg ICAgLT4gIFNlcSBTY2FuIG9uIHRlbmsyIGIKICgxMSByb3dzKQogCistLSB3ZSBjYW4gcHVs bCB1cCB0aGUgYWdncmVnYXRlIHN1YmxpbmsgaW50byBSSFMgb2YgYSBsZWZ0IGpvaW4uCitl eHBsYWluIChjb3N0cyBvZmYpCitTRUxFQ1QgKiBGUk9NIHRlbmsxIEEgTEVGVCBKT0lOIHRl bmsyIEIKK09OIEIuaHVuZHJlZCBpbiAoU0VMRUNUIG1pbihjLmh1bmRyZWQpIEZST00gdGVu azIgQyBXSEVSRSBjLm9kZCA9IGIub2RkKTsKKyAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgUVVFUlkgUExBTiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgIAorLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tCisgTmVzdGVkIExv b3AgTGVmdCBKb2luCisgICAtPiAgU2VxIFNjYW4gb24gdGVuazEgYQorICAgLT4gIE1hdGVy aWFsaXplCisgICAgICAgICAtPiAgTmVzdGVkIExvb3AKKyAgICAgICAgICAgICAgIC0+ICBT ZXEgU2NhbiBvbiB0ZW5rMiBiCisgICAgICAgICAgICAgICAtPiAgTWVtb2l6ZQorICAgICAg ICAgICAgICAgICAgICAgQ2FjaGUgS2V5OiBiLmh1bmRyZWQsIGIub2RkCisgICAgICAgICAg ICAgICAgICAgICBDYWNoZSBNb2RlOiBiaW5hcnkKKyAgICAgICAgICAgICAgICAgICAgIC0+ ICBTdWJxdWVyeSBTY2FuIG9uICJBTllfc3VicXVlcnkiCisgICAgICAgICAgICAgICAgICAg ICAgICAgICBGaWx0ZXI6IChiLmh1bmRyZWQgPSAiQU5ZX3N1YnF1ZXJ5Ii5taW4pCisgICAg ICAgICAgICAgICAgICAgICAgICAgICAtPiAgUmVzdWx0CisgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICBJbml0UGxhbiAxIChyZXR1cm5zICQxKQorICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAtPiAgTGltaXQKKyAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgdGVuazJfaHVuZHJlZCBv biB0ZW5rMiBjCisgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgIEluZGV4IENvbmQ6IChodW5kcmVkIElTIE5PVCBOVUxMKQorICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6IChvZGQgPSBiLm9kZCkK KygxNiByb3dzKQorCmRpZmYgLS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL3NxbC9zdWJzZWxl Y3Quc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvc3Vic2VsZWN0LnNxbAppbmRleCA0OWNl NWZjOTlhOC4uMmYzNjAxYTA1ODIgMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jlc3Mvc3Fs L3N1YnNlbGVjdC5zcWwKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwvc3Vic2VsZWN0LnNx bApAQCAtMTAwMCwzICsxMDAwLDggQEAgT04gQi5odW5kcmVkIGluIChTRUxFQ1QgYy5odW5k cmVkIEZST00gdGVuazIgQyBXSEVSRSBjLm9kZCA9IGIub2RkKTsKIGV4cGxhaW4gKGNvc3Rz IG9mZikKIFNFTEVDVCAqIEZST00gdGVuazEgQSBJTk5FUiBKT0lOIHRlbmsyIEIKIE9OIEEu aHVuZHJlZCBpbiAoU0VMRUNUIGMuaHVuZHJlZCBGUk9NIHRlbmsyIEMgV0hFUkUgYy5vZGQg PSBiLm9kZCk7CisKKy0tIHdlIGNhbiBwdWxsIHVwIHRoZSBhZ2dyZWdhdGUgc3VibGluayBp bnRvIFJIUyBvZiBhIGxlZnQgam9pbi4KK2V4cGxhaW4gKGNvc3RzIG9mZikKK1NFTEVDVCAq IEZST00gdGVuazEgQSBMRUZUIEpPSU4gdGVuazIgQgorT04gQi5odW5kcmVkIGluIChTRUxF Q1QgbWluKGMuaHVuZHJlZCkgRlJPTSB0ZW5rMiBDIFdIRVJFIGMub2RkID0gYi5vZGQpOwpc IE5vIG5ld2xpbmUgYXQgZW5kIG9mIGZpbGUK --------------YxGjakEm5pyx7zWEfxDvOt3y--