[PHOENIX-4869] Empty row when using OFFSET + LIMIT

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 4.7.0
• Assignee: Unassigned
• Reporter: Gardella Juan Pablo
• Created: 25/Aug/18 21:33
• Updated: 06/Sep/18 13:25

Description

I'm using Phoenix shipped at HDP 2.6.1 and HDP 2.6.3. I have a table defined as:

 
create table test (
   id VARCHAR not null primary key,
   json VARCHAR,
   ts timestamp
)

-- POPULATE THE TABLE
-- Create an index.

CREATE INDEX testix ON test (ts) INCLUDE(id,json);

IMPORTANT: Without the index the issue does not happen.

It has 2559774 rows. If I execute the following query, it returns a row with a null value.

select * from
(
   SELECT ID
   FROM test
   LIMIT 100000 OFFSET 100000
)
where ID is null

 
I was reviewing the git logs and I didn't see any commit related to that[1]. Notice the query for OFFSET and LIMIT lowers than 100000 does not fail. I've attached a capture of the query results.

Notice if I execute SELECT ID FROM test WHERE ID IS NULL returns an empty result as expected.

 

Thread: https://lists.apache.org/thread.html/fd54a0cf623a20ad54d1ac65656d01add8eeef74ad51fb1674afb566@%3Cuser.phoenix.apache.org%3E

 [1] Similar but not equal is PHOENIX-3422. The results is no data instead of null row.