[hibernate-issues] [Hibernate-JIRA] Commented: (HB-1331) Aliases in the SELECT clause will be used in the ORDER BY clause for the MySQL dialect
Diego Pires Plentz (JIRA)
noreply at atlassian.com
Mon Sep 4 18:21:24 EDT 2006
[ http://opensource.atlassian.com/projects/hibernate/browse/HB-1331?page=comments#action_24300 ]
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:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list