SqliteException "no such column : rX.value" when ordering on subquery value in combination with a "where contains" when upgrading from 7 to 8.0.0-rc.2.23480.1

When upgrading
from
```C#
   <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.5">
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.5" />
```
 to 
```C#
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="8.0.0-rc.2.23480.1">
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="8.0.0-rc.2.23480.1" />
```

the following exception happens when running our test suite when querying 

```An exception occurred while iterating over the results of a query for context type 'Materialise.Limics.Infrastructure.LimicsContext'.
      Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such column: r4.value'.
         at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
         at Microsoft.Data.Sqlite.SqliteCommand.GetStatements()+MoveNext()
         at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
         at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
```

Reason is the following part in the query (full query below)
  SELECT **"r4"**."value"
              FROM json_each(@__request_RecordDefinitionIds_0) AS **"r2"**

the  actual test value" request.RecordDefinitionIds" in the c# query code is a list with one Guid. Previously this was translated as a regular equals (value was inline, as explained in  https://devblogs.microsoft.com/dotnet/announcing-ef8-preview-4/ )
 AND "t5"."RecordDefinitionId" = 'ACA990DF-C8BC-47FD-9CAF-B2FE4563D5EE' 


## C# code

### query
```C#
            var pageNr = Math.Max(0, request.PageNr - 1);
            var iterationValuesQuery = _context.IterationValues
                .Include(x => x.Iteration)
                .ThenInclude(x => x.Record)
                .ThenInclude(x => x.RecordObject)
                .Where(r => request.RecordDefinitionIds.Contains(r.Iteration.Record.RecordDefinitionId));

            iterationValuesQuery = WhereMatchCriteria(request, iterationValuesQuery);

            var recordIdsWithMatchCriteriaCountQuery =  iterationValuesQuery
                .GroupBy(x => new { x.IterationId, x.Iteration.RecordId })
                .Where(x => x.Count() >= request.Criteria.Count)
                .Select(x => new
                {
                    x.Key.RecordId,
                    x.Key.IterationId,
                    //for debugging
                    Count = x.Count(),
                    //for sorting
                    MaxTimestamp = x.Select(xx=>xx.Iteration.Timestamp).Max(),
                });

            var recordIdsWithMatchCriteriaCount = await  recordIdsWithMatchCriteriaCountQuery.OrderBy(x => x.MaxTimestamp)
                .Skip(pageNr * request.PageSize)
                .Take(request.PageSize)
                .ToListAsync(cancellationToken);
```
### Context.OnModelCreating
```C#
       var recordBuilder = modelBuilder.Entity<Record>().ToTable("Records", pdbSchema);
       recordBuilder.HasQueryFilter(x => x.TenantId == _tenantAccessor.GetTenantId());
       recordBuilder.HasMany(x => x.Iterations).WithOne(x => x.Record).IsRequired().OnDelete(DeleteBehavior.ClientCascade);

       var iterationBuilder = modelBuilder.Entity<Iteration>().ToTable("Iterations", pdbSchema);
       iterationBuilder.HasQueryFilter(x => x.TenantId == _tenantAccessor.GetTenantId());
       iterationBuilder.HasMany(x => x.IterationValues)
           .WithOne(x => x.Iteration).IsRequired().OnDelete(DeleteBehavior.ClientCascade);
       iterationBuilder.HasOne(x => x.Record).WithMany(x => x.Iterations).IsRequired();

       var iterationValueBuilder = modelBuilder.Entity<IterationValue>().ToTable("IterationValues", pdbSchema);
       iterationValueBuilder.HasQueryFilter(x => x.TenantId == _tenantAccessor.GetTenantId());

        var recordDefinitionBuilder = modelBuilder.Entity<RecordDefinition>()
            .ToTable("RecordDefinitions", pdbSchema);
        recordDefinitionBuilder.HasMany(x => x.Records)
            .WithOne(x => x.RecordDefinition)
            .OnDelete(DeleteBehavior.ClientNoAction);
```

### SQL Sqlite 8.0.0-rc.2.23480.1

