[jira] [Created] (PHOENIX-3746) NTH_VALUE returns 'null' when aggregating results of aggregate sub-query
Matthew Silverman created PHOENIX-3746: -- Summary: NTH_VALUE returns 'null' when aggregating results of aggregate sub-query Key: PHOENIX-3746 URL: https://issues.apache.org/jira/browse/PHOENIX-3746 Project: Phoenix Issue Type: Bug Affects Versions: 4.9.0 Reporter: Matthew Silverman {code:sql} 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 | +--+ {code} 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. -- This message was sent by Atlassian JIRA (v6.3.15#6346)
[jira] [Updated] (PHOENIX-3746) NTH_VALUE returns 'null' when aggregating results of aggregate sub-query
[ https://issues.apache.org/jira/browse/PHOENIX-3746?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Matthew Silverman updated PHOENIX-3746: --- Attachment: PHOENIX-3476.patch > NTH_VALUE returns 'null' when aggregating results of aggregate sub-query > > > Key: PHOENIX-3746 > URL: https://issues.apache.org/jira/browse/PHOENIX-3746 > Project: Phoenix > Issue Type: Bug >Affects Versions: 4.9.0 >Reporter: Matthew Silverman > Attachments: PHOENIX-3476.patch > > > {code:sql} > 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 | > +--+ > {code} > 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. -- This message was sent by Atlassian JIRA (v6.3.15#6346)