[PHOENIX-3342] ORDER BY and LIMIT+OFFSET doesnt work on second column from compound key

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 4.8.1
• Fix Version/s: 4.9.0, 4.8.2
• Assignee: Ankit Singhal
• Reporter: Alex Batyrshin
• Created: 01/Oct/16 16:19
• Updated: 21/Oct/16 18:25
• Resolved: 21/Oct/16 14:51

Description

Here is simple test case

CREATE TABLE "test" (
    col1 VARCHAR,
    col2 VARCHAR,
    "foo"."data" VARCHAR,
    CONSTRAINT PK PRIMARY KEY (col1, col2)
);

0: jdbc:phoenix:localhost> upsert into "test" (COL1, COL2, "data") values ('1', '1', 'd1');
1 row affected (0.044 seconds)
0: jdbc:phoenix:localhost> upsert into "test" (COL1, COL2, "data") values ('1', '2', 'd2');
1 row affected (0.008 seconds)
0: jdbc:phoenix:localhost> upsert into "test" (COL1, COL2, "data") values ('1', '3', 'd3');
1 row affected (0.007 seconds)

0: jdbc:phoenix:localhost> select * from "test" order by col2;
+-------+-------+-------+
| COL1  | COL2  | data  |
+-------+-------+-------+
| 1     | 1     | d1    |
| 1     | 2     | d2    |
| 1     | 3     | d3    |
+-------+-------+-------+
3 rows selected (0.026 seconds)

0: jdbc:phoenix:localhost> select * from "test" order by col2 limit 1;
+-------+-------+-------+
| COL1  | COL2  | data  |
+-------+-------+-------+
| 1     | 1     | d1    |
+-------+-------+-------+
1 row selected (0.026 seconds)

0: jdbc:phoenix:localhost> select * from "test" order by col2 offset 1;
+-------+-------+-------+
| COL1  | COL2  | data  |
+-------+-------+-------+
| 1     | 2     | d2    |
| 1     | 3     | d3    |
+-------+-------+-------+
2 rows selected (0.02 seconds)

And this query doesn't work as expected:

0: jdbc:phoenix:localhost> select * from "test" order by col2 limit 1 offset 1;
+-------+-------+-------+
| COL1  | COL2  | data  |
+-------+-------+-------+
+-------+-------+-------+
No rows selected (0.024 seconds)

Issue Links

• duplicates: PHOENIX-3353 Paged Queries: use of LIMIT + OFFSET with ORDER BY