|
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. 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 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. 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 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.
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 regarding the reasoning behind this proposal. 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. Committed the simple test to the trunk as revision 656929.
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. |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 |
|
Improvement
Resolved
Minor
Column aliasing could simplify queries
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.