[
https://issues.apache.org/jira/browse/PHOENIX-3867?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16190899#comment-16190899
]
James Taylor commented on PHOENIX-3867:
---------------------------------------
Ping [~singamteja]?
> nth_value returns valid values for non-existing rows
> -----------------------------------------------------
>
> Key: PHOENIX-3867
> URL: https://issues.apache.org/jira/browse/PHOENIX-3867
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 4.10.0
> Reporter: Loknath Priyatham Teja Singamsetty
> Fix For: 4.12.0
>
>
> Assume a table with two rows as follows:
> id, page_id, date, value
> 2, 8 , 1 , 7
> 3, 8 , 2, 9
> Fetch 3rd most recent value of page_id 3 should not return any values.
> However, rs.next() succeeds and rs.getInt(1) returns 0 and the assertion
> fails. Below is the test case depicting the same.
> Issues:
> --------
> a) From sqline, the 3rd nth_value is returned as null
> b) When programatically accessed, it is coming as 0
> Test Case:
> -------------
> public void nonExistingNthRowTestWithGroupBy() throws Exception {
> Connection conn = DriverManager.getConnection(getUrl());
> String nthValue = generateUniqueName();
> String ddl = "CREATE TABLE IF NOT EXISTS " + nthValue + " "
> + "(id INTEGER NOT NULL PRIMARY KEY, page_id UNSIGNED_LONG,"
> + " dates INTEGER, val INTEGER)";
> conn.createStatement().execute(ddl);
> conn.createStatement().execute(
> "UPSERT INTO " + nthValue + " (id, page_id, dates, val) VALUES
> (2, 8, 1, 7)");
> conn.createStatement().execute(
> "UPSERT INTO " + nthValue + " (id, page_id, dates, val) VALUES
> (3, 8, 2, 9)");
> conn.commit();
> ResultSet rs = conn.createStatement().executeQuery(
> "SELECT NTH_VALUE(val, 3) WITHIN GROUP (ORDER BY dates DESC) FROM
> " + nthValue
> + " GROUP BY page_id");
> assertTrue(rs.next());
> assertEquals(rs.getInt(1), 4);
> assertFalse(rs.next());
> }
> Root Cause:
> ---------------
> The underlying issue seems to be with the way NTH_Value aggregation is done
> by the aggregator. The client aggregator is first populated with the top 'n'
> rows (if present) and during the iterator.next() never gets evaluated in
> BaseGroupedAggregatingResultIterator to see if the nth row is actually
> present or not. Once the iterator.next() succeeds, retrieving the value from
> the result set using the row projector triggers the client aggregators
> evaluate() method as part of schema.toBytes(..) which is defaulting to 0 for
> empty row if it is int when programmatically accessed.
>
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
