Bei der Migration von einer Oracle DB zu einer MySQL DB (4.0.18) stiess ich auf das Problem, dass
bei MySQL Aggregat-Funktionen in der ORDER BY-Klausel nicht unterstützt werden (Siehe Listing 1).

Folgende Fehlermeldung erhalte ich:
......
JDBCExceptionReporter.logExceptions(71) | SQL Error: 1111, SQLState: HY000
JDBCExceptionReporter.logExceptions(72) | Invalid use of group function
....

Die Möglichkeit, die Sortierung aus der DB-Ebene rauszunehmen und später in Java durchzuführen, möchte ich mir ersparen.

Hat jemand Erfahrung bei der Umschiffung dieses Problems ? Gibt es ein vernünftiges workaround oder sollte ich, da derartige Queries im aktuellen
Projekt häufig vorkommen, einfach auf den Einsatz von MySQL verzichten ?


LISTING 1:

List results = new ArrayList();

StringBuffer querySb = new StringBuffer();
querySb.append(" select new myProject.service.dto.SummaryRow");
querySb.append("(");
querySb.append(" a.name,");
querySb.append(" sum(a.this),");
querySb.append(" sum(a.other),");
querySb.append(" )");
querySb.append(" from ObjectA a ");
querySb.append(" join a.objectB b ");
querySb.append(" group by a.name ");
querySb.append(" order by sum(a.this) desc, sum(a.other) desc");

String queryString = querySb.toString();

try {
Query query = HibernateUtil.getSession().createQuery(queryString);
Iterator iter = query.list().iterator();
while (iter.hasNext()) {
SummaryRow dto = (SummaryRow) iter.next();
results.add(dto);
}
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}

return results;

Du mußt für die Summe eines Alias definieren. Wie das genau mit Hibernate geht, hab ich jetzt noch nicht getestet.

Während z.B. eine Query

mit fehlschlägt, liefert die Query
das gewünschte Ergebnis.

Wenn du es damit schaffst, würde mich die Hibernate-Query interessieren.

vielen Dank für Deinen Tipp mit den Spalten-Aliasen

Ich habe es ausprobiert, das läuft aber nicht (siehe Listing 2).

Hibernate schmeisst eine org.hibernate.exception.SQLGrammarException:

Der stack sagt mir, dass Hibernate die Spaltenaliase nicht erkennt.
............
JDBCExceptionReporter.logExceptions(71) | SQL Error: 1054, SQLState: 42S22
JDBCExceptionReporter.logExceptions(72) | Unknown column 'X' in 'order clause'
................

Laut Hibernate Reference Manual soll das Setzen von Spaltenaliasen möglich sein:

You may assign aliases to selected expressions using as:
select max(bodyWeight) as max, min(bodyWeight) as min, count(*) as n
from Cat cat

Mein Codebeispiel besagt aber das Gegenteil :-(

Hat da jemand einen Tipp ?

Sorry, hatte das Listing 2 vergessen mitzusenden:


LISTING 2:

List results = new ArrayList();

StringBuffer querySb = new StringBuffer();
querySb.append(" select new myProject.service.dto.SummaryRow");
querySb.append("(");
querySb.append(" a.name,");
querySb.append(" sum(a.this) as X,");
querySb.append(" sum(a.other) as Y ,");
querySb.append(" )");
querySb.append(" from ObjectA a ");
querySb.append(" join a.objectB b ");
querySb.append(" group by a.name ");
querySb.append(" order by X desc, Y desc");

String queryString = querySb.toString();

try {
Query query = HibernateUtil.getSession().createQuery(queryString);
Iterator iter = query.list().iterator();
while (iter.hasNext()) {
SummaryRow dto = (SummaryRow) iter.next();
results.add(dto);
}
} catch (HibernateException ex) {
throw new InfrastructureException(ex);
}

return results;

Hab mal im Forum eine Suche nach "order by aggregate function" durchgeführt.
Danach habe ich den Eindruck, daß es zwar prinzipiell geht, aber nicht mit MySQL.

Da müßte man dann auf Plain JDBC zurückgreifen.

vielen Dank für Deine Recherche und die Quintessenz.

Plain JDBC ist möglich aber keine Lösung, zumal ich aus Performancegründen und wegen der einfachen Handhabarkeit auf den Einsatz von SummaryRow-Objekten nicht verzichten möchte.

Die neueste Version von MySQL, 5.x, scheint sich dieses Problems noch immer nicht angenommen zu haben, zumindest habe ich nichts dergleichen finden können.

Einfach lächerlich.

Da ich nicht weiss, was MySQL sonst noch an unangenehmen Überraschungen bereithält, werde ich die Migration rückgängig machen und mir vorbehalten, zukünftig ganz auf den Einsatz von MySQL verzichten.

Unter Oracle habe ich übrigens das gleiche Problem mit den Spaltenaliasen.


Da mich die richtige Verwendung von Spaltenaliasen schon interessiert, ich aber keine Erklärung für das Scheitern in meinem akuellen Projekt habe, werde ich einen neuen Thread zum Thema "Spaltenaliase in Hibernate" aufmachen.

Ist es nicht generell ein Problem, den Alias in der Order-By Klausel zu verwenden?

Hab es jetzt nicht getestet, aber ich meine mich zu erinnern, dass man im Order-By-Part nie den vergebenen Alias verwenden konnte, sondern immer die Summenfunktion selbst noch einmal angeben mußte.

Wenn Hibernate jetzt aber unbedingt einen Alias haben möchte, dann wäre das durchaus ein Problem.


Vielleicht testest Du mal, ob Hibernate es annimmt, einen Alias für die Gruppen-Funktion zu verwenden, aber keinen im Order-By-Ausdruck?

vielleicht ist es ein problem das der alias ein reserviertes wort oder in diesem fall eine aggregate function ist.
Vielleicht geht es wenn man es nicht min/max nennt, sondern minValue/maxValue oder so.