Paul Rogers created DRILL-5142:
----------------------------------
Summary: TestWindowFrame.testUnboundedFollowing relies on side
effects
Key: DRILL-5142
URL: https://issues.apache.org/jira/browse/DRILL-5142
Project: Apache Drill
Issue Type: Bug
Reporter: Paul Rogers
Assignee: Paul Rogers
Priority: Minor
The unit test {{TestWindowFrame.testUnboundedFollowing}} is one of a family of
tests that does the same query in two different ways, using the results of the
second to verify the first. Unfortunately, this particular tests "works" only
because it relies on undefined implementation artifacts about the way the
"verification" query is run in Drill.
Here is the query under test:
{code}
SELECT
position_id,
employee_id,
LAST_VALUE(employee_id)
OVER(PARTITION BY position_id
ORDER by employee_id
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS
`last_value`
FROM
dfs_test.`%s/window/b4.p4`
{code}
With expected results as follows:
{code}
1,0,9
...
1,9,9
{code}
Here is the "expected results" query:
{code}
SELECT
position_id,
employee_id,
MAX(employee_id) OVER(PARTITION BY position_id) AS `last_value`
FROM (
SELECT *
FROM dfs_test.`%s/window/b4.p4`
ORDER BY position_id, employee_id
)
{code}
The above happens to produce the correct results only because the query
executes in a single fragment. The query produces correct results with the
"unmanaged" external sort, but produces the following (valid) results with the
managed external sort:
{code}
1,0,9
1,2,9
...
1,9,9
1,1,9
{code}
The query relies on the inner query sort order "showing through" to the outer
query. But, if the query were distributed, the outer query would be unordered.
Hence, the verification query just happened to work, but is not actually valid.
The proper solution is to modify the verification query to move the ORDER BY to
the outer query:
{code}
...
FROM (
SELECT *
FROM dfs_test.`%s/window/b4.p4`
)
ORDER BY position_id, employee_id
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
