db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [jira] Commented: (DERBY-127) Aliased Columns not recognized after "group by... order by" combination
Date Fri, 25 Mar 2005 15:26:30 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
I am just describing the current behavior. Definitely not saying it
can't be changed. If the statement is SQL standard based, then anyone
can address the issue and submit a patch.<br>
<br>
Satheesh<br>
<br>
Fawzi Karachi wrote:<br>
<blockquote cite="mid63a00bcd050325044125312d0@mail.gmail.com"
 type="cite">
  <pre wrap="">That's strange. The statement is SQL-92 compliant and supported by all
the databases I have tried including MS SQL, Oracle, DB2 and Mckoi.
Is derby not SQL-92 compliant?
Are you planning not to fix it in the next release of derby?
Please let me know as I have a system currenlty running against McKoi
and was planning to migrate it to Derby. But if this bug won't be
fixed, we will probably stay with McKoi or try Oracle Lite.

Thank you. 

Fawzi.


On Thu, 24 Mar 2005 21:10:21 +0100 (CET), Satheesh Bandaram (JIRA)
<a class="moz-txt-link-rfc2396E" href="mailto:derby-dev@db.apache.org">&lt;derby-dev@db.apache.org&gt;</a>
wrote:
  </pre>
  <blockquote type="cite">
    <pre wrap="">    [ <a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/browse/DERBY-127?page=comments#action_61513">http://issues.apache.org/jira/browse/DERBY-127?page=comments#action_61513</a>
]

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)

    </pre>
    <blockquote type="cite">
      <pre wrap="">Aliased Columns not recognized after "group by... order by" combination
-----------------------------------------------------------------------

         Key: DERBY-127
         URL: <a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/browse/DERBY-127">http://issues.apache.org/jira/browse/DERBY-127</a>
     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
      </pre>
    </blockquote>
    <blockquote type="cite">
      <pre wrap="">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
      </pre>
    </blockquote>
    <pre wrap="">--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
  <a class="moz-txt-link-freetext" href="http://issues.apache.org/jira/secure/Administrators.jspa">http://issues.apache.org/jira/secure/Administrators.jspa</a>
-
If you want more information on JIRA, or have a bug to report see:
  <a class="moz-txt-link-freetext" href="http://www.atlassian.com/software/jira">http://www.atlassian.com/software/jira</a>


    </pre>
  </blockquote>
  <pre wrap=""><!---->


  </pre>
</blockquote>
</body>
</html>


Mime
View raw message