SD.LLBLGen.Pro.DQE.SqlServer.NET20.dll version: 2.6.8.903 SD.LLBLGen.Pro.ORMSupportClasses.NET20.dll version: 2.6.8.903 Adapter vs. SQL Server

This is somewhat related to this old thread that I started in 2005: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=4942

This following is not a bug, exactly. However, I think it can produce strange results when using paging.

To summarize the problem: when using paging, the query emitted by LLBL is very different when retrieving page 1 vs. retrievng any subsequent page. The effect of this is that the requested sort order when using paging can be interpreted differently (by SQL Server) between the page 1 query and the page x query.

A basic example: imagine a simple table of two columns, person_id and year_of_birth. There are twenty rows in the table, with the first five rows having different years of birth "1970", "1975", "1972", "1983", "1965". The rest of the rows all have the same value for year of birth: "2001".

If I used LLBL's paging feature to get the first ten rows sorted by year_of_birth, I will get a query like:

SELECT TOP 10 * FROM person_birth ORDER BY year_of_birth

If I use paging to get the second page of ten rows, sorted the same way, I will get a query that uses a CTE, and will be something like (consider this pseudocode!):

WITH    __actualSet
          AS (
              SELECT
                *,
                ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) AS __rowcnt
              FROM
                (
                 SELECT DISTINCT TOP 20 * 
                 FROM person_birth 
                 ORDER BY year_of_birth
                )
    FROM
        __actualSet
    WHERE
        [__rowcnt] > @__rownoStart
        AND [__rowcnt] <= @__rownoEnd
    ORDER BY
        [__rowcnt] ASC

The result of these queries being different is that in some cases, the same rows that have identical values for year_of_birth can show up in BOTH resultsets. So a person whose year_of_birth is "2001" can show up on page 1 AND page two.

In one of our test cases, when we plug the values 1 and 10 in for @__rownoStart and @__rownoEnd, we get consistant sort order between pages, so making the queries consistant fixes the "problem" (I realize that one case proves nothing, of course ).

Now, as noted in the thread referenced above, SQL does not guarantee any sort order that is not explicit. For this reason, this is probably not a "bug" per se--making the queries consistant for all pages won't guarantee anything. So we can (and probably should) add an additional sort column (like the PK) to both queries, which would result in ensuring that each pages' data remains "unique".

Still, emitting consistant queries for all pages would at least be less likely to cause this kind of side effect.

Thoughts?

Thanks,

Phil

Please check the following threads: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=8388 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=13353