Aliases in the SELECT clause will be used in the ORDER BY clause for the MySQL dialect

Description

This patch is a temporary fix to address the MySQL bug that prevents sorting by aggregate functions, a pretty common task for a database.

It parses the generated SQL SELECT clause for aliases, and then replaces the corosponding columns in the ORDER BY clause with the alias. This is the current workaround for the MySQL bug.

For more background information, see: http://forum.hibernate.org/viewtopic.php?t=925363

For more details on the MySQL bug, see: http://bugs.mysql.com/bug.php?id=5478

To use the patch, copy the lines below, starting with the first diff all the way to the end, to a file called patchfile. Then run `patch -lp0 < patchfile` from the directory that contains the hibernate-2.1 directory.

diff -ru hibernate-2.1.7c/src/net/sf/hibernate/dialect/Dialect.java hibernate-2.1/src/net/sf/hibernate/dialect/Dialect.java
— hibernate-2.1.7c/src/net/sf/hibernate/dialect/Dialect.java 2004-11-23 11:47:39.000000000 -0600
+++ hibernate-2.1/src/net/sf/hibernate/dialect/Dialect.java 2004-11-28 23:46:46.000000000 -0600
@@ -180,7 +180,15 @@
public boolean qualifyIndexName() {
return true;
}
-
+
+ /**
+ * Does this dialect require an alias for functions in the order by clause?+ * @return boolean
+ */
+ public boolean requiresAliasForSorting() {
+ return false;
+ }
+
/**

  • Does this dialect support the <tt>FOR UPDATE</tt> syntax?

  • @return boolean
    diff -ru hibernate-2.1.7c/src/net/sf/hibernate/dialect/MySQLDialect.java hibernate-2.1/src/net/sf/hibernate/dialect/MySQLDialect.java

    •  

      • hibernate-2.1.7c/src/net/sf/hibernate/dialect/MySQLDialect.java 2004-11-23 11:47:38.000000000 -0600
        +++ hibernate-2.1/src/net/sf/hibernate/dialect/MySQLDialect.java 2004-11-28 23:46:46.000000000 -0600
        @@ -219,6 +219,10 @@
        return StringHelper.UNDERSCORE;
        }


+ public boolean requiresAliasForSorting() {
+ return true;
+ }
+
/**

  • Build an instance of the SQLExceptionConverter preferred by this dialect for

  • converting SQLExceptions into Hibernate's JDBCException hierarchy. The default
    diff -ru hibernate-2.1.7c/src/net/sf/hibernate/sql/QuerySelect.java hibernate-2.1/src/net/sf/hibernate/sql/QuerySelect.java

    •  

      • hibernate-2.1.7c/src/net/sf/hibernate/sql/QuerySelect.java 2004-11-23 11:47:38.000000000 -0600
        +++ hibernate-2.1/src/net/sf/hibernate/sql/QuerySelect.java 2004-11-28 23:47:58.000000000 -0600
        @@ -1,8 +1,10 @@
        //$Id: QuerySelect.java,v 1.12 2004/06/04 05:43:48 steveebersole Exp $
        package net.sf.hibernate.sql;


+import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
+import java.util.StringTokenizer;

import net.sf.hibernate.dialect.Dialect;
import net.sf.hibernate.util.StringHelper;
@@ -19,6 +21,7 @@
private StringBuffer orderBy = new StringBuffer();
private StringBuffer having = new StringBuffer();
private boolean distinct=false;
+ private Dialect dialect = null;

private static final HashSet DONT_SPACE_TOKENS = new HashSet();
static {
@@ -50,6 +53,7 @@
}

public QuerySelect(Dialect dialect) {
+ this.dialect = dialect;
joins = new QueryJoinFragment(dialect, false);
}

@@ -126,10 +130,59 @@
}
if ( groupBy.length() > 0 ) buf.append(" group by ").append( groupBy.toString() );
if ( having.length() > 0 ) buf.append(" having ").append( having.toString() );

  • if ( orderBy.length() > 0 ) buf.append(" order by ").append( orderBy.toString() );
    + if ( orderBy.length() > 0 ) {
    + if (dialect.requiresAliasForSorting()) {
    + //alias order by
    + buf.append(" order by ").append( aliasedOrder(select.toString(), orderBy.toString()) );
    + } else {
    + buf.append(" order by ").append( orderBy.toString() );
    + }
    + }
    return buf.toString();
    }


+ private String aliasedOrder(String select, String order) {
+ StringTokenizer st = new StringTokenizer(select, StringHelper.COMMA);
+ HashMap map = new HashMap();
+ String as = " as ";
+ while (st.hasMoreTokens()) {
+ String token = st.nextToken().trim();
+ int i = token.indexOf(as);
+ map.put(token.substring(0, i), token.substring(i + as.length(), token.length()));
+ }
+ st = new StringTokenizer(order, StringHelper.COMMA);
+ StringBuffer sb = new StringBuffer();
+ while (st.hasMoreTokens()) {
+ String token = st.nextToken().trim();
+ System.out.println(token);
+
+ //the order by token may contain desc or asc
+ int directionIndex = token.indexOf(' ');
+ if (directionIndex == -1) {
+ directionIndex = token.indexOf(StringHelper.CLOSE_PAREN);
+ }
+ String direction = null;
+ if (directionIndex != -1) {
+ direction = token.substring(directionIndex+1, token.length());
+ token = token.substring(0, directionIndex+1).trim();
+ }
+
+ String alias = (String)map.get(token);
+ if (alias==null) {
+ sb.append(token);
+ } else {
+ sb.append(alias);
+ }
+ if (direction != null) {
+ sb.append(' ').append(direction);
+ }
+ if (st.hasMoreTokens()) {
+ sb.append(StringHelper.COMMA_SPACE);
+ }
+ }
+ return sb.toString();
+ }
+
private static void appendTokens(StringBuffer buf, Iterator iter) {
boolean lastSpaceable=true;
boolean lastQuoted=false;

Environment

Linux 2.6.8, MySQL 4.1.7, Hibernate 2.1.7c, Java 1.5

Assignee

Unassigned

Reporter

Jarod

Labels

None

Feedback Requested

None

Feedback Requested By

None

backPortable

None

Suitable for new contributors

None

Pull Request

None

backportDecision

None

backportReEvaluate

None

Time tracking

0m

Components

Affects versions

Priority

Minor
Configure