Hello,
We upgraded an old project using LLBL Framework 3.5 to LLBL Framework 5.12.
We use a SQL Server 2019 Database
During our tests with found a difference on select queries when a paging is used with no sort expression
With LLBL 5.12, a clause "ORDER BY (SELECT NULL)" is added to the querry.
But the query contains also the 'DISTINCT' key word (maybe because of the paging), and this is not allowed by SQL Server.
We got this error : ORDER BY items must appear in the select list if SELECT DISTINCT is specified
Exemple of request generated by LLBL 3.5
SELECT distinct [Table1].[Field1], [Table2].[Field2]
FROM [dbo].[Table1] WITH(NOLOCK)
LEFT JOIN [dbo].[Table2] WITH(NOLOCK) ON [Table1].[Table1Id]=[Table2].[Table1Id]
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
The same request generated by LLBL 5.12
SELECT distinct [Table1].[Field1], [Table2].[Field2]
FROM [dbo].[Table1] WITH(NOLOCK)
LEFT JOIN [dbo].[Table2] WITH(NOLOCK) ON [Table1].[Table1Id]=[Table2].[Table1Id]
ORDER BY (SELECT NULL) OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY
Is there a way to prevent LLBL 5.12 to add an 'ORDER BY' clause when no sort expression has been provided ?
Thank you
