Bug 124683 - More SQL Server compatibility and performance enhancement
More SQL Server compatibility and performance enhancement
Status: CLOSED WONTFIX
Product: Red Hat Web Application Framework
Classification: Retired
Component: persistence (Show other bugs)
nightly
All Linux
medium Severity medium
: ---
: ---
Assigned To: Archit Shah
: FutureFeature
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2004-05-28 10:29 EDT by Brett Prucha
Modified: 2007-04-18 13:08 EDT (History)
1 user (show)

See Also:
Fixed In Version:
Doc Type: Enhancement
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2005-11-10 16:04:44 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description Brett Prucha 2004-05-28 10:29:33 EDT
Description of problem:

SQL Server does not allow you to use order by in views, inline 
functions, derived tables, and subqueries unless TOP is also 
specified.

When getting the size of a data query then the query is wrapped and 
select count(*) is applied to the wrapped query.  When an order by 
has been applied to the data query and size is called then SQL Server 
reports this error.

I came accross this problem due to incompatibility with SQL Server 
but it may also be a performance enhancement as well.  Performance 
issues is probably why SQL Server does not allow this.  It does not 
make sense to order the query when all your doing is getting the 
count of the query.  I modified 
com.arsdigita.persistence.DataQueryImpl to account for this.  In 
makeExpr(boolean size) I do a if(!size) test before running the 
following:

    for (int i = m_orders.size() - 1; i >= 0; i--) {
        String order = (String) m_orders.get(i);
        orders[i] = mapAndAddPaths(order);
    }

and 

    for (int i = orders.length - 1; i >= 0; i--) {
        expr = new Sort(expr, new Static(orders[i], m_bindings));
    }
Comment 1 Archit Shah 2004-05-28 14:16:56 EDT
Only the second for loop (the one with new Sort) needs to be skipped
during a size(). mapAndAddPaths should be called on every relevant
path, including those in the order because it can change the size of
the query if one of the paths traverses a property that is a collection.
Comment 2 Brett Prucha 2004-06-16 15:28:31 EDT
The DataQueryImpl.isEmpty() function also needs to be modified here 
by calling DataQueryImpl.makeExpr(true) because DataSet.isEmpty() 
simply calls DataSet.size().

Note You need to log in before you can comment on or make changes to this bug.