```SQL
      SELECT "t"."RecordId", "i"."IterationId", COUNT(*) AS "Count", (
          SELECT MAX("t6"."Timestamp")
          FROM "IterationValues" AS "i4"
          INNER JOIN (
              SELECT "i5"."Id", "i5"."DatCre", "i5"."DatLu", "i5"."IterationState", "i5"."Number", "i5"."RecordId", "i5"."TenantId", "i5"."Timestamp", "i5"."UserCre", "i5"."UserLu"
              FROM "Iterations" AS "i5"
              WHERE "i5"."TenantId" = @__ef_filter__p_0
          ) AS "t4" ON "i4"."IterationId" = "t4"."Id"
          INNER JOIN (
              SELECT "r3"."Id", "r3"."DatCre", "r3"."DatLu", "r3"."EquipmentId", "r3"."IterationState", "r3"."RecordDefinitionId", "r3"."RecordObjectId", "r3"."TenantId", "r3"."UserCre", "r3"."UserLu"
              FROM "Records" AS "r3"
              WHERE "r3"."TenantId" = @__ef_filter__p_0
          ) AS "t5" ON "t4"."RecordId" = "t5"."Id"
          INNER JOIN (
              SELECT "i6"."Id", "i6"."DatCre", "i6"."DatLu", "i6"."IterationState", "i6"."Number", "i6"."RecordId", "i6"."TenantId", "i6"."Timestamp", "i6"."UserCre", "i6"."UserLu"
              FROM "Iterations" AS "i6"
              WHERE "i6"."TenantId" = @__ef_filter__p_0
          ) AS "t6" ON "i4"."IterationId" = "t6"."Id"
          WHERE "i4"."TenantId" = @__ef_filter__p_0 AND "t5"."RecordDefinitionId" IN (
              SELECT "r4"."value"
              FROM json_each(@__request_RecordDefinitionIds_0) AS "r4"
          ) AND "i"."IterationId" = "i4"."IterationId" AND "t"."RecordId" = "t4"."RecordId") AS "MaxTimestamp"
      FROM "IterationValues" AS "i"
      INNER JOIN (
          SELECT "i0"."Id", "i0"."RecordId"
          FROM "Iterations" AS "i0"
          WHERE "i0"."TenantId" = @__ef_filter__p_0
      ) AS "t" ON "i"."IterationId" = "t"."Id"
      INNER JOIN (
          SELECT "r"."Id", "r"."RecordDefinitionId"
          FROM "Records" AS "r"
          WHERE "r"."TenantId" = @__ef_filter__p_0
      ) AS "t0" ON "t"."RecordId" = "t0"."Id"
      WHERE "i"."TenantId" = @__ef_filter__p_0 AND "t0"."RecordDefinitionId" IN (
          SELECT "r0"."value"
          FROM json_each(@__request_RecordDefinitionIds_0) AS "r0"
      )
      GROUP BY "i"."IterationId", "t"."RecordId"
      HAVING COUNT(*) >= @__request_Criteria_Count_1
      ORDER BY (
          SELECT MAX("t6"."Timestamp")
          FROM "IterationValues" AS "i4"
          INNER JOIN (
              SELECT "i5"."Id", "i5"."DatCre", "i5"."DatLu", "i5"."IterationState", "i5"."Number", "i5"."RecordId", "i5"."TenantId", "i5"."Timestamp", "i5"."UserCre", "i5"."UserLu"
              FROM "Iterations" AS "i5"
              WHERE "i5"."TenantId" = @__ef_filter__p_0
          ) AS "t4" ON "i4"."IterationId" = "t4"."Id"
          INNER JOIN (
              SELECT "r3"."Id", "r3"."DatCre", "r3"."DatLu", "r3"."EquipmentId", "r3"."IterationState", "r3"."RecordDefinitionId", "r3"."RecordObjectId", "r3"."TenantId", "r3"."UserCre", "r3"."UserLu"
              FROM "Records" AS "r3"
              WHERE "r3"."TenantId" = @__ef_filter__p_0
          ) AS "t5" ON "t4"."RecordId" = "t5"."Id"
          INNER JOIN (
              SELECT "i6"."Id", "i6"."DatCre", "i6"."DatLu", "i6"."IterationState", "i6"."Number", "i6"."RecordId", "i6"."TenantId", "i6"."Timestamp", "i6"."UserCre", "i6"."UserLu"
              FROM "Iterations" AS "i6"
              WHERE "i6"."TenantId" = @__ef_filter__p_0
          ) AS "t6" ON "i4"."IterationId" = "t6"."Id"
          WHERE "i4"."TenantId" = @__ef_filter__p_0 AND "t5"."RecordDefinitionId" IN (
              SELECT "r4"."value"
              FROM json_each(@__request_RecordDefinitionIds_0) AS "r2"
          ) AND "i"."IterationId" = "i4"."IterationId" AND "t"."RecordId" = "t4"."RecordId")
      LIMIT @__p_3 OFFSET @__p_2
```

### Sql sqlite 7.05

