|
Don't have the time to look into this right now.
This seems fairly similar to
for how to resolve column references in ORDER BY clauses. The resolution rules use a search path and thus can handle both the use of aliases as well as the use of underlying column names, and distinguish ambiguous cases depending on whether the column reference was qualified or unqualified. It seems like something similar could work effectively for column references in GROUP BY and HAVING clauses. I'll have a look at the work we did in and see if the same, or similar, techniques could be used here. It appears that, while ORDER BY items are bound to the select's
result column list, GROUP BY and HAVING items are bound to the select's "from list". So one approach might be to change GroupByColumn.bindExpression and similar places to be more like OrderByColumn.bindOrderByColumn; that is, to bind against the select RCL, rather than against the from list. > It appears that, while ORDER BY items are bound to the select's
> result column list, GROUP BY and HAVING items are bound to > the select's "from list". > > So one approach might be to change GroupByColumn.bindExpression > and similar places to be more like OrderByColumn.bindOrderByColumn; > that is, to bind against the select RCL, rather than against the from list. This seems to be intentional, cf. this comment: http://issues.apache.org/jira/browse/DERBY-84?focusedCommentId=66872#action_66872 (Klebanoff 02/Jun/05 05:23 PM): "My reading of the SQL2003 spec indicates that the Derby behavior follows the SQL standard. See the standard's discussion of identifier chains. The spec differentiates between identifiers used in ORDER BY clauses and identifiers used elsewhere. The spec says that (column) identifiers in a ORDER BY clause should be bound to the column names defined in the select list, but that other (column) identifiers should be bound to columns in tables in the FROM list." I did not check the standard yet. I see the portion of the standard that Jack refers to, and I agree that
it specifies that only identifiers used in ORDER BY clauses should be allowed to refer to derived columns. Section 6.6, syntax rule 8.a.i appears to be the text that that Jack observed. So perhaps we should resolve this issue as invalid? 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 is not legal SQL. Of course, there may other other considerations that standards compliance ;) Attached is 'additionalTests.diff', which is a straightforward
conversion of '1624_repro.sql' into a patch to GroupByTest.java. I think it's valuable having these tests, even if we decide that the current behavior is current and that we don't want to change it. In that case, the tests will serve to verify the desired behavior. Also, if someone should experiment with changing the behavior in the future, having these tests in the system will draw people's attention to this JIRA entry, and thus to the discussion and comments in the issue. So, I propose to commit the test-only patch, and resolve the issue as invalid. I committed the additional test cases to the trunk as revision 655947.
Since we believe that the current behavior of Derby is correct according to the SQL Standard, I'm resolving the issue as invalid. (Would "won't fix" be more appropriate?) Should we also then resolve
Extra tests are good, thanks Bryan!
WHERE clauses cannot reference the select list column aliases either, AFAICT, so it might be good to add tests for that, too, if it is not already done somewhere. Bryan> Would "won't fix" be more appropriate? I tend to agree; since this issue is not a misunderstanding, to some this would be an "improvement" (it is not marked as a bug); we are merely deciding that we don't want to do it, in this case because it is not standard compliant as far as we can tell. Andrew> Should we also then resolve +1 Re-opening to re-resolve as "won't fix", since that seems more appropriate.
I'll look into adding the WHERE clause tests mentioned by Dag as part of |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Improvement
Resolved
Minor
Use of column aliases in group by / having clauses can cause queries to fail
DERBY-1624which demonstrates the issue.