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-2457) Use of column aliases in group by / having clauses can cause queries to fail
Date Fri, 09 May 2008 14:24:55 GMT

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

Dag H. Wanvik commented on DERBY-2457:

I *think* this issue is invalid, but I am not really familiar with the
standard semantics in these areas, which is pretty hard to unravel :)
However I also found this:

* Legal column reference occuring inside a GROUP BY is specified in
  section 7.9, SR 1: 

  "Each <grouping column reference> shall unambiguously reference a
   column of the table resulting from the <from clause>."

The <select list> is on the same syntactic level as the <from clause>
and is thus not involved in defining the table resulting from the
<from clause>, see. <query specification> in section 7.12.

* Legal column references inside <search condition> in HAVING <search
  condition> is defined in section 7.10, SR 1-3:

   "1) Let HC be the <having clause>. Let TE be the <table expression>
   that immediately contains HC. If TE does not immediately contain a
   <group by clause>, then "GROUP BY ()" is implicit. Let T be the
   descriptor of the table defined by the <group by clause> GBC
   immediately contained in TE and let R be the result of GBC.  

   2) Let G be the set consisting of every column referenced by a <column
   reference> contained in GBC.  

   3) Each column reference directly contained in the <search condition>
   shall be one of the following: a) An unambiguous reference to a column
   that is functionally dependent on G.  b) An outer reference.  NOTE 148
   - See also the Syntax Rules of Subclause 6.7, <column reference>.

For the HAVING case, I *think* that the definition of "outer
reference" excludes the AS <column> alias: It seems to boil down to
whether the <select-list> constitutes a scope that contains the table
expression or not, cf. definition of "outer reference". As I read it
now, that is not the case, but I could not establish this decisively.

If that is the correct reading, it seems that any AS alias in the
<select-list> are not legal in these context, only in the ORDER BY
context (that clause is defined syntactically not as part of the <from
clause>, but at the level of <cursor specification>, section 14.1).

By the same reasoning, I also think the change called for in DERBY-84
is not legal SQL. Of course, there may other other considerations that standards
compliance ;)

> Use of column aliases in group by / having clauses can cause queries to fail
> ----------------------------------------------------------------------------
>                 Key: DERBY-2457
>                 URL: https://issues.apache.org/jira/browse/DERBY-2457
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions:
>            Reporter: Andrew McIntyre
>            Assignee: Bryan Pendleton
>            Priority: Minor
>         Attachments: 1624_repro.sql
> Some use of column aliases in group by / having clauses can cause queries to fail with
error 42X04. The queries can sometimes be made to work by also aliasing the table or rewriting
the query to use a subselect. Attached is a simple sql script which reproduces the issue,
originally found as part of DERBY-1624.

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

View raw message