[PHOENIX-3746] NTH_VALUE returns 'null' when aggregating results of aggregate sub-query

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.9.0
• Fix Version/s: 4.11.0
• Assignee: Matthew Silverman
• Reporter: Matthew Silverman
• Created: 20/Mar/17 17:54
• Updated: 21/Mar/17 23:30
• Resolved: 21/Mar/17 23:30

Description

CREATE TABLE IF NOT EXISTS TEST_TABLE (id INTEGER NOT NULL PRIMARY KEY, page_id UNSIGNED_LONG, "DATE" INTEGER, "value" UNSIGNED_LONG);
       
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (1, 8, 0, 300);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (2, 8, 1, 7);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (3, 9, 2, 9);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (4, 9, 3, 4);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (5, 10, 4, 2);
UPSERT INTO TEST_TABLE (id, page_id, "DATE", "value") VALUES (6, 10, 5, 150);

SELECT NTH_VALUE(SUM_VALUE, 2) WITHIN GROUP (ORDER BY MIN_DATE DESC) FROM
 (SELECT MIN("DATE") AS MIN_DATE, SUM("value") AS SUM_VALUE FROM TEST_TABLE GROUP BY page_id) x;

+--------------------------------------+
| null(MIN_DATE, false, SUM_VALUE, 2)  |
+--------------------------------------+
| null                                 |
+--------------------------------------+

I would have expected '13' to be returned, as it is equal to 4+9.

It appears that the offset field of the server-side aggregator is being reset on every call to reset(), when it should persist permanently on the aggregator once set. I have attached the above example as an integration test, together with a potential fix.