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

Query using Row Value Constructor comprised of non leading PK columns returns incorrect results

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 4.8.0
    • Fix Version/s: 4.9.0, 4.8.2
    • Labels:
      None
    • Flags:
      Important

      Description

      I'm trying to use Row Value Constructors to do paging in a query in the below table:

      1. TABLE #
        CREATE TABLE IF NOT EXISTS COMMUNITIES.TEST (
        ORGANIZATION_ID CHAR(15) NOT NULL,
        SCORE DOUBLE NOT NULL,
        ENTITY_ID CHAR(15) NOT NULL
        CONSTRAINT TEST_PK PRIMARY KEY (
        ORGANIZATION_ID,
        SCORE DESC,
        ENTITY_ID DESC
        )

      ) VERSIONS=1, MULTI_TENANT=TRUE, REPLICATION_SCOPE=1, TTL=43200
      UPSERT INTO COMMUNITIES.TEST VALUES ('org1',3,'01');
      UPSERT INTO COMMUNITIES.TEST VALUES ('org1',2,'04');
      UPSERT INTO COMMUNITIES.TEST VALUES ('org1',2,'03');
      UPSERT INTO COMMUNITIES.TEST VALUES ('org1',1,'02');

      1. QUERY #
        SELECT entity_id, score
        FROM communities.test
        WHERE organization_id = 'org1'
        AND (score, entity_id) < (2, '04')
        ORDER BY score DESC, entity_id DESC
        LIMIT 3

      I'm specifying the score and entity_id of the second row so I'm expecting it to return the 2 last values (with id 03 and 02). However, it returns the 3 first values (and if it wasn't for the LIMIT 3 it would just return all).

        Attachments

        1. PHOENIX-3382_wip.patch
          11 kB
          James R. Taylor
        2. PHOENIX-3382.patch
          11 kB
          James R. Taylor

          Activity

            People

            • Assignee:
              jamestaylor James R. Taylor
              Reporter:
              jpalmert Joel Palmert
            • Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: