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