SqlServer: Split query involving Skip without OrderBy does not work

[smitpatel]

exec sp_executesql N'SELECT [t].[CustomerID], [t].[Address], [t].[City], [t].[CompanyName], [t].[ContactName], [t].[ContactTitle], [t].[Country], [t].[Fax], [t].[Phone], [t].[PostalCode], [t].[Region]
FROM (
    SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
    FROM [Customers] AS [c]
    ORDER BY (SELECT 1)
    OFFSET @__p_0 ROWS
) AS [t]
ORDER BY [t].[CustomerID]',N'@__p_0 int',@__p_0=10;

exec sp_executesql N'SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate], [t].[CustomerID]
FROM (
    SELECT [c].[CustomerID], [c].[Address], [c].[City], [c].[CompanyName], [c].[ContactName], [c].[ContactTitle], [c].[Country], [c].[Fax], [c].[Phone], [c].[PostalCode], [c].[Region]
    FROM [Customers] AS [c]
    ORDER BY (SELECT 1)
    OFFSET @__p_0 ROWS
) AS [t]
INNER JOIN [Orders] AS [o] ON [t].[CustomerID] = [o].[CustomerID]
ORDER BY [t].[CustomerID]',N'@__p_0 int',@__p_0=10

Generates following results

The first customer does not match. If instead of ORDER BY (SELECT 1) proper order is specified, it returns correct results.

This works fine in Sqlite.

[ajcvickers]

/cc @roji

[lauxjpn]

Doesn't work with Pomelo/MySQL as well.

[maumar]

@smitpatel add deterministic orderby when we can and/or disable this scenario for split otherwise?

[smitpatel]

Since this gives incorrect results, we should throw an error in this case rather than letting query run. It is not test issue of non-deterministic ordering.