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

Row value constructors failed on the index, when len(table's pks) > 2 and table's 1st pk is index's last pk

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 4.5.2
    • Fix Version/s: 4.6.0
    • Component/s: None
    • Labels:
      None
    • Environment:

      cdh5.3.6

      Description

      1. Table has more than 2 primary keys;
      2. Table's 1st pk as index's last pk; eg. table's pks are (pk1, pk2, pk3), the failed index's pks are (pk2, pk3, pk1); table's pks are (1, 2, 3, 4), failed index's pks are (2, 3, 4, 5, 1);
      3. Use row value constructors on index with another condition that use one pks(not the table's 1st pk);
      4. You will get "DEGENERATE SCAN OVER TABLE_NAME"

      Here is the Test SQL

      DROP TABLE IF EXISTS T;
      
      CREATE TABLE IF NOT EXISTS T (
         PK1 VARCHAR not null,
         PK2 VARCHAR not null,
         PK3 VARCHAR not null,
         V1  VARCHAR,
         CONSTRAINT PK PRIMARY KEY (PK1, PK2, PK3)
      );
      
      CREATE INDEX IDX_T ON T
      (
         PK2, PK3, PK1
      );
      
      UPSERT INTO T VALUES('100', '200', '300', 'V');
      UPSERT INTO T VALUES('101', '201', '301', 'V');
      UPSERT INTO T VALUES('102', '202', '302', 'V');
      UPSERT INTO T VALUES('103', '203', '303', 'V');
      UPSERT INTO T VALUES('104', '204', '304', 'V');
      
      SELECT * FROM T;
      
      EXPLAIN
      SELECT PK1, PK2, PK3 FROM T WHERE 
      (PK2, PK3, PK1) >= ('202', '302', '102') 
      AND PK2 < '204'
      LIMIT 10;
      

      I've tried 3 primary key, here is the results.
      1. table's pks are (pk1, pk2, pk3);
      2. 132 means (pk1, pk3, pk2);

      index's pks order result
      132 correct
      213 correct
      231 fail
      312 correct
      321 correct

      I've also test this on table with 4, 5 pks

      len(pks) failed order
      3 231
      4 2341
      5 23451

        Attachments

        1. PHOENIX-2327.patch
          3 kB
          Chunhui Liu
        2. PHOENIX-2327_v1.patch
          4 kB
          James R. Taylor

          Activity

            People

            • Assignee:
              jamestaylor James R. Taylor
              Reporter:
              chunhui.L Chunhui Liu
            • Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: