OrderBy with Distinct is incorrectly translated into SELECT 1

[Rimobul]

Steps to reproduce

1. Create a table with at least one Guid (non-nullable), one DateTime (non-nullable) and one boolean (non-nullable) fields.
2. Populate the table with valid data.
3. Run the following query:

var idForInvestigation = Guid.NewGuid();
var limit = 100;
var result = context.MyTestTables
    .Where(x => x.IsChecked && x.ParentId == idForInvestigation)
    .OrderBy(x => x.CreatedAt)
    .Distinct()
    .Skip(limit)
    .Take(limit + 1)
    .ToList();

The issue

The code produces the following SQL query:

SELECT DISTINCT t."ParentId", t."IsChecked", t."CreatedAt", t."CreatedBy", t."Title", t."Description"
FROM public."MyTestTable" AS t
WHERE t."IsChecked" AND (t."Id" = @__idForInvestigation_0)
ORDER BY (SELECT 1)
LIMIT @__p_2 OFFSET @__p_1

The query fails with the exception message:

Npgsql.PostgresException (0x80004005): 42P10: for SELECT DISTINCT, ORDER BY expressions must appear in select list
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<<DoReadMessage>g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
   at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   ...local files...
  Exception data:
    Severity: ERROR
    SqlState: 42P10
    MessageText: for SELECT DISTINCT, ORDER BY expressions must appear in select list
    Position: 390
    File: parse_clause.c
    Line: 2822
    Routine: transformDistinctClause

It seems the .OrderBy(x => x.CreatedAt) is incorrectly translated into ORDER BY (SELECT 1) instead of ORDER BY t."CreadtedAt". I have tried ordering the query by other columns, but the exception remains the same. When I remove the .Distinct() clause, the query is evaluated successfully:

SELECT t."ParentId", t."IsChecked", t."CreatedAt", t."CreatedBy", t."Title", t."Description"
FROM public."MyTestTable" AS t
WHERE t."IsChecked" AND (t."Id" = @__idForInvestigation_0)
ORDER BY t."CreatedAt"
LIMIT @__p_2 OFFSET @__p_1

Further technical details

Npgsql version: 4.1.2
Npgsql.EntityFrameworkCore.PostgreSQL: 3.1.0
PostgreSQL version: 10.6
Operating system: Windows 10 64-bit

Other details about my project setup: ASP.NET Core 3.1 web application

[roji]

This is a duplicate of dotnet/efcore#8523.

Distinct doesn't preserve ordering, so the OrderBy which comes before is erased. The Skip/Take coming after the distinct are generated, and EF Core adds ORDER BY (SELECT 1) since no ordering is provided - this is necessary in SQL Server and is currently being done for PostgreSQL too, even though it's not needed. Of course, this shouldn't generate invalid SQL (that's what dotnet/efcore#8523 is about), but you probably want to simply place the OrderBy after the Distinct:

var result = context.MyTestTables
    .Where(x => x.IsChecked && x.ParentId == idForInvestigation)
    .Distinct()
    .OrderBy(x => x.CreatedAt)
    .Skip(limit)
    .Take(limit + 1)
    .ToList();

This generates the following SQL:

SELECT t."Id", t."CreatedAt", t."IsChecked", t."ParentId"
      FROM (
          SELECT DISTINCT m."Id", m."CreatedAt", m."IsChecked", m."ParentId"
          FROM "MyTestTables" AS m
          WHERE m."IsChecked" AND (m."ParentId" = @__idForInvestigation_0)
      ) AS t
      ORDER BY t."CreatedAt"
      LIMIT @__p_2 OFFSET @__p_1

I'll add a note on dotnet/efcore#8523.