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

Incorrect determination of preservation of order for an aggregate query leads to incorrect query results

    Details

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

      Description

      This may be related to PHOENIX-3452 but the behavior is different so filing it separately.
      Steps to repro:

      CREATE TABLE IF NOT EXISTS TEST.TEST (
      ORGANIZATION_ID CHAR(15) NOT NULL,
      CONTAINER_ID CHAR(15) NOT NULL,
      ENTITY_ID CHAR(15) NOT NULL,
      SCORE DOUBLE,
      CONSTRAINT TEST_PK PRIMARY KEY (
      ORGANIZATION_ID,
      CONTAINER_ID,
      ENTITY_ID
      )
      ) VERSIONS=1, MULTI_TENANT=TRUE, REPLICATION_SCOPE=1, TTL=31536000;
      CREATE INDEX IF NOT EXISTS TEST_SCORE ON TEST.TEST (CONTAINER_ID, SCORE DESC, ENTITY_ID DESC);

      UPSERT INTO test.test VALUES ('org2','container2','entityId6',1.1);
      UPSERT INTO test.test VALUES ('org2','container1','entityId5',1.2);
      UPSERT INTO test.test VALUES ('org2','container2','entityId4',1.3);
      UPSERT INTO test.test VALUES ('org2','container1','entityId3',1.4);
      UPSERT INTO test.test VALUES ('org2','container3','entityId7',1.35);
      UPSERT INTO test.test VALUES ('org2','container3','entityId8',1.45);

      EXPLAIN
      SELECT DISTINCT entity_id, score
      FROM test.test
      WHERE organization_id = 'org2'
      AND container_id IN ( 'container1','container2','container3' )
      ORDER BY score DESC
      LIMIT 2

      OUTPUT
      entityId5 1.2
      entityId3 1.4

      The expected out out would be
      entityId8 1.45
      entityId3 1.4

      You will get the expected output if you remove the secondary index from the table or remove distinct from the query.

      As described in PHOENIX-3452 if you run the query without the LIMIT the ordering is not correct. However, the 2first results in that ordering is still not the onces returned by the limit clause, which makes me think there are multiple issues here and why I filed both separately. The rows being returned are the ones assigned to container1. It looks like Phoenix is first getting the rows from the first container and when it finds that to be enough it stops the scan. What it should be doing is getting 2 results for each container and then merge then and then limit again.

        Attachments

        1. PHOENIX-3451_v2.patch
          18 kB
          James Taylor
        2. PHOENIX-3451_v1.patch
          11 kB
          James Taylor

          Issue Links

            Activity

              People

              • Assignee:
                comnetwork chenglei
                Reporter:
                jpalmert Joel Palmert
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: