Re: (PATCH) Adding CORRESPONDING to Set Operations
От | Thom Brown |
---|---|
Тема | Re: (PATCH) Adding CORRESPONDING to Set Operations |
Дата | |
Msg-id | CAA-aLv6MU_xpRrD9LFiSzCUo9BHWTiFp9OAHQSxh9-1PMZ6FXQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: (PATCH) Adding CORRESPONDING to Set Operations (Kerem Kat <keremkat@gmail.com>) |
Ответы |
Re: (PATCH) Adding CORRESPONDING to Set Operations
(Kerem Kat <keremkat@gmail.com>)
|
Список | pgsql-hackers |
On 25 October 2011 18:49, Kerem Kat <keremkat@gmail.com> wrote: > On Mon, Oct 24, 2011 at 20:52, Erik Rijkers <er@xs4all.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.00rows=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.70rows=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
В списке pgsql-hackers по дате отправления: