You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "Sumit Nigam (JIRA)" <ji...@apache.org> on 2015/12/11 05:55:10 UTC

[jira] [Created] (PHOENIX-2514) Even with ORDER BY clause the LIMIT does not work correctly with salted tables containing many records.

Sumit Nigam created PHOENIX-2514:
------------------------------------

             Summary: Even with ORDER BY clause the LIMIT does not work correctly with salted tables containing many records.
                 Key: PHOENIX-2514
                 URL: https://issues.apache.org/jira/browse/PHOENIX-2514
             Project: Phoenix
          Issue Type: Bug
    Affects Versions: 4.5.1
         Environment: HBase-0.98.14
            Reporter: Sumit Nigam
            Priority: Critical
             Fix For: 4.5.1


A query such as SELECT CURRENT_TIMESTAMP FROM TBL ORDER BY CURRENT_TIMESTAMP DESC LIMIT 1 does not really return the MAX(CURRENT_TIMESTAMP). The table is salted and has 200272 records.

select current_timestamp from TBL order by current_timestamp desc limit 1;
+------------------------------------------+
|            CURRENT_TIMESTAMP    |
+------------------------------------------+
| 1448815328556                            |
+------------------------------------------+


select max(current_timestamp) from TBL;
+------------------------------------------+
|         MAX("CURRENT_TIMESTAMP")         |
+------------------------------------------+
| 1449732792090                            |
+------------------------------------------+

The results are different. MAX is of course, returning the right record.

The above query is one example. There are other queries which also seem to be returning incorrect record with ORDER BY and LIMIT.

Is this also correct that when there is a WHERE clause limiting the number of projected records, then LIMIT seems to work fine? I seem to be noticing that also.

The table DDL is:
CREATE TABLE IF NOT EXISTS TBL 
(CURRENT_TIMESTAMP BIGINT NOT NULL, ID VARCHAR(96), CURR_EXDOC VARCHAR,  CURR_CHECKSUM VARCHAR(32), SUMMARY VARCHAR, 
CONSTRAINT PK PRIMARY KEY(CURRENT_TIMESTAMP, ID)) 
BLOCKCACHE=FALSE, COMPRESSION=SNAPPY, SALT_BUCKETS=8



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)