db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jack Klebanoff (JIRA)" <derby-...@db.apache.org>
Subject [jira] Assigned: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Date Sat, 30 Apr 2005 00:14:07 GMT
     [ 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


Mime
View raw message