```SQL
      SELECT "t"."RecordId", "i"."IterationId", COUNT(*) AS "Count", (
          SELECT MAX("t6"."Timestamp")
          FROM "IterationValues" AS "i4"
          INNER JOIN (
              SELECT "i5"."Id", "i5"."DatCre", "i5"."DatLu", "i5"."IterationState", "i5"."Number", "i5"."RecordId", "i5"."TenantId", "i5"."Timestamp", "i5"."UserCre", "i5"."UserLu"
              FROM "Iterations" AS "i5"
              WHERE "i5"."TenantId" = @__ef_filter__p_1
          ) AS "t4" ON "i4"."IterationId" = "t4"."Id"
          INNER JOIN (
              SELECT "r1"."Id", "r1"."DatCre", "r1"."DatLu", "r1"."EquipmentId", "r1"."IterationState", "r1"."RecordDefinitionId", "r1"."RecordObjectId", "r1"."TenantId", "r1"."UserCre", "r1"."UserLu"
              FROM "Records" AS "r1"
              WHERE "r1"."TenantId" = @__ef_filter__p_2
          ) AS "t5" ON "t4"."RecordId" = "t5"."Id"
          INNER JOIN (
              SELECT "i6"."Id", "i6"."DatCre", "i6"."DatLu", "i6"."IterationState", "i6"."Number", "i6"."RecordId", "i6"."TenantId", "i6"."Timestamp", "i6"."UserCre", "i6"."UserLu"
              FROM "Iterations" AS "i6"
              WHERE "i6"."TenantId" = @__ef_filter__p_1
          ) AS "t6" ON "i4"."IterationId" = "t6"."Id"
          WHERE "i4"."TenantId" = @__ef_filter__p_0 AND "t5"."RecordDefinitionId" = 'ACA990DF-C8BC-47FD-9CAF-B2FE4563D5EE' AND "i"."IterationId" = "i4"."IterationId" AND "t"."RecordId" = "t4"."RecordId") AS "MaxTimestamp"
      FROM "IterationValues" AS "i"
      INNER JOIN (
          SELECT "i0"."Id", "i0"."RecordId"
          FROM "Iterations" AS "i0"
          WHERE "i0"."TenantId" = @__ef_filter__p_1
      ) AS "t" ON "i"."IterationId" = "t"."Id"
      INNER JOIN (
          SELECT "r"."Id", "r"."RecordDefinitionId"
          FROM "Records" AS "r"
          WHERE "r"."TenantId" = @__ef_filter__p_2
      ) AS "t0" ON "t"."RecordId" = "t0"."Id"
      WHERE "i"."TenantId" = @__ef_filter__p_0 AND "t0"."RecordDefinitionId" = 'ACA990DF-C8BC-47FD-9CAF-B2FE4563D5EE'
      GROUP BY "i"."IterationId", "t"."RecordId"
      HAVING COUNT(*) >= @__request_Criteria_Count_1
      ORDER BY (
          SELECT MAX("t6"."Timestamp")
          FROM "IterationValues" AS "i4"
          INNER JOIN (
              SELECT "i5"."Id", "i5"."DatCre", "i5"."DatLu", "i5"."IterationState", "i5"."Number", "i5"."RecordId", "i5"."TenantId", "i5"."Timestamp", "i5"."UserCre", "i5"."UserLu"
              FROM "Iterations" AS "i5"
              WHERE "i5"."TenantId" = @__ef_filter__p_1
          ) AS "t4" ON "i4"."IterationId" = "t4"."Id"
          INNER JOIN (
              SELECT "r1"."Id", "r1"."DatCre", "r1"."DatLu", "r1"."EquipmentId", "r1"."IterationState", "r1"."RecordDefinitionId", "r1"."RecordObjectId", "r1"."TenantId", "r1"."UserCre", "r1"."UserLu"
              FROM "Records" AS "r1"
              WHERE "r1"."TenantId" = @__ef_filter__p_2
          ) AS "t5" ON "t4"."RecordId" = "t5"."Id"
          INNER JOIN (
              SELECT "i6"."Id", "i6"."DatCre", "i6"."DatLu", "i6"."IterationState", "i6"."Number", "i6"."RecordId", "i6"."TenantId", "i6"."Timestamp", "i6"."UserCre", "i6"."UserLu"
              FROM "Iterations" AS "i6"
              WHERE "i6"."TenantId" = @__ef_filter__p_1
          ) AS "t6" ON "i4"."IterationId" = "t6"."Id"
          WHERE "i4"."TenantId" = @__ef_filter__p_0 AND "t5"."RecordDefinitionId" = 'ACA990DF-C8BC-47FD-9CAF-B2FE4563D5EE' AND "i"."IterationId" = "i4"."IterationId" AND "t"."RecordId" = "t4"."RecordId")
      LIMIT @__p_3 OFFSET @__p_2
```

### provider and version information

EF Core version:
Database provider: (e.g. Microsoft.EntityFrameworkCore.Sqlite)
Target framework: (e.g. .NET 8.0)
Operating system:
IDE: 17.8.0 Preview 5.0



Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SqliteException "no such column : rX.value" when ordering on subquery value in combination with a "where contains" when upgrading from 7 to 8.0.0-rc.2.23480.1 #32234

C# code

query

Context.OnModelCreating

SQL Sqlite 8.0.0-rc.2.23480.1

Sql sqlite 7.05

provider and version information

Metadata

Assignees

Labels

Type

Fields

Projects

Milestone

Relationships

Development

SqliteException "no such column : rX.value" when ordering on subquery value in combination with a "where contains" when upgrading from 7 to 8.0.0-rc.2.23480.1 #32234

Description

C# code

query

Context.OnModelCreating

SQL Sqlite 8.0.0-rc.2.23480.1

Sql sqlite 7.05

provider and version information

Metadata

Metadata

Assignees

Labels

Type

Fields

Projects

Milestone

Relationships

Development

Issue actions