[ http://issues.apache.org/jira/browse/DERBY-127?page=all ]
Jack Klebanoff reassigned DERBY-127:
------------------------------------
Assign To: Jack Klebanoff
> 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
> Assignee: Jack Klebanoff
>
> 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
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
