[jira] [Updated] (PHOENIX-3867) nth_value returns valid values for non-existing rows

["Thomas D'Silva (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/PHOENIX-3867?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Thomas D'Silva updated PHOENIX-3867:
------------------------------------
    Fix Version/s:     (was: 4.15.0)

> 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 
>            Priority: Major
>
> 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
(v7.6.3#76005)