Ordering issue in ManyToMany?

[roji]

When syncing EFCore.PG, I'm getting this failure from Join_with_skip_navigation:

Assert.Equal() Failure
Expected: 8
Actual:   19
   at Microsoft.EntityFrameworkCore.Query.ManyToManyQueryFixtureBase.<>c.<GetEntityAsserters>b__6_3(Object e, Object a) in /home/roji/projects/efcore/test/EFCore.Specification.Tests/Query/ManyToManyQueryFixtureBase.cs:line 61
   at Microsoft.EntityFrameworkCore.TestUtilities.QueryAsserter.AssertEqual[T](T expected, T actual, Action`2 asserter) in /home/roji/projects/efcore/test/EFCore.Specification.Tests/TestUtilities/QueryAsserter.cs:line 1418
   at Microsoft.EntityFrameworkCore.Query.QueryTestBase`1.AssertEqual[T](T expected, T actual, Action`2 asserter) in /home/roji/projects/efcore/test/EFCore.Specification.Tests/Query/QueryTestBase.cs:line 1082
   at Microsoft.EntityFrameworkCore.Query.ManyToManyQueryTestBase`1.<Join_with_skip_navigation>b__23_2(<>f__AnonymousType277`2 e, <>f__AnonymousType277`2 a) in /home/roji/projects/efcore/test/EFCore.Specification.Tests/Query/ManyToManyQueryTestBase.cs:line 242
   at Microsoft.EntityFrameworkCore.TestUtilities.TestHelpers.AssertResults[T](IList`1 expected, IList`1 actual, Func`2 elementSorter, Action`2 elementAsserter, Boolean verifyOrdered) in /home/roji/projects/efcore/test/EFCore.Specification.Tests/TestUtilities/TestHelpers.cs:line 235
   at Microsoft.EntityFrameworkCore.TestUtilities.QueryAsserter.AssertQuery[TResult](Func`2 actualQuery, Func`2 expectedQuery, Func`2 elementSorter, Action`2 elementAsserter, Boolean assertOrder, Int32 entryCount, Boolean async, String testMethodName) in /home/roji/projects/efcore/test/EFCore.Specification.Tests/TestUtilities/QueryAsserter.cs:line 119
--- End of stack trace from previous location ---

The PG SQL is identical to the SQL Server one (modulu quoting etc.), but the results are different (see below). Without me diving into this, @smitpatel do you have any idea? The LIMIT 1 without an ORDER BY seems suspicious...

PG query:

SELECT e."Id", e."CollectionInverseId", e."Name", e."ReferenceInverseId", e0."Id", e0."CollectionInverseId", e0."Name", e0."ReferenceInverseId"
FROM "EntityTwos" AS e
INNER JOIN "EntityTwos" AS e0 ON e."Id" = (
    SELECT e1."Id"
    FROM "JoinTwoSelfShared" AS j
    INNER JOIN "EntityTwos" AS e1 ON j."RightId" = e1."Id"
    WHERE e0."Id" = j."LeftId"
    LIMIT 1);

SQL Server:

Id	CollectionInverseId	Name	ReferenceInverseId	Id	CollectionInverseId	Name	ReferenceInverseId
9	7	EntityTwo 9	NULL	1	1	EntityTwo 1	NULL
2	1	EntityTwo 2	NULL	3	NULL	EntityTwo 3	NULL
11	9	EntityTwo 11	18	4	3	EntityTwo 4	NULL
8	7	EntityTwo 8	NULL	5	3	EntityTwo 5	NULL
18	16	EntityTwo 18	5	6	5	EntityTwo 6	NULL
2	1	EntityTwo 2	NULL	8	7	EntityTwo 8	NULL
4	3	EntityTwo 4	NULL	9	7	EntityTwo 9	NULL
5	3	EntityTwo 5	NULL	10	9	EntityTwo 10	20
13	11	EntityTwo 13	14	12	11	EntityTwo 12	16
14	13	EntityTwo 14	12	13	11	EntityTwo 13	14
6	5	EntityTwo 6	NULL	16	15	EntityTwo 16	9
9	7	EntityTwo 9	NULL	17	15	EntityTwo 17	7
2	1	EntityTwo 2	NULL	18	16	EntityTwo 18	5
2	1	EntityTwo 2	NULL	19	16	EntityTwo 19	3
4	3	EntityTwo 4	NULL	20	17	EntityTwo 20	1

PostgreSQL:

Id	CollectionInverseId	Name	ReferenceInverseId	Id	CollectionInverseId	Name	ReferenceInverseId
2	1	EntityTwo 2	NULL	3	NULL	EntityTwo 3	NULL
11	9	EntityTwo 11	18	1	1	EntityTwo 1	NULL
2	1	EntityTwo 2	NULL	19	16	EntityTwo 19	3
5	3	EntityTwo 5	NULL	18	16	EntityTwo 18	5
13	11	EntityTwo 13	14	12	11	EntityTwo 12	16
20	17	EntityTwo 20	1	17	15	EntityTwo 17	7
6	5	EntityTwo 6	NULL	16	15	EntityTwo 16	9
14	13	EntityTwo 14	12	13	11	EntityTwo 13	14
5	3	EntityTwo 5	NULL	10	9	EntityTwo 10	20
4	3	EntityTwo 4	NULL	20	17	EntityTwo 20	1
20	17	EntityTwo 20	1	8	7	EntityTwo 8	NULL
11	9	EntityTwo 11	18	4	3	EntityTwo 4	NULL
8	7	EntityTwo 8	NULL	5	3	EntityTwo 5	NULL
18	16	EntityTwo 18	5	6	5	EntityTwo 6	NULL
14	13	EntityTwo 14	12	9	7	EntityTwo 9	NULL

[roji]

Another possibly related failure is Skip_navigation_long_count_with_predicate (Expected: 1, Actual: 3). The ordering is by a subquery with a COUNT_BIG(*) in its projection, but the value of that seems to be 0 for all rows in this test; on SQL Server the rows happen to come out ordered by the Id, but in PG it's random - could a 2nd ordering clause be missing for ties?

[roji]

@smitpatel let me know if you need any help with testing a fix etc.