Bug 124683 - More SQL Server compatibility and performance enhancement
Summary: More SQL Server compatibility and performance enhancement
Keywords:
Status: CLOSED WONTFIX
Alias: None
Product: Red Hat Web Application Framework
Classification: Retired
Component: persistence
Version: nightly
Hardware: All
OS: Linux
medium
medium
Target Milestone: ---
Assignee: Archit Shah
QA Contact:
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2004-05-28 14:29 UTC by Brett Prucha
Modified: 2007-04-18 17:08 UTC (History)
1 user (show)

Fixed In Version:
Clone Of:
Environment:
Last Closed: 2005-11-10 21:04:44 UTC
Embargoed:


Attachments (Terms of Use)

Description Brett Prucha 2004-05-28 14:29:33 UTC
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 18:16:56 UTC
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 19:28:31 UTC
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.