Re: A new strategy for pull-up correlated ANY_SUBLINK - Mailing list pgsql-hackers

From Andy Fan
Subject Re: A new strategy for pull-up correlated ANY_SUBLINK
Date
Msg-id CAKU4AWphxS_QKEeUhSkt46_+7W8Y3ddr9q9roK4oWt0g2BZVmQ@mail.gmail.com
Whole thread Raw
In response to Re: A new strategy for pull-up correlated ANY_SUBLINK  (Alena Rybakina <lena.ribackina@yandex.ru>)
Responses Re: A new strategy for pull-up correlated ANY_SUBLINK
List pgsql-hackers

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

pgsql-hackers by date:

Previous
From: jian he
Date:
Subject: Re: [PATCH] Add support function for containment operators
Next
From: Michael Paquier
Date:
Subject: Re: New WAL record to detect the checkpoint redo location