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
Comments
tim-stuyckens-materialise
changed the title
|
@roji This looks like a regression from 7.0 with JSON-based Contains on SQLIte. Runnable repro below; still fails on daily: using (var context = new SomeDbContext())
{
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
var definitionIds = new[] { 1 };
var pageNr = 1;
var crCount = 2;
var iterationValuesQuery = context.IterationValues
.Include(x => x.Iteration)
.ThenInclude(x => x.Record)
.ThenInclude(x => x.RecordObject)
.Where(r => definitionIds.Contains(r.Iteration!.Record.RecordDefinitionId));
var recordIdsWithMatchCriteriaCountQuery = iterationValuesQuery
.GroupBy(x => new { x.IterationId, x.Iteration.RecordId })
.Where(x => x.Count() >= crCount)
.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 ps = 20;
var recordIdsWithMatchCriteriaCount = await recordIdsWithMatchCriteriaCountQuery.OrderBy(x => x.MaxTimestamp)
.Skip(pageNr * ps)
.Take(ps)
.ToListAsync();
}
public class SomeDbContext : DbContext
{
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlite("Data Source=test.db")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
public DbSet<IterationValue> IterationValues => Set<IterationValue>();
private int GetTenantId() => 1;
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
var pdbSchema = "pdb";
var recordBuilder = modelBuilder.Entity<Record>().ToTable("Records", pdbSchema);
recordBuilder.HasQueryFilter(x => x.TenantId == 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 == 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 == GetTenantId());
var recordDefinitionBuilder = modelBuilder.Entity<RecordDefinition>()
.ToTable("RecordDefinitions", pdbSchema);
recordDefinitionBuilder.HasMany(x => x.Records)
.WithOne(x => x.RecordDefinition)
.OnDelete(DeleteBehavior.ClientNoAction);
}
}
public class RecordDefinition
{
public int Id { get; set; }
public List<Record> Records { get; set; } = new();
}
public class IterationValue
{
public int Id { get; set; }
public int TenantId { get; set; }
public Iteration? Iteration { get; set; }
public int IterationId { get; set; }
}
public class Iteration
{
public int Id { get; set; }
public int TenantId { get; set; }
public int RecordId { get; set; }
public Record Record { get; set; } = null!;
public List<IterationValue> IterationValues { get; set; } = new();
public DateTime Timestamp { get; set; }
}
public class Record
{
public int Id { get; set; }
public int TenantId { get; set; }
public List<Iteration> Iterations { get; set; } = new();
public RecordDefinition RecordDefinition { get; set; }
public RecordObject RecordObject { get; set; }
public int RecordDefinitionId { get; set; }
}
public class RecordObject
{
public int Id { get; set; }
} |
|
@ajcvickers thanks for the repro - I'll put this on my high-priority list. |
|
Done some deep investigation into this, here are some preliminary observations. Minimal repro query: _ = await context.IterationValues
.Where(r => ids.Contains(r.IterationId))
.GroupBy(x => x.IterationId)
.Select(x => new
{
x.Key,
MaxTimestamp = x.Select(x => x.Iteration.Timestamp).Max(),
})
.OrderBy(x => x.MaxTimestamp)
.ToListAsync();Full minimal repro codeawait using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();
var ids = new[] { 1 };
_ = await context.IterationValues
.Where(r => ids.Contains(r.IterationId))
.GroupBy(x => x.IterationId)
.Select(x => new
{
x.Key,
MaxTimestamp = x.Select(x => x.Iteration.Timestamp).Max(),
})
.OrderBy(x => x.MaxTimestamp)
.ToListAsync();
public class BlogContext : DbContext
{
public DbSet<IterationValue> IterationValues => Set<IterationValue>();
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();
}
public class IterationValue
{
public int Id { get; set; }
public Iteration? Iteration { get; set; }
public int IterationId { get; set; }
}
public class Iteration
{
public int Id { get; set; }
public List<IterationValue> IterationValues { get; set; } = new();
public DateTime Timestamp { get; set; }
}SELECT [i].[IterationId] AS [Key], (
SELECT MAX([i5].[Timestamp])
FROM [IterationValues] AS [i4]
INNER JOIN [Iteration] AS [i5] ON [i4].[IterationId] = [i5].[Id]
WHERE [i4].[IterationId] IN (
SELECT [i6].[value]
FROM OPENJSON(@__ids_0) WITH ([value] int '$') AS [i6]
) AND [i].[IterationId] = [i4].[IterationId]) AS [MaxTimestamp]
FROM [IterationValues] AS [i]
WHERE [i].[IterationId] IN (
SELECT [i0].[value]
FROM OPENJSON(@__ids_0) WITH ([value] int '$') AS [i0]
)
GROUP BY [i].[IterationId]
ORDER BY (
SELECT MAX([i5].[Timestamp])
FROM [IterationValues] AS [i4]
INNER JOIN [Iteration] AS [i5] ON [i4].[IterationId] = [i5].[Id]
WHERE [i4].[IterationId] IN (
SELECT [i6].[value] -- Incorrect [i6] - should be [i3]
FROM OPENJSON(@__ids_0) WITH ([value] int '$') AS [i3]
) AND [i].[IterationId] = [i4].[IterationId])
This seems like an infrastructural bug that was present before, and can be triggered regardless of the recent Contains/JSON changes; any time a visitor causes a SelectExpression to be replaced, and that SelectExpression is referenced from multiple places, this bug would manifest itself. One easier/short-term way to fix this, is to recursively to over the new SelectExpression and replace the TableReferenceExpressions themselves (rather than updating them, since they may be shared). This would be relatively inefficient (this needs to be done any time we replace a SelectExpression), and makes the concept of TableReferenceExpression quite redundant (ColumnExpressions might as well just reference directly, and we'd update them). However, more generally, we should not end up in a situation where the same SelectExpression gets referenced from two places in the query; this is very inefficient SQL, since e.g. the potentially expensive subquery above needs to be evaluated twice. Instead, we should perform a pushdown to a subquery and apply the OrderBy over that. If we never have a SelectExpression referenced from multiple places, this problem wouldn't occur. |
|
Note specifically on this bug:
This in itself isn't necessarily a bug; having a column from one select pointing to the table of another select is OK, as long as things are fully immutable (as expression trees where meant to be). The problem here comes from the fact that table alias uniquification actually mutates the TableReferenceExpression; this is what makes the sharing of TableReferenceExpressions across SelectExpressions problematic. So ignoring the more general problem of duplicate subqueries in SQL (split out to #32277), a targeted fix here would either:
|
|
Removing servicing-consider as a risk here is likely to be non-trivial and somewhat risky. One targeted workaround for the very specific regression above (with Contains) is to set the SQL Server compatibility level to a low value, which will tricker reverting back to the previous transaction (which has no subquery and therefore doesn't trigger this). |
|
Hi, Any suggestion on how this can be mitigated on SQLite or MySQL? |
|
This is also hitting us when updating from dotnet 7 til 8. While the compatibility workaround could work for sqlserver, it doesn't exactly help us for sqlite, and is completely blocking is from upgrading to dotnet 8. |
|
Everyone, thanks for reporting, I'll look at patching this for 8.0. |
|
Reopening to consider patching. |
…ildren (dotnet#32456) Fixes dotnet#32234 (cherry picked from commit cf5ec40)
…ildren Fixes dotnet#32234 (cherry picked from commit cf5ec40)
ajcvickers
added
closed-fixed
…ildren Fixes dotnet#32234 (cherry picked from commit cf5ec40)
When upgrading
from
to
the following exception happens when running our test suite when querying
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
Context.OnModelCreating
SQL Sqlite 8.0.0-rc.2.23480.1
Sql sqlite 7.05
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
The text was updated successfully, but these errors were encountered: