[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)
