Summary of Change

An ORDER BY clause which specifies a table-name-qualified column alias is now rejected as invalid, where previously it was accepted.

Symptoms Seen by Applications Affected by Change

Applications which specify a column alias for a column in the SELECT statement, and which specify an ORDER BY clause which specifies that column alias qualified by the table name, will now receive an error indicating that the ORDER BY clause is invalid.

As an example, take the following:

create table t1 (i int, j int);
select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by t1.idcolumn1, t1.idcolumn2;

This query is now rejected, as there is no column named 'idcolumn1' in table 't1'. The error message is:

ERROR 42X04: Column 'T1.IDCOLUMN1' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'T1.IDCOLUMN1' is not a column in the target table.

Valid forms of the query above are:

select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by idcolumn1, idcolumn2;

or

select t1.id as idcolumn1, t1.id as idcolumn2 from t1 order by t1.id, t1.id;

Rationale for Change

The rules for resolving column references in ORDER BY clauses have been enhanced to consider column aliases and column names more fully. Derby now uses different resolution rules depending on whether the ORDER BY column reference is table.column, or just column:

• if the table name is provided, we match against the underlying table name, and don't consider any aliases
• if the table name is NOT provided, we first match against the alias name, if present, and if no alias name matches then we match against the underlying source column name.

Application Changes Required

A query which specifies table-name.alias-name should be rewritten to specify either simply alias-name, or table-name.column-name.