[hibernate-issues] [Hibernate-JIRA] Commented: (HB-1331) Aliases in the SELECT clause will be used in the ORDER BY clause for the MySQL dialect

Aliases in the SELECT clause will be used in the ORDER BY clause for the MySQL dialect -------------------------------------------------------------------------------------- Key: HB-1331 URL: http://opensource.atlassian.com/projects/hibernate/browse/HB-1331 Project: Hibernate2 Type: Patch

Components: core Versions: 2.1.7 Environment: Linux 2.6.8, MySQL 4.1.7, Hibernate 2.1.7c, Java 1.5 Reporter: Jarod Priority: Minor Attachments: patchfile-mySQL-aliases Original Estimate: 0 minutes Remaining: 0 minutes 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;