Uploaded image for project: 'Phoenix'
  1. Phoenix
  2. PHOENIX-3342

ORDER BY and LIMIT+OFFSET doesnt work on second column from compound key

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.8.1
    • Fix Version/s: 4.9.0, 4.8.2
    • Labels:
      None

      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)
      

        Attachments

        1. PHOENIX-3342_v1.patch
          10 kB
          Ankit Singhal
        2. PHOENIX-3342_v2_4.8_branch.patch
          11 kB
          Ankit Singhal
        3. PHOENIX-3342_v2.patch
          11 kB
          Ankit Singhal
        4. PHOENIX-3342.patch
          6 kB
          Ankit Singhal
        5. PHOENIX-3342-wip.patch
          4 kB
          Sergey Soldatov

          Issue Links

            Activity

              People

              • Assignee:
                ankit@apache.org Ankit Singhal
                Reporter:
                0x62ash Alex Batyrshin
              • Votes:
                1 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: