Uploaded image for project: 'Apache Trafodion (Retired)'
  1. Apache Trafodion (Retired)
  2. TRAFODION-14

upsert or merge into a table with indexes can result in inconsistency between index and table

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Critical
    • Resolution: Fixed
    • None
    • 2.0-incubating
    • None
    • None

    Description

      Steps to reproduce this problem

      set schema seabase ;
      CREATE TABLE TRAFODION.SEABASE.TEST1
      (
      A INT NO DEFAULT NOT NULL NOT DROPPABLE
      , B INT DEFAULT NULL
      , PRIMARY KEY (A ASC)
      )
      ;

      CREATE INDEX IDX1 ON TRAFODION.SEABASE.TEST1
      (
      B ASC
      )
      ;

      CREATE TABLE TRAFODION.SEABASE.TEST2
      (
      A INT NO DEFAULT NOT NULL NOT DROPPABLE
      , B INT DEFAULT NULL
      , PRIMARY KEY (A ASC)
      )
      ;

      insert into test2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9),(10,10) ;

      prepare s2 from upsert into test1 select * from test2;
      1) execute s2;
      2) select * from test1 ;

      If you repeat steps 1 and 2 more than once, at times fewer than 10 rows are selected from the step 2.

      In this particular case select * from test1 involves index scan plan.

      Attachments

        Activity

          People

            selvag Selvaganesan Govindarajan
            selvag Selvaganesan Govindarajan
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: