[
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: 10.3.1.4
> 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.
