Query: Distinct on non-materialized QSRE could give incorrect result

[smitpatel]

https://github.com/aspnet/EntityFrameworkCore/blob/dev/src/EFCore.Specification.Tests/Query/GearsOfWarQueryTestBase.cs#L2726-L2738

Generates

SELECT [t].[HasSoulPatch]
FROM (
    SELECT DISTINCT [ig].*
    FROM [Gears] AS [ig]
    WHERE [ig].[Discriminator] IN (N'Officer', N'Gear')
) AS [t]

This could generate incorrect results if Gears table has some non-mapped columns. So even though expectation is distinct records there could be duplicates.
We should probably expand * projection to full list of mapped properties here to get closer to linq behavior.

@anpete @divega @ajcvickers - thoughts?

[divega]

@smitpatel explained that he was removing the star projection from cases like this, but it became apparent that for DISTINCT we should have the full list of columns (as opposed to just what is being projected in the outer projection).

Personally, I would not expect non-mapped columns to have an influence in the results.

There is however an optimization when the root is a scan over an entity type (with optional filters, paging and sorting): just eliminate the whole DISTINCT because we know that we will be getting a unique PK value per row (see #8643).

[ajcvickers]

Assigning to @anpete as part of #9290.

[smitpatel]

Generated SQL in new pipeline

SELECT [t].[HasSoulPatch]
FROM (
    SELECT DISTINCT [g].[Nickname], [g].[SquadId], [g].[AssignedCityName], [g].[CityOrBirthName], [g].[Discriminator], [g].[FullName], [g].[HasSoulPatch], [g].[LeaderNickname], [g].[LeaderSquadId], [g].[Rank]
    FROM [Gears] AS [g]
    WHERE [g].[Discriminator] IN (N'Gear', N'Officer')
) AS [t]