SubSonic 2.2 SqlQuery object generates very different sql for WHERE...IN statement for SQL Server 2008 and SQL Server 2005

Question

Does anybody have an idea why SubSonic 2.2 SubSonic.SqlQuery object would be generating very different sql for the same C# code when running against SQL Server 2005 or SQL Server 2008?

I have a site that's been running for a while on SubSonic 2.2/SQL Server 2005. I just upgraded the DB to mssql 2008 and am encountering the following error:

SqlException (0x80131904): Incorrect syntax near the keyword 'AND'

I've dumped the SqlQuery.ToString() at the point of failure and noticed the following differences between running the exact same codebase on SQL Server 2005 and SQL Server 2008. Here is the source code:

SubSonic.SqlQuery q = new Select()
  .From(Views.VwSearchIndexQuery2Mtx)
  .Paged(pageNumber, maximumRows)
  .Where(VwSearchIndexQuery2Mtx.Columns.SearchIndexQueryId)
    .In(
        new Select(SearchIndexQueryGroupMap.Columns.SearchIndexQueryId)
          .From(Tables.SearchIndexQueryGroupMap)
          .Where(SearchIndexQueryGroupMap.Columns.SearchIndexQueryGroupId)
          .IsEqualTo(searchIndexQueryGroupId));

And the auto-generated sql for SQL Server 2005 is:

 SELECT * FROM     
(SELECT ROW_NUMBER() OVER ( ORDER BY CreatedOn DESC ) AS Row
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchTerms]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[IndustryId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[IndustryName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[DaysMonitored]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Incidents]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Relevance]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[CreatedOn]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[CreatedBy]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[ModifiedOn]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[ModifiedBy]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Deleted]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryTypeId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryTypeName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[LastUpdatedTime]     
FROM [dbo].[Vw_SearchIndexQuery2_Mtx]    
WHERE [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId] 
IN (SELECT [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryId]  
FROM [dbo].[SearchIndexQueryGroup_Map]  
WHERE [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryGroupId] =   @SearchIndexQueryGroupId0 )  )             
AS PagedResults WHERE  Row >= 1 AND Row <= 20

The auto-generated sql for SQL Server 2008:

DECLARE @Page int      
DECLARE @PageSize int       
SET @Page = 1      
SET @PageSize = 20       
SET NOCOUNT ON       
-- create a temp table to hold order ids      
DECLARE @TempTable TABLE (IndexId int identity, _keyID Int)       
-- insert the table ids and row numbers into the memory table      
INSERT INTO @TempTable      (        _keyID      )      
SELECT        [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId]           
 FROM [dbo].[Vw_SearchIndexQuery2_Mtx]  
 WHERE [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId] 
 IN (SELECT [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryId]  
  FROM [dbo].[SearchIndexQueryGroup_Map]  
  WHERE [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryGroupId] 
                         = @SearchIndexQueryGroupId0 
    ) 
   /* it's at this next AND where the error is thrown */
 AND [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId] 
 IN (SELECT [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryId]  
  FROM [dbo].[SearchIndexQueryGroup_Map]  
  AND [dbo].[SearchIndexQueryGroup_Map].[SearchIndexQueryGroupId] 
                         = @SearchIndexQueryGroupId0 
                  )  
ORDER BY CreatedOn DESC       
-- select only those rows belonging to the proper page          
SELECT [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchTerms]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[IndustryId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[IndustryName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[DaysMonitored]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Incidents]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Relevance]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[CreatedOn]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[CreatedBy]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[ModifiedOn]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[ModifiedBy]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[Deleted]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryTypeId]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryTypeName]
, [dbo].[Vw_SearchIndexQuery2_Mtx].[LastUpdatedTime]   
FROM [dbo].[Vw_SearchIndexQuery2_Mtx]       
INNER JOIN @TempTable t ON [dbo].[Vw_SearchIndexQuery2_Mtx].[SearchIndexQueryId] =    t._keyID      
WHERE t.IndexId BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize)

I know why the error is happening - the sql is invalid by the AND that I've commented above. I just can't figure out why SubSonic is generating invalid SQL after it worked on SQL Server 2008. You'll see that for SQL Server 2008 it uses a temp table and it also looks like it's repeating the WHERE...IN sub-query. I thought maybe it was the ISO Compatibility Level since the upgraded DB was set for 100. So I've tested with setting it to both 90 & 80 and SubSonic generates the same sql as in each case. (And BTW, the code generated for SQL Server 2005 which uses "select rownumber() over... as row" executes fine against SQL Server 2008.)

Does anyone have any ideas why this is happening and how to track it down?

Many thanks,

Terry

Answer 1

This may have been fixed already in the source? I suggest you try the most recent source from github to see if the problem has been addressed.

Basically, it's as you said -- the 2005 generator overrides the BuildPagedSelectStatement() method provided by ANSISqlGenerator. The 2008 generator inherits from 2005, so it should use the same paging method as 2005.

This can be seen by comparing these two files:

I believe you would also see the behavior you're describing on a SQL 2000 database since it doesn't override the ANSISqlGenerator.BuildPagedSelectStatement() method.

Also, I don't think compatability level is used to determine the SQL generator:

    public static bool IsSql2008(DataProvider provider)
    {
        return provider.DatabaseVersion.IndexOf("2008 - 10.") > -1 ||
        provider.DatabaseVersion.IndexOf("2008 (RTM) - 10.") > -1;
    }

And, assuming the ANSI generator is what's actually being used, the problem with the AND may be caused by this line in BuildPagedSqlStatement():

    //have to doctor the wheres, since we're using a WHERE in the paging
    //bits. So change all "WHERE" to "AND"
    string tweakedWheres = wheres.Replace("WHERE", "AND");

It needs to be a little smarter than that, although the point you raise about the duplicated clause also needs to be addressed.