db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dag H. Wanvik (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-4370) Implement JOIN ... USING syntax
Date Mon, 07 Sep 2009 21:36:57 GMT

    [ https://issues.apache.org/jira/browse/DERBY-4370?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12752265#action_12752265

Dag H. Wanvik commented on DERBY-4370:

I think Derby is correct in suppressing the join column in the case
you show above.  Consider this stanza from SQl 2003, vol 2, section
7.12 <query specification>, syntax rule 7 on <asterisked identifier
chain>, paragraph g), case i):

   "If the basis is a <table or query name> or <correlation name>, then
   let TQ be the table associated with the basis. The <select sublist> is
   equivalent to a <value expression> sequence in which each <value
   expression> is a column reference CR that references a column of TQ
   that is not a common column of a <joined table>. Each column of TQ
   that is not a referenced common column shall be referenced exactly
   once. The columns shall be referenced in the ascending sequence of
   their ordinal positions within TQ."

Note my underlining. A "common column" (below) is the one used in the USING
clause, so those columns should be omitted, as does Derby.

For the definition of "common column", see section 7.7 <joined table>,
syntax rule 7, paragraphs a) and b):

   "7) If NATURAL is specified or if a <join specification> immediately
    containing a <named columns join> is specified, then:

      a) If NATURAL is specified, then let common column name be a <field
      name> that is equivalent to the <field name> of exactly one field of
      RT1 and the <field name> of exactly one field of RT2. RT1 shall not
      have any duplicate common column names and RT2 shall not have any
      duplicate common column names. Let corresponding join columns refer to
      all fields of RT1 and RT2 that have common column names, if any.

      b) If a <named columns join> is specified, then every <column name>
      in the <join column list> shall be equivalent to the <field name>
      of exactly one field of RT1 and the <field name> of exactly one
      field of RT2. Let common column name be the name of such a
      column. Let corresponding join columns refer to the columns
      identified in the <join column list>."

So, this suppression also applies for NATURAL JOIN, if we want to
implement that.

> Implement JOIN ... USING syntax
> -------------------------------
>                 Key: DERBY-4370
>                 URL: https://issues.apache.org/jira/browse/DERBY-4370
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions:
>            Reporter: Knut Anders Hatlen
>            Priority: Minor
>         Attachments: using.diff
> The SQL standard defines two ways to specify the join condition in an INNER JOIN or a
(LEFT/RIGHT/FULL) OUTER JOIN: with an ON clause or with a USING clause. Derby currently only
accepts joins with an ON clause. Internally, Derby has code that supports USING. This code
should be enabled to ease the migration to Derby. We must also verify that the implementation
adheres to the standard before we enable it.
> Since USING is already a reserved keyword in Derby's parser, enabling the USING syntax
should not cause any compatibility issues for existing queries.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message