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

Incorrect Queries on Multi-tenant tables with WHERE clause containing Row Value Constructor

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 4.7.0
    • Fix Version/s: 4.8.0
    • Labels:

      Description

      Nothing returns from query against multi-tenant table where the SELECT statement contains a WHERE condition filtering on the multi-tenant field and also contains a row value constructor.

      REPRO:
      Run the statement below and see that no records are returned (the second upserted record should be returned). Notice that you can either remove the multi-tenancy of the table, or the "pk1 = 'a' AND" WHERE condition and the query returns the second record fine, but does not return the second record as it is.

      DROP TABLE IF EXISTS TEST_TABLE;
      CREATE TABLE IF NOT EXISTS TEST_TABLE (
      pk1 VARCHAR NOT NULL,
      pk2 DECIMAL NOT NULL,
      v1 VARCHAR
      CONSTRAINT PK PRIMARY KEY
      (
      pk1,
      pk2
      )
      ) MULTI_TENANT=true,IMMUTABLE_ROWS=true;
      CREATE INDEX TEST_INDEX ON TEST_TABLE (v1);

      upsert into TEST_TABLE (pk1, pk2, v1) values ('a', 1, 'value');
      upsert into TEST_TABLE (pk1, pk2, v1) values ('a', 2, 'value');

      SELECT pk1, pk2, v1
      FROM TEST_TABLE
      WHERE
      – notice that if you comment/remove the "pk1='a' AND " statement below the query returns the second record correctly
      pk1 = 'a' AND
      (pk1, pk2) > ('a', 1)
      ORDER BY PK1, PK2
      LIMIT 2;

        Attachments

          Activity

            People

            • Assignee:
              jamestaylor James Taylor
              Reporter:
              rangent Brian Esserlieu
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: