OrderBy and OrderByDescending, with FirstOrDefault on a column with duplicate values

[joshakaw]

I have an Access database with duplicate fields in the HappenedOn column that I wish to order by in my LINQ queries. When it is sorted descending (as shown in the screenshot below), there are two duplicate dates. When sorted ascending, there are also two duplicate dates.

When I run either OrderBy or OrderByDescending,

  var currentMeeting = _context.MeetingT
       .OrderBy(m => m.HappenedOn)
       .Select(m => m.MeetingId).FirstOrDefault();

  var currentMeeting = _context.MeetingT
       .OrderByDescending(m => m.HappenedOn)
       .Select(m => m.MeetingId).FirstOrDefault();

I get an exception that is unexpected for the method FirstOrDefault, "Sequence contains more than one element". This does not happen when I order by a field that has no duplicates, like the MeetingId column. I may be mistaken, but I believe that an arbitrary record should be returned if there are two records that have the same value in the sort field.

[joshakaw]

I've created a test for this and went through the debugger, the query is being made correctly as:

SELECT TOP(1) b.Id
FROM dbo.Blog AS b
ORDER BY b.NotFigTime DESC

So I tested the query directly similarly on Microsoft Access with my table

SELECT
    TOP 1 MeetingT_local.Notes
FROM
    MeetingT_local
ORDER BY
    MeetingT_local.HappenedOn DESC;

and I got two results:

It turns out that Microsoft Access does not always return one result when you use TOP(1), which messes up the expected SingleOrDefault ResultCardinality of the ShapedQueryExpression.

[joshakaw]

The workaround is wrapping it in one more select:

SELECT
    TOP 1 *
FROM
    (
        SELECT
            TOP 1 MeetingT_local.Notes
        FROM
            MeetingT_local
        ORDER BY
            MeetingT_local.HappenedOn DESC
    )

