GROUP BY writes its reserved `key` parameter into the caller's parameter document
Version
dev at 7d2a16c4. Behaviour is identical on #2905.
Describe the bug
Executing a GROUP BY statement writes the current group key into the BsonDocument that the caller passed as SQL parameters. After the call, the caller's document contains a key entry it never had. If the caller already uses a parameter named key, its value is overwritten, and reusing that document for another statement silently returns wrong results.
Code to reproduce
using var db = new LiteDatabase(":memory:");
db.GetCollection("rows").InsertBulk(Enumerable.Range(1, 20).Select(i =>
new BsonDocument { ["_id"] = i, ["City"] = "City" + i % 3 }));
var parameters = new BsonDocument { ["min"] = 5 };
using (var reader = db.Execute(
"SELECT { city: @key, n: COUNT(*) } FROM rows WHERE _id > @min GROUP BY City", parameters))
{
while (reader.Read()) { }
}
// parameters is now {"min":5,"key":"City2"} expected: {"min":5}With a caller-owned key:
var own = new BsonDocument { ["key"] = 5 };
using (var reader = db.Execute(
"SELECT { city: @key, n: COUNT(*) } FROM rows WHERE _id > @key GROUP BY City", own))
{
while (reader.Read()) { }
}
// own is now {"key":"City2"}
using (var reader = db.Execute("SELECT _id FROM rows WHERE _id > @key", own))
{
// actual: no rows
// expected: 15 rows (_id 6..20)
}SELECT $ ... GROUP BY City does not leak; a projection that uses @key, and HAVING, do.
Expected behavior
Executing a query should not modify the caller's parameter document. The reserved key binding should live in a document owned by the query execution.
Cause
SqlParser hands the caller's BsonDocument to every parsed clause by reference, so Select.Parameters and Having.Parameters are the caller's object. GroupByPipe.SetKeyParameter then does expression.Parameters["key"] = key once per group (LiteDB/Engine/Query/Pipeline/GroupByPipe.cs:147).
The same shared, mutated binding is what made the position of @key in the select list matter in #2323. Giving the group pipeline its own parameter document (a shallow copy of the caller's plus key) would address both.
Workaround
Pass a fresh BsonDocument to each GROUP BY statement, and avoid naming your own parameter key.
Found while auditing #2905; it is not caused by that PR.
Source: litedb-org/LiteDB