I am using the most recent nightly build: 5.0.2872
While Querying for an entity with 2 associated eager loaded entities, LightSpeed generates the following SQL call:
exec sp_executesql N'
...
SELECT
RWX_ListingTypes.Id,
RWX_ListingTypes.CreatedOn,
RWX_ListingTypes.DeletedOn,
RWX_ListingTypes.LastUpdatedUser,
RWX_ListingTypes.Name,
RWX_ListingTypes.UpdatedOn
FROM
RWX_ListingTypes
WHERE
(EXISTS (
SELECT
RWX_Listings.*
FROM
(
SELECT
RWX_Listings.*,
ROW_NUMBER() OVER(ORDER BY RWX_Listings.EndDTTM DESC) as RowNumber
FROM
RWX_Listings
WHERE
((EXISTS (
SELECT
RWX_Users.*
FROM
RWX_Users
WHERE
RWX_Users.Id = RWX_Listings.OwnerId AND
RWX_Users.DeletedOn IS NULL AND
RWX_Users.UserName = @p2
) AND ((RWX_Listings.Status = @p3 OR RWX_Listings.Status = @p4) OR RWX_Listings.Status = @p5)) AND RWX_Listings.DeletedOn IS NULL) AND
RWX_Listings.DeletedOn IS NULL
)
RWX_Listings
WHERE
RWX_Listings.ListingTypeId = RWX_ListingTypes.Id AND
RowNumber > @p0 AND
RowNumber <= @p1
) AND RWX_ListingTypes.DeletedOn IS NULL)',N'@p0 int,@p1 int,@p2 nvarchar(5),@p3 nvarchar(5),@p4 nvarchar(10),@p5 nvarchar(12)',@p0=0,@p1=20,@p2=N'admin',@p3=N'Ended',@p4=N'Successful',@p5=N'Unsuccessful'
If I replace the parameters the pure T-sql is as follows:
SELECT
RWX_ListingTypes.Id,
RWX_ListingTypes.CreatedOn,
RWX_ListingTypes.DeletedOn,
RWX_ListingTypes.LastUpdatedUser,
RWX_ListingTypes.Name,
RWX_ListingTypes.UpdatedOn
FROM
RWX_ListingTypes
WHERE
(EXISTS (
SELECT
RWX_Listings.*
FROM
(
SELECT
RWX_Listings.*,
ROW_NUMBER() OVER(ORDER BY RWX_Listings.EndDTTM desc) as RowNumber
FROM
RWX_Listings
WHERE
((EXISTS (
SELECT
RWX_Users.*
FROM
RWX_Users
WHERE
RWX_Users.Id = RWX_Listings.OwnerId AND
RWX_Users.DeletedOn IS NULL AND
RWX_Users.UserName = 'admin'
) AND ((RWX_Listings.Status = 'Ended' OR RWX_Listings.Status = 'Successful') OR RWX_Listings.Status = 'Unsuccessful')) AND RWX_Listings.DeletedOn IS NULL) AND
RWX_Listings.DeletedOn IS NULL
)
RWX_Listings
WHERE
RWX_Listings.ListingTypeId = RWX_ListingTypes.Id AND
RowNumber > 0 AND
RowNumber <= 20
) AND RWX_ListingTypes.DeletedOn IS NULL)
Since Row_Number() is nondeterministic (per MSDN),
There is no guarantee that the rows returned by a query using
ROWNUMBER() will be ordered exactly the same with each execution
unless the following conditions are true. Values of the partitioned
column are unique. Values of the ORDER BY columns are unique.
Combinations of values of the partition column and ORDER BY columns
are unique.
ROWNUMBER() is nondeterministic. For more information,
see Deterministic and Nondeterministic Functions.
and my order by clause does have duplicates, therefore the results I receive when I run the above are:
Id CreatedOn DeletedOn LastUpdatedUser Name UpdatedOn
----------- ----------------------- ----------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------------------
22201 2009-05-13 17:33:20.443 NULL StartupRegistrar Auction 2012-08-15 13:08:02.117
(1 row(s) affected)
which is incorrect...
If I modify the command slightly, to add ", RWX_Listings.Id desc" to the ORDER BY, my results are as follows:
Id CreatedOn DeletedOn LastUpdatedUser Name UpdatedOn
----------- ----------------------- ----------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- -----------------------
22201 2009-05-13 17:33:20.443 NULL StartupRegistrar Auction 2012-08-15 13:08:02.117
71401 2009-10-08 15:10:06.110 NULL StartupRegistrar FixedPrice 2012-08-15 13:08:02.383
325000 2011-03-01 16:24:15.800 NULL StartupRegistrar Classified 2012-08-15 13:08:02.503
(3 row(s) affected)
Which is correct...
Determinism should at least be consistent during a LightSpeed SQL call... therefore, IMHO, this is a bug, and perhaps the .Id order by clause should always be used. Since LightSpeed and SQL know this value to be unique, and thus, "Combinations of values of the partition column and ORDER BY columns are unique." When that's the case, Lightspeed can guarantee "that the rows returned by a query using ROW_NUMBER() will be ordered exactly the same with each execution," and will never fail to properly query related eager loaded entities.