(probably don't even need the TOP 1 in the subquery)

[ChrisJollyAU]

@joshakaw Thanks. I did have a very good suspicion it was that but hadn't gotten round to actually confirming.

This is unfortunately an error/problem that occurs on the Microsoft Access database driver side and not directly in this ef core provider.

The problem is actually a bit more than what you have here.

In fact MS Access does not use a strict TOP and when you have duplicates appearing around the point where it is meant to cut off, it just sees the duplicates and returns up to the end of the duplicates (so theoretically it is possible to have a TOP 6 and if the column you are ordering by of rows 6 - 20 is the same it will actually return 20 rows)

Technically this is actually valid but does go against the standard practice. From what I've read, the SQL standard actually does NOT specify how a database must handle duplicate columns with the ORDER BY (and hence also with TOP)

The unwritten rule that is standard practice (used by SQL Server, direct LINQ queries and other mainstream databases) is fairly predictable

1. When you have rows that your ORDER BY considers duplicates, keep those rows in the same order as they initially appear in the database (so the order if you do an unordered select)
2. Use a hard limit and return exactly the quantity rows you asked for

MS Access differs

1. The rows that the ORDER BY considers duplicates do not necessarily stay in their original order. From observations it does try at least 1 pass of a sort algorithm. which means the order of those rows is not the same as what other databases do
2. It uses a softer limit and returns all rows that the ORDER BY considers duplicates irrespective of whether it puts you over the TOP limit or not.

I do have tests in the testsuite that encounter the above situations

Just thinking on your workaround, I think it is only a partial workaround. Sure you get passed the error message but the TOP 1 that you actually end up could be different to how it would be in SQL Server etc, based on how MS Access sorted those duplicate rows initially.

The thing is, when we are writing the ef core provider, the test suite runs it directly with linq and also with the database and it passes if both produce the same result. This allows people to write the same linq queries irrespective of the underlying provider and know that if they change the provider, the results will still be the same. With your work around that still wouldnt be able to pass.

Off the top of my head my recommendation would be:

1. add a secondary column to order by (e.g. MeetingID). That should make the rows more unique when ordering (unless you have two meetings with the same ID on the same day)
2. Or you can put a .ToList() before the FirstOrDefault so that that executes client side

[bubibubi]

@ChrisJollyAU I think this is a regression bug. The issue is from JetDataReader up to callers.
The original JetDataReader code used to take in input the top clause parameter (not only the skip clause parameter) and then it overrides DbDataReader.Read

The top handling code has been deleted in this commit
db47437

[ChrisJollyAU]

@bubibubi Not really. If I remember correctly, it could only handle that if the TOP was in primary/parent (whatever you want to call it) query. When you get a Sequence contains more than one element error this is from a scalar subquery which is not able to be handled like that.

Also, as I had from my one point, MS Access can order those rows a bit differently so even working around the softer limit that gave extra rows, there is no guarantee that those rows will be in the same order when comparing with the linq query for that test and the test will still fail

[bubibubi]

When I first met the issue I thought that working on the datareader Is the only option. I still can't imagine another way. It solves all simple (normal) cases (as skip that is not implemented in MS Access).
Also skip was removed then it has been restored.

[joshakaw]

In fact MS Access does not use a strict TOP and when you have duplicates appearing around the point where it is meant to cut off, it just sees the duplicates and returns up to the end of the duplicates (so theoretically it is possible to have a TOP 6 and if the column you are ordering by of rows 6 - 20 is the same it will actually return 20 rows)

Ah, ok - that makes a lot more sense of how it comes up with two records my case. I appreciate this explanation! I also notice that SQL Server has what seems to be a similar option TOP WITH TIES.

The thing is, when we are writing the ef core provider, the test suite runs it directly with linq and also with the database and it passes if both produce the same result. This allows people to write the same linq queries irrespective of the underlying provider and know that if they change the provider, the results will still be the same. With your work around that still wouldnt be able to pass.

Understandable that you don't want to break your tests by putting on a non-standard workaround. It's just a bit unfortunate the way that Linq queries with one DB provider can be incompatible with this one given the weak TOP evaluation that the test suite locks in. This is beyond this issue, so I don't expect a response, but If I were to make a modification to implement my TOP-wrapping workaround, where would I start?

[ChrisJollyAU]

Ah, ok - that makes a lot more sense of how it comes up with two records my case. I appreciate this explanation! I also notice that SQL Server has what seems to be a similar option TOP WITH TIES.

Yep. Its exactly that behaviour. Unfortunately its the only behaviour by the MS Access engine and as you've seen, its the same within ms access as well.

but If I were to make a modification to implement my TOP-wrapping workaround, where would I start?

That gets interesting. I probably wouldnt at least without lots of testing.

With the way your example goes I can already at a glance see a potential issue. What happens if the column you have (or the row in more general terms) itself has duplicates e.g. in your example MeetingT_local.Notes that you have from the subquery itself has the same value? Then no matter how many times you wrap it, you still have duplicate values it has to look at.

The other thing if you already know that MeetingT_local.Notes likely wont be the same then the simplest is to rewrite the query so that it produces something like

SELECT
    TOP 1 MeetingT_local.Notes
FROM
    MeetingT_local
ORDER BY
    MeetingT_local.HappenedOn DESC, MeetingT_local.Notes;

Then both HappenedOn and Notes have to be the same on all rows in question for those rows to be deemed a tie/duplicate

If you are going to play around and investigate, I think the best place would be as one of the postprocessor's. See JetQueryTranslationPostprocessor.cs. Call it from the Process function. I already have some custom postprocessors that you can take a look at e.g. the JetLiftOrderByPostprocessor.

Also let me expand on one of my points earlier. Lets take the test Entity_equality_orderby_subquery in orthwindMiscellaneousQueryJetTest.

Jet produces this SQL

SELECT `c0`.`CustomerID`, `c0`.`Address`, `c0`.`City`, `c0`.`CompanyName`, `c0`.`ContactName`, `c0`.`ContactTitle`, `c0`.`Country`, `c0`.`Fax`, `c0`.`Phone`, `c0`.`PostalCode`, `c0`.`Region`, `c0`.`c`
FROM (
    SELECT `c`.`CustomerID`, `c`.`Address`, `c`.`City`, `c`.`CompanyName`, `c`.`ContactName`, `c`.`ContactTitle`, `c`.`Country`, `c`.`Fax`, `c`.`Phone`, `c`.`PostalCode`, `c`.`Region`, (
        SELECT TOP 1 `o`.`OrderID`
        FROM `Orders` AS `o`
        WHERE `c`.`CustomerID` = `o`.`CustomerID`) AS `c`
    FROM `Customers` AS `c`
) AS `c0`
ORDER BY `c0`.`c`

and this result

SQL Server produces

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 TOP(1) [o].[OrderID]
    FROM [Orders] AS [o]
    WHERE [c].[CustomerID] = [o].[CustomerID])

And

Note the difference in ordering on the first 2 lines. While you still get the correct total lines, the ordering of those 2 lines are different due to how ms access sorts rows that are deemed equivalent/duplicate from the ORDER BY columns

Even if we fix the TOP, we can still get different results because of this other part