]
Diego Pires Plentz commented on HB-1331:
----------------------------------------
Ingres has the same problem, so, I think that "requiresAliasForSorting()" could
become a part of Dialects. What about that?
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;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: