Re: (PATCH) Adding CORRESPONDING to Set Operations

On 25 October 2011 18:49, Kerem Kat <keremkat(at)gmail(dot)com> wrote:
> On Mon, Oct 24, 2011 at 20:52, Erik Rijkers <er(at)xs4all(dot)nl> wrote:
>> On Wed, October 19, 2011 15:01, Kerem Kat wrote:
>>> Adding CORRESPONDING to Set Operations
>>> Initial patch, filename: corresponding_clause_v2.patch
>>
>> I had a quick look at the behaviour of this patch.
>>
>> Btw, the examples in your email were typoed (one select is missing):
>>
>>> SELECT 1 a, 2 b, 3 c UNION CORRESPONDING 4 b, 5 d, 6 c, 7 f;
>> should be:
>>  SELECT 1 a, 2 b, 3 c UNION CORRESPONDING select 4 b, 5 d, 6 c, 7 f;
>>
>> and
>>
>>> SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) 4 b, 5 d, 6 c, 7 f;
>> should be:
>>  SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(b) select 4 b, 5 d, 6 c, 7 f;
>>>
>
> Yes you are correct, mea culpa.
>
>>
>>
>>
>> But there is also a small bug, I think: the order in the CORRESPONDING BY list should be followed,
>> according to the standard (foundation, p. 408):
>>
>> "2) If <corresponding column list> is specified, then let SL be a <select list> of those <column
>> name>s explicitly appearing in the <corresponding column list> in the order that these
>> <column name>s appear in the <corresponding column list>. Every <column name> in the
>> <corresponding column list> shall be a <column name> of both T1 and T2."
>>
>> That would make this wrong, I think:
>>
>> SELECT 1 a, 2 b, 3 c UNION CORRESPONDING BY(c,b) select 5 d, 6 c, 7 f, 4 b ;
>>
>>  b | c
>> ---+---
>>  2 | 3
>>  4 | 6
>> (2 rows)
>>
>> i.e., I think it should show columns in the order c, b (and not b, c); the order of the
>> CORRESPONDING BY phrase.
>>
>> (but maybe I'm misreading the text of the standard; I find it often difficult to follow)
>>
>
> It wasn't a misread, I checked the draft, in my version same
> explanation is at p.410.
> I have corrected the ordering of the targetlists of subqueries. And
> added 12 regression
> tests for column list ordering. Can you confirm that the order has
> changed for you?
>
>
>>
>> Thanks,
>>
>>
>> Erik Rijkers
>>
>>
>
> Regards,
>
> Kerem KAT

This explain plan doesn't look right to me:

test=# explain select a,b,c from one intersect corresponding by (a,c)
select a,b,c from two;
QUERY PLAN
---------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..117.00 rows=200 width=8)
-> Append (cost=0.00..97.60 rows=3880 width=8)
-> Subquery Scan on "*SELECT* 3" (cost=0.00..48.80 rows=1940 width=8)
-> Seq Scan on one (cost=0.00..29.40 rows=1940 width=8)
-> Subquery Scan on "*SELECT* 4" (cost=0.00..48.80 rows=1940 width=8)
-> Seq Scan on two (cost=0.00..29.40 rows=1940 width=8)
(6 rows)

If I do the same thing without the "corresponding...":

test=# explain select a,b,c from one intersect select a,b,c from two;
QUERY PLAN
----------------------------------------------------------------------------------
HashSetOp Intersect (cost=0.00..126.70 rows=200 width=12)
-> Append (cost=0.00..97.60 rows=3880 width=12)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..48.80
rows=1940 width=12)
-> Seq Scan on one (cost=0.00..29.40 rows=1940 width=12)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..48.80
rows=1940 width=12)
-> Seq Scan on two (cost=0.00..29.40 rows=1940 width=12)
(6 rows)

So it looks like it's now seeing the two tables as the 3rd and 4th
tables, even though there are only 2 tables in total.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company