You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Thomas Browne (JIRA)" <de...@db.apache.org> on 2005/01/20 20:38:17 UTC
[jira] Created: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
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
[jira] Commented: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Posted by "baleineca (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-127?page=comments#action_60735 ]
baleineca commented on DERBY-127:
---------------------------------
Also observed when using Hibernate. Same combination of aliased name, group by, order by in the generated SQL producing the same error.
> 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
[jira] Commented: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Posted by "Julian Hyde (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-127?page=comments#action_62534 ]
Julian Hyde commented on DERBY-127:
-----------------------------------
Not exactly a dup of 84.
This bug (127) calls for expressions (albeit very simple expressions!) in the ORDER BY clause, which is an optional feature in the SQL standard. This feature is exposed via the JDBC metadata call java.sql.DatabaseMetadata.supportsExpressionsInOrderBy(), and the Derby driver answers truthfully: false. Of course it would be nice if Derby supported this feature, but it's not a bug. Incidentally, we will be able to work around it very easily in Mondrian.
Bug 84 calls for a different feature, which is to make column aliases available in the WHERE, GROUP BY and HAVING clauses. This would not adhere to the standard, and is occasionally ambiguous. For example:
SELECT x AS y, y AS z FROM t WHERE y = 5 GROUP BY x, y
> 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
[jira] Resolved: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Posted by "Jack Klebanoff (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-127?page=all ]
Jack Klebanoff resolved DERBY-127:
----------------------------------
Resolution: Fixed
Fix Version: 10.1.0.0
SVN revision 169744
> 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
> Fix For: 10.1.0.0
>
> 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
[jira] Commented: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Posted by "Micah Spears (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Posted by "Fawzi Karachi (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-127?page=comments#action_61510 ]
Fawzi Karachi commented on DERBY-127:
-------------------------------------
This also happens in standard derby install.
> 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
[jira] Assigned: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Posted by "Jack Klebanoff (JIRA)" <de...@db.apache.org>.
[ 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
[jira] Commented: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Posted by "Satheesh Bandaram (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-127?page=comments#action_61513 ]
Satheesh Bandaram commented on DERBY-127:
-----------------------------------------
Derby, in general, doesn't allow order by sort keys to be exposed names, if they also have a correlation name. (alias name) When a correlation name is specified, ORDER BY, specially requires using the correlation name or a simple-integer number. (like order by 1)
Going by this, the third query that works also should not. (Query without group by)
> 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
[jira] Closed: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Posted by "Jack Klebanoff (JIRA)" <de...@db.apache.org>.
[ http://issues.apache.org/jira/browse/DERBY-127?page=all ]
Jack Klebanoff closed DERBY-127:
--------------------------------
> 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
> Fix For: 10.1.0.0
>
> 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