Details
-
Bug
-
Resolution: Fixed
-
Major
-
6.0.0
-
Untriaged
-
Unknown
-
CX Sprint 145
Description
In the following query the predicate in the WHERE clause is incorrectly pushed through the running aggregate operator (AT subclause) which leads to wrong result.
Query:
select v.*, i/cnt * 100 as percentile, i, cnt
|
from
|
(select name, abv from beers order by name) v at i,
|
(select value count(*) from beers) cnt
|
where v.name = "Bill Old Ale"
|
order by percentile;
|
Relevant plan snippet:
running-aggregate [$$i] <- [tid()]
|
-- RUNNING_AGGREGATE |PARTITIONED|
|
project ([$$61])
|
-- STREAM_PROJECT |PARTITIONED|
|
exchange
|
-- SORT_MERGE_EXCHANGE [$$86(ASC) ] |PARTITIONED|
|
order (ASC, $$86)
|
-- STABLE_SORT [$$86(ASC)] |PARTITIONED|
|
exchange
|
-- ONE_TO_ONE_EXCHANGE |PARTITIONED|
|
select (eq($$86, "Bill Old Ale"))
|
-- STREAM_SELECT |PARTITIONED|
|
project ([$$86, $$61])
|
-- STREAM_PROJECT |PARTITIONED|
|
assign [$$61] <- [{"name": $$86, "abv": $$beers.getField("abv")}]
|
-- ASSIGN |PARTITIONED|
|
assign [$$86] <- [$$beers.getField("name")]
|
Incorrect result:
[
|
{
|
"percentile": 0.016975046681378374,
|
"i": 1,
|
"cnt": 5891,
|
"name": "Bill Old Ale",
|
"abv": 0
|
}
|
]
|
Workaround rewrite WHERE clause so it depends on the output of the running aggregate (variable "i") and therefore won't be pushed down.
where substr(substr(tostring(i), 0, 1) || v.name, 1) = "Bill Old Ale"
|
Correct result:
[
|
{
|
"percentile": 11.220505856391105,
|
"i": 661,
|
"cnt": 5891,
|
"name": "Bill Old Ale",
|
"abv": 0
|
}
|
]
|