2.4.0 DISTINCT FirstOrDefault without Order By defined adds wrong Order BY statement - SqlServerCompiler

[nielslucas]

Query:

var query = Db.Query("tdProducts")
                    .Select("tdProductTypes.IsStock", "tdProductVariants.MainVariantID")
                    .Distinct()
                    .Join("tdProductTypes", "tdProductTypes.ProductTypeID", "tdProducts.ProductType")
                    .Join("tdProductVariants", "tdProductVariants.ProductID", "tdProducts.ProductID")
                    .Join("tdDimensionCombinations", "tdDimensionCombinations.VariantID", "tdProductVariants.VariantID")
                    .Join("tdDimensions",
                        j => j.On("tdDimensions.DimensionID", "tdDimensionCombinations.DimensionID")
                            .WhereIfNotNull("tdDimensions.scnlDimensionUnit", 0))
                    .Join("tdDimensionValues", j => j.On("tdDimensionValues.DimensionID", "tdDimensions.DimensionID")
                        .WhereColumns("tdDimensionValues.ValueID", "=", "tdDimensionCombinations.DimensionValueID")
                        .WhereIfNotNull("tdDimensionValues.scnlBottleroom", 0))
                    .Where("tdProducts.ProductID", productId)
                    ;

var (isStockBottleRoomProduct, mainVariantID) = query.FirstOrDefault<(bool, int)>(Transaction);

Result:

exec sp_executesql N'SELECT DISTINCT [tdProductTypes].[IsStock], [tdProductVariants].[MainVariantID] FROM [tdProducts] 
INNER JOIN [tdProductTypes] ON [tdProductTypes].[ProductTypeID] = [tdProducts].[ProductType]
INNER JOIN [tdProductVariants] ON [tdProductVariants].[ProductID] = [tdProducts].[ProductID]
INNER JOIN [tdDimensionCombinations] ON [tdDimensionCombinations].[VariantID] = [tdProductVariants].[VariantID]
INNER JOIN [tdDimensions] ON ([tdDimensions].[DimensionID] = [tdDimensionCombinations].[DimensionID] AND NULLIF(tdDimensions.scnlDimensionUnit, 0) IS NOT NULL)
INNER JOIN [tdDimensionValues] ON ([tdDimensionValues].[DimensionID] = [tdDimensions].[DimensionID] AND [tdDimensionValues].[ValueID] = [tdDimensionCombinations].[DimensionValueID] AND NULLIF(tdDimensionValues.scnlBottleroom, 0) IS NOT NULL) WHERE [tdProducts].[ProductID] = @p0 
ORDER BY (SELECT 0) OFFSET @p1 ROWS FETCH NEXT @p2 ROWS ONLY',N'@p0 int,@p1 bigint,@p2 int',@p0=1974,@p1=0,@p2=1

The Order By is added with a 'SELECT 0' which is invalid. I added myself a .OrderBy and everything works fine. In 3.7 this worked fine.

[nielslucas]

Everything worked fine in 2.3.7*

[fairking]

SqlKata: v.2.4.0 SqlServerCompiler

Same with my issue. The following query gives me a wrong result:

var query = new SqlKata.Query("Customer")
    .Distinct()
    .Select("Name")
    .Where("Name", "John")
    .ForPage(2, 10);

The result is:

SELECT DISTINCT [Name] 
FROM [Customer]
ORDER BY (SELECT 0) OFFSET 10 ROWS FETCH NEXT 20 ROWS ONLY

The query is wrong and sql server throws the following error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Workaround. In order to avoid such errors I have switched UseLegacyPagination = true. You can also use GroupBy instead.