NullCheck + base Id: ambiguous _id in PVT CTE
Reproduces the ambiguous _id defect in the PVT CTE.
See docs/BUG_PVT_NULLCHECK_AMBIGUOUS_ID.md.
A props filter == null switches the PVT CTE into the two-table form
(FROM _objects o_src LEFT JOIN _values v), while the base-field filter
is compiled without a table alias. _id is the only column present in
BOTH _objects and _values, so the combination produces
column reference "_id" is ambiguous.
Four cases: two controls that must stay green, the repro, and a control that
pins the diagnosis to _id specifically (_id_parent exists only
in _objects, so it must stay green even in the two-table form).
Scope: verified against redb.Postgres.Pro. MSSql.Pro and SQLite.Pro generate
the same shape but are out of scope for now.
See docs/BUG_PVT_NULLCHECK_AMBIGUOUS_ID.md.
A props filter == null switches the PVT CTE into the two-table form
(FROM _objects o_src LEFT JOIN _values v), while the base-field filter
is compiled without a table alias. _id is the only column present in
BOTH _objects and _values, so the combination produces
column reference "_id" is ambiguous.
Four cases: two controls that must stay green, the repro, and a control that
pins the diagnosis to _id specifically (_id_parent exists only
in _objects, so it must stay green even in the two-table form).
Scope: verified against redb.Postgres.Pro. MSSql.Pro and SQLite.Pro generate
the same shape but are out of scope for now.
var sw = Stopwatch.StartNew();
var log = new StringBuilder();
var summary = new List<string>();
var suspects = 0;
async Task CaseAsync(string label, string expectation, IRedbQueryable<EmployeeProps> query)
{
log.AppendLine("-----------------------------------------------------------------");
log.AppendLine($"-- {label}");
log.AppendLine($"-- expected: {expectation}");
log.AppendLine("-----------------------------------------------------------------");
string sql;
try
{
sql = await query.ToSqlStringAsync();
}
catch (Exception ex)
{
log.AppendLine($"-- SQL BUILD FAIL: {ex.GetType().Name}: {ex.Message}");
log.AppendLine();
summary.Add($"{label}: SQL build failed ({ex.GetType().Name})");
Console.WriteLine($"[BUILD-FAIL] {label}: {ex.Message}");
return;
}
log.AppendLine(sql);
log.AppendLine();
// Console.WriteLine();
// Console.WriteLine($"--- {label} (expected: {expectation}) ---");
// Console.WriteLine(sql);
// Static verdict: the two-table CTE form together with an unqualified _id.
var twoTableForm = sql.Contains("FROM _objects o_src");
var hasBareId = BareId.IsMatch(sql);
var verdict = twoTableForm && hasBareId ? "AMBIGUOUS" : "clean";
if (verdict == "AMBIGUOUS") suspects++;
// Dynamic verdict: does the database actually accept it.
string execVerdict;
try
{
var rows = await query.ToListAsync();
execVerdict = $"executed, {rows.Count} rows";
}
catch (Exception ex)
{
execVerdict = $"{ex.GetType().Name}: {ex.Message.Split('\n')[0]}";
}
log.AppendLine($"-- static: two-table form={twoTableForm}, bare _id={hasBareId} => {verdict}");
log.AppendLine($"-- runtime: {execVerdict}");
log.AppendLine();
summary.Add($"{label}: {verdict} | {execVerdict}");
Console.WriteLine($"[{verdict,9}] {label} -> {execVerdict}");
}
// 1. Control. Props == null alone: two-table form, but no base filter, so nothing to qualify.
await CaseAsync(
"control: props == null only",
"clean",
redb.Query<EmployeeProps>()
.Where(e => e.EmployeeCode == null)
.Take(10));
// 2. Control. Base Id alone: single-table subquery over _objects, name resolves in its own scope.
await CaseAsync(
"control: base Id only",
"clean",
redb.Query<EmployeeProps>()
.WhereRedb(o => o.Id > 0)
.Where(e => e.Age > 0)
.Take(10));
// 3. Repro. Both at once.
await CaseAsync(
"repro: props == null + base Id",
"AMBIGUOUS",
redb.Query<EmployeeProps>()
.Where(e => e.EmployeeCode == null)
.WhereRedb(o => o.Id > 0)
.Take(10));
// 4. Control. Same shape, but the base column exists only in _objects.
// Proves the defect is specific to _id and not to base-field pushdown in general.
await CaseAsync(
"control: props == null + base ParentId",
"clean",
redb.Query<EmployeeProps>()
.Where(e => e.EmployeeCode == null)
.WhereRedb(o => o.ParentId != null)
.Take(10));
string? outPath = null;
try
{
var dir = Path.Combine(AppContext.BaseDirectory, "Output");
Directory.CreateDirectory(dir);
outPath = Path.Combine(dir, "nullcheck_baseid_ambiguity.txt");
await File.WriteAllTextAsync(outPath, log.ToString());
}
catch (Exception ex)
{
Console.WriteLine($"[WARN] failed to write dump file: {ex.Message}");
}
sw.Stop();
// The run time above is the sum over all the queries of the example, each printed and then
// executed, so the count comes first: without it the figure reads as one slow query.
var output = new List<string>
{
$"{summary.Count} queries in this example: SQL printed, then each executed"
};
output.AddRange(summary);
output.AddRange(new[]
{
$"Suspect cases: {suspects} (expected 1: the repro)",
outPath != null ? $"Output: {outPath}" : "Output file: <not written>",
"Report: docs/BUG_PVT_NULLCHECK_AMBIGUOUS_ID.md"
});
PG Pro
487ms(4)
postgres.pro.repeat
466ms(4)
postgres.pro.cache
500ms(4)
MS Pro
336ms(4)
mssql.pro.repeat
639ms(4)
mssql.pro.cache
186ms(4)
sqlite.pro
234ms(4)
sqlite.pro.repeat
221ms(4)
sqlite.pro.cache
225ms(4)