[ http://issues.apache.org/jira/browse/DERBY-127?page=comments#action_61660 ] Micah Spears commented on DERBY-127: ------------------------------------
This is a duplicate issue. Please see http://issues.apache.org/jira/browse/DERBY-84 related to hibernate column aliasing. > Aliased Columns not recognized after "group by... order by" combination > ----------------------------------------------------------------------- > > Key: DERBY-127 > URL: http://issues.apache.org/jira/browse/DERBY-127 > Project: Derby > Type: Bug > Components: SQL > Versions: 10.0.2.1 > Environment: Windows XP Professional > JDK 1.4 > (first found in relation to Mondrian 1.0.1) > Reporter: Thomas Browne > > I've been doing work to try and integrate Derby with the Mondrian ROLAP > engine, which has uncovered a bug in Derby when a query involves column > aliasing, a group by clause, and an order by clause. > For example: Mondrian will generate the following query: > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by STORE.STORE_COUNTRY, STORE.STORE_STATE, > STORE.STORE_CITY, STORE.STORE_NAME > which should be valid SQL. I have tested this query outside of the Mondrian > environment and still receive the same error which is: > "Column 'STORE.STORE_COUNTRY' 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. > SQL State: 42x04 > Error Code: 30000 > However, if I remove any one of the three elements (aliasing, group by, order > by) or if the order by uses the aliased names, the query works. It is only > the combination of all 3 elements that is causing a problem. > [ie. all of the following queries work correctly] > select STORE.STORE_COUNTRY , STORE.STORE_STATE , STORE.STORE_CITY , > STORE.STORE_NAME , STORE.STORE_TYPE , STORE.STORE_MANAGER , STORE.STORE_SQFT > , STORE.GROCERY_SQFT , STORE.FROZEN_SQFT , STORE.MEAT_SQFT , STORE.COFFEE_BAR > , STORE.STORE_STREET_ADDRESS from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by STORE.STORE_COUNTRY, STORE.STORE_STATE, > STORE.STORE_CITY, STORE.STORE_NAME > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE order by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME > select STORE.STORE_COUNTRY as c0, STORE.STORE_STATE as c1, STORE.STORE_CITY > as c2, STORE.STORE_NAME as c3, STORE.STORE_TYPE as c4, STORE.STORE_MANAGER as > c5, STORE.STORE_SQFT as c6, STORE.GROCERY_SQFT as c7, STORE.FROZEN_SQFT as > c8, STORE.MEAT_SQFT as c9, STORE.COFFEE_BAR as c10, > STORE.STORE_STREET_ADDRESS as c11 from STORE as STORE group by > STORE.STORE_COUNTRY, STORE.STORE_STATE, STORE.STORE_CITY, STORE.STORE_NAME, > STORE.STORE_TYPE, STORE.STORE_MANAGER, STORE.STORE_SQFT, STORE.GROCERY_SQFT, > STORE.FROZEN_SQFT, STORE.MEAT_SQFT, STORE.COFFEE_BAR, > STORE.STORE_STREET_ADDRESS order by c0,c1,c2,c3 -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - If you want more information on JIRA, or have a bug to report see: http://www.atlassian.com/software/jira
