Column aliasing could simplify queries

• Key: DERBY-84
• Type: Improvement
• Status: Resolved
• Resolution: Won't Fix
• Priority: Minor
• Assignee: Bryan Pendleton
• Reporter: Bob Gibson
• Component/s: SQL
• Affects Version/s: 10.0.2.0

Description

Currently, one can not use an alias to simplify queries. For example, being able to alias "LongDescriptiveColumnName" AS LDCN would allow one to use the alias elsewhere in the query, e.g., the WHERE clause:

SELECT LongDescriptiveColumnName AS LDCN FROM MyTable WHERE LDCN LIKE '%testing%';

The current result is a message like:

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

Comments

[Micah Spears]

This issue is hindering Hibernate support. HQL queries using 'group by' and 'having' clauses fail in cloudscape because hibernate uses column aliases. An example is below.

HQL: select p.rxId from gov.va.med.predemo.local.pharmacy.db.pharmacy.hibernate.PatientPrescriptionDO as p, p.prescriptionStatusLogs as s group by p having max(s.statusId) = :status

SQL: select patientpre0_.RX_ID as x0_0_ from Pharmacy.PATIENT_PRESCRIPTION patientpre0_ inner join Pharmacy.PRESCRIPTION_STATUS_LOG prescripti1_ on patientpre0_.RX_ID=prescripti1_.RX_ID group by patientpre0_.RX_ID having (max(prescripti1_.STATUS_ID)=? )

Message: com.ibm.db2.jcc.c.SqlException: Column 'PATIENTPRE0_.X0_0_' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'PATIENTPRE0_.X0_0_' is not a column in the target table.


If I remove 'patientpre0_.RX_ID as x0_0_', the query runs as expected.

[Jack Klebanoff]

The problem described by Bob Gibson in the main body of this report and the problem described by Micah Spears in his comment of 12/Jan/05 seem different to me.

This bug report complains that Derby does _not_ support the use of column aliases in WHERE clauses.

The problem described in the 12/Jan/05 comment is that Derby _requires_ the use of column aliases in an ORDER BY clause for columns that have aliases. The comment complains that Derby should, but currently does not, allow the original column names in an ORDER BY clause if the column has an alias. I think that the 12/Jan/05 comment is a duplicate of Derby-127, but this bug report (Derby-84) is different.

[Shreyas Kaushik]

Without the where clause this works, looks like a binding issue

ij> connect 'jdbc:derby:test';
ij> drop table test;
0 rows inserted/updated/deleted
ij> create table test(i int, longcolname varchar(10));
0 rows inserted/updated/deleted
ij> insert into test values(1,'abcd');
1 row inserted/updated/deleted
ij> select longcolname as lc from test where lc like '%ab%';
ERROR 42X04: Column 'LC' is not in any table in the FROM list or it appears within a join specification and is outside the scope of the join specification or it appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then 'LC' is not a column in the target table.
ij> select longcolname as lc from test;
LC
----------
abcd

1 row selected

[Jack Klebanoff]

I suspect that this is not a bug, that Derby is behaving correctly here according to the SQL standard.

The alias in the select list names the column in the virtual table produced by the SELECT. However Derby requires that where clause column references refer to columns in tables, views, or sub-queries listed in the FROM clause. In the bug report example LDCN is not a column in in any from list table, so Derby issues an error message when LDCN is used in the where clause.

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.

[Shreyas Kaushik]

So, can this be closed ? Should the hibernate group be informed about the way they generate queries to take care of this ?
BTW have a look at this link, http://www.hibernate.org/80.392.html

[Alex Miller]

I agree that this matches the SQL spec. The ORDER BY column is conceptually executed after the SELECT clause and works on the output names defined in the SELECT clause. All other clauses are logically evaluated *before* the SELECT clause and thus cannot use the output column names defined by the SELECT clause. Of course, many dbs are more tolerant about what can occur, particularly about what can occur in the ORDER BY.

[Bryan Pendleton]

I intend to add a few more tests to the test suite, to demonstrate the
current handling of column aliases in WHERE clauses, and then to
resolve this issue as "won't fix".

See the discussion in DERBY-2457 for some additional comments
regarding the reasoning behind this proposal.

[Bryan Pendleton]

simpleTest.diff contains a simple addition to select.sql to
demonstrate the current (believed to be standards-compliant)
behavior of Derby with respect to column aliases in the WHERE clause.

I propose to commit this change and resolve this issue as won't-fix,
with the reasoning that we intend to preserve the standards-compliant
behavior in this area for now.

[Bryan Pendleton]

Committed the simple test to the trunk as revision 656929.

[Bryan Pendleton]

For the time being, we intend to preserve Derby's current behavior,
as we believe it to be standards-compliant, and adhering to the
SQL standard seems important for this case.