public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andy Fan <[email protected]>
To: Alena Rybakina <[email protected]>
Cc: pgsql-hackers <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: vignesh C <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: Andrey Lepikhov <[email protected]>
Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK
Date: Fri, 13 Oct 2023 15:04:45 +0800
Message-ID: <CAKU4AWphxS_QKEeUhSkt46_+7W8Y3ddr9q9roK4oWt0g2BZVmQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAKU4AWoZksNZ4VR-fLTdwmiR91WU8qViDBNQKNwY=7iyo+uV0w@mail.gmail.com>
	<[email protected]>
	<CAKU4AWpp8aANvwU1ivjPtF3AakFjTf1jBm_OFmPSdCqizjBQLw@mail.gmail.com>
	<[email protected]>
	<CAKU4AWrwggExWQ6mg7QGT59TVL6BGwBKbVn3ZQmmOAwOF3-w=g@mail.gmail.com>
	<[email protected]>

>
> 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.

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:)

-- 
Best Regards
Andy Fan

view thread (8+ messages)  latest in thread

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 using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: A new strategy for pull-up correlated ANY_SUBLINK
  In-Reply-To: <CAKU4AWphxS_QKEeUhSkt46_+7W8Y3ddr9q9roK4oWt0g2BZVmQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox