A grouped query should be "flat" when the query omits WITH ROLLUP. This means that the root row should not be selected in this case.
The root row is a by-product of the row binning that occurs if a GROUP BY is present in the query. Internally, the engine builds out the full tree following the group selectors, and then at the end if WITH ROLLUP isn't present, the engine will strip all children below the first level.
Reproduction:
SELECT
Avg(x),
Sum(y)
FROM
Lattice('x = 1 to 10 step 1, y = 1 to 10 step 1')
GROUP BY x % 3, y
The above query should result in a flat table (as it does on most SQL engines), but on MQL the table has a root row at the top:
Historically, the C# engine also exhibited this issue. While we shouldn't use it as a gold standard, it's important for us to note that we would be intentionally breaking with the C# engine's behavior.
A grouped query should be "flat" when the query omits
WITH ROLLUP. This means that the root row should not be selected in this case.The root row is a by-product of the row binning that occurs if a GROUP BY is present in the query. Internally, the engine builds out the full tree following the group selectors, and then at the end if
WITH ROLLUPisn't present, the engine will strip all children below the first level.Reproduction:
The above query should result in a flat table (as it does on most SQL engines), but on MQL the table has a
rootrow at the top:Historically, the C# engine also exhibited this issue. While we shouldn't use it as a gold standard, it's important for us to note that we would be intentionally breaking with the C# engine's behavior.
MavenScape behavior: