openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Abe White (JIRA)" <j...@apache.org>
Subject [jira] Resolved: (OPENJPA-554) The GetMapValue class should have/supply an alias for ORDER-BY clauses.
Date Mon, 07 Apr 2008 16:27:27 GMT

     [ https://issues.apache.org/jira/browse/OPENJPA-554?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Abe White resolved OPENJPA-554.
-------------------------------

    Resolution: Fixed

Applied suggested fix in revision 645589.

> The GetMapValue class should have/supply an alias for ORDER-BY clauses.
> -----------------------------------------------------------------------
>
>                 Key: OPENJPA-554
>                 URL: https://issues.apache.org/jira/browse/OPENJPA-554
>             Project: OpenJPA
>          Issue Type: Bug
>          Components: jdbc
>    Affects Versions: 1.0.2
>         Environment: MS SQLServer
>            Reporter: Joe Weinstein
>             Fix For: 1.0.3
>
>   Original Estimate: 48h
>  Remaining Estimate: 48h
>
> A generated select query asks for one of it's columns returned as
> a subselect, and then asks that the results be ordered by that subselect.
> The DBMS is throwing a spurious error message, saying that in order to
> do a SELECT DISTINCT/ORDER BY, the select list has to contain the
> column to be ordered by. It's spurious because the query clearly does
> list the identical subselect in the select list and the order-by, but the DBMS
> is apparently not smart enough to equate those.
> Here is a slightly simplified example:
>       s.executeQuery("SELECT DISTINCT "
>               + "    t0.id, "
>               + "    (SELECT PMH_testPCKeyStringValue.value "
>               + "     FROM PMH_testPCKeyStringValue "
>               + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
>               + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) "
>               + "FROM PMH t0 "
>               + "INNER JOIN PMH_testPCKeyStringValue t1 ON t0.id = t1.PERSISTENTMAPHOLDER_ID
"
>               + "WHERE ("
>               + "    (SELECT PMH_testPCKeyStringValue.value "
>               + "     FROM PMH_testPCKeyStringValue "
>               + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
>               + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) "
>               + "     IS NOT NULL) "
>               + "ORDER BY "
>               + "    (SELECT PMH_testPCKeyStringValue.value "
>               + "     FROM PMH_testPCKeyStringValue "
>               + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
>               + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) "
>               + "DESC");
> The actual SQL generated has parameter markers for the testPCKeyStringValue
> value, and is executed with a prepared statement.
>   A modified query that works, which initially simply enough, involves declaring
> a column name for the subselect, and then using that column name in the order-by:
>       s.executeQuery("SELECT DISTINCT "
>               + "    t0.id, "
>               + "    (SELECT PMH_testPCKeyStringValue.value "
>               + "     FROM PMH_testPCKeyStringValue "
>               + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
>               + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) AS MY_COL_ALIAS
"
>               + "FROM PMH t0 "
>               + "INNER JOIN PMH_testPCKeyStringValue t1 ON t0.id = t1.PERSISTENTMAPHOLDER_ID
"
>               + "WHERE ("
>               + "    (SELECT PMH_testPCKeyStringValue.value "
>               + "     FROM PMH_testPCKeyStringValue "
>               + "     WHERE PMH_testPCKeyStringValue.PERSISTENTMAPHOLDER_ID = t0.id "
>               + "     AND PMH_testPCKeyStringValue.testPCKeyStringValue = 1) "
>               + "     IS NOT NULL) "
>               + "ORDER BY MY_COL_ALIAS "
>               + "DESC");
> The fix, suggested by Abe White, and tested successfully by me (in this case/DBMS only)
is:
> " - When we find JDOQL of the form "<map>.get(<value>)", we add the result
> of ExpressionFactory.getMapValue(...) to the expression tree. 
> - In the case we're concerned with the ExpressionFactory in question is
> the org.apache.openjpa.jdbc.kernel.exps.JDBCExpressionFactory, and the
> return value is an org.apache.openjpa.jdbc.kernel.exps.GetMapValue.
> - The GetMapValue class manually constructs the SQL subselect to
> retrieve the value for the given key.
> Our goal is to alias the subselect in the SELECT portion of the query,
> to keep the subselect unaliased in the WHERE portion, and to use the
> SELECT alias in place of the subselect in the ORDER BY portion.
> Luckily, I believe this can be accomplished easily with a few
> modifcations to the GetMapValue class:
> - Add a "String _alias" member to GetMapValue.  This will be a unique
> alias within the select for the subselect we'll produce.  I recommend
> generating this value with a monotonically-increasing int in
> JDBCExpressionFactory and passing it to the GetMapValue constructor.
> I.e.:
> class JDBCExpressionFactory {
>   private int _getMapValueAlias = 0;
>   ...
>   Value getMapValue(...) {
>     return new GetMapValue(..., "gmv" + _getMapValueAlias++);
>   }
> }
> - In GetMapValue.select(...), append " AS " + the _alias member to the
> SQLBuffer returned by newSQLBuffer(...).
> - In GetMapValue.orderBy(...), just order by the _alias member, not the
> result of newSQLBuffer(...).    
> This should work because when we construct the select (see
> org.apache.openjpa.jdbc.exps.SelectConstructor) we automatically call
> select(...) for any ordering value, in addition to orderBy(...).  So the
> same GetMapValue instance will have a chance to create both its SELECT
> SQL and its ORDER BY SQL.  
> Notes:
> - You might only want to use subselect aliasing at all if the
> DBDictionary in use (accessible through ctx.store.getDBDictionary()) has
> its requiresAliasForSubselect field set to true.  Or maybe it would be
> best for all dictionaries.  I don't know -- it would require a test run
> on all our supported databases to see what each one likes.  My hunch
> would be to do it for all dictionaries."

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message