Re: New CORRESPONDING clause design

On Tue, Jan 17, 2017 at 08:20:25AM -0600, Merlin Moncure wrote:
> On Tue, Jan 17, 2017 at 12:37 AM, Surafel Temsgen <surafel3000(at)gmail(dot)com> wrote:
> > I am new here and I really want to contribute, I have read same resource
> > that help understanding database system and postgresql. I would like to
> > start implementing sql syntax corresponding by clause because I believe
> > implementing sql syntax gives an opportunity to familiarize many part of
> > postgresql source code. Previous implementation is here and have an issue on
> > explain query and break cases on unlabeled NULLs
> > To repeat what a corresponding by clause means
> > Corresponding clause either contains a BY(...) clause or not. If it
> > doesn't have a BY(...) clause the usage is as follows.
>
> This is great stuff. Does the syntax only apply to UNION? I would
> imagine it would also apply to INTERSECT/EXCEPT? What about UNION
> ALL?

My draft working standard from 2011 says in 7IWD-02-Foundation section 7.13 <query expression>:

a) If CORRESPONDING is specified, then:
i) Within the columns of T1, equivalent <column name>s shall not be specified more than once
and within the columns of T2, equivalent <column name>s shall not be specified more than
once.
ii) At least one column of T1 shall have a <column name> that is the <column name> of some
column of T2.
iii) Case:
1) If <corresponding column list> is not specified, then let SL be a <select list> of those
<column name>s that are <column name>s of both T1 and T2 in the order that those
<column name>s appear in T1.
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.
iv) The <query term> or <query expression body> is equivalent to:
( SELECT SL FROM TN1 ) OP ( SELECT SL FROM TN2 )

Earlier, it defines ( UNION | EXCEPT ) [ ALL | DISTINCT ] to have a
meaning in this context, to wit:

<query expression body> ::=
<query term>
| <query expression body> UNION [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>
| <query expression body> EXCEPT [ ALL | DISTINCT ]
[ <corresponding spec> ] <query term>

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate