update involving mod() func results in data corruption

[Julie Thai]

Table contains 1000 rows, primary key (integer, numeric(11,3)). Update of primary key int column using mod() function, returns "3 row(s) updated."; expected "1000 row(s) updated." Subsequent select [count(*)|count(distinct colintk)] returns incorrect rowcount.

>>-- integer primary key column
>>-- expect: 1000
>>SELECT COUNT(DISTINCT colintk) FROM f00;

(EXPR)
--------------------

                1000

--- 1 row(s) selected.

>>prepare XX from UPDATE f00 SET colintk = MOD(colintk, 100);

--- SQL command prepared.
>>-- expect: 1000 row(s) updated.
>>-- but instead get 3 row(s) updated???
>>execute XX;

--- 3 row(s) updated.

>>-- expect: 100
>>-- but instead get 999
>>SELECT COUNT(DISTINCT colintk) FROM f00;

(EXPR)
--------------------

                 999

--- 1 row(s) selected.

>>-- expect: 1000
>>-- but instead get 999
>>SELECT COUNT(*) FROM f00;

(EXPR)
--------------------

                 999

--- 1 row(s) selected.

To reproduce, see contents of attachment, updcorrupt.tar:
      - obey file upd_pkey.sql or:

DROP TABLE f00;
CREATE TABLE f00(
               colintk int not null,
               colint int not null,
               collint largeint not null,
               colnum numeric(11,3) not null,
               primary key (colintk, colnum))
;

UPSERT WITH NO ROLLBACK INTO f00 SELECT
               c1+c2*10+c3*100+c4*1000+c5*10000,
               c1+c2*10+c3*100+c4*1000+c5*10000,
               (c1+c2*10+c3*100+c4*1000+c5*10000) + 549755813888,
               cast(c1+c2*10+c3*100+c4*1000+c5*10000 as numeric(11,3))
               from (values(1)) t
               transpose 0,1,2,3,4,5,6,7,8,9 as c1
               transpose 0,1,2,3,4,5,6,7,8,9 as c2
               transpose 0,1,2,3,4,5,6,7,8,9 as c3
               --transpose 0,1,2,3,4,5,6,7,8,9 as c4
               transpose 0 as c4
               --transpose 0,1,2,3,4,5,6,7,8,9 as c5
               transpose 0 as c5
;

UPDATE STATISTICS FOR TABLE f00 ON EVERY COLUMN;

-- integer primary key column
-- expect: 1000
SELECT COUNT(DISTINCT colintk) FROM f00;
prepare XX from UPDATE f00 SET colintk = MOD(colintk, 100);
-- expect: 1000 row(s) updated.
-- but instead get 3 row(s) updated???
execute XX;
-- expect: 100
-- but instead get 999
SELECT COUNT(DISTINCT colintk) FROM f00;
-- expect: 1000
-- but instead get 999
SELECT COUNT(*) FROM f00;

Attached updcorrupt.tar also contains logs generated without/with explain output (see upd_pkey.out[_wexp]).

[Julie Thai (julie-y-thai)]

Verified on traf_0601:
>>CREATE TABLE f00(
                 colintk int not null,
                 colint int not null,
                 collint largeint not null,
                 colnum numeric(11,3) not null,
                 primary key (colintk, colnum))
  ;
+>+>+>+>+>+>
--- SQL operation complete.
>>
>>UPSERT WITH NO ROLLBACK INTO f00 SELECT
                 c1+c2*10+c3*100+c4*1000+c5*10000,
                 c1+c2*10+c3*100+c4*1000+c5*10000,
                 (c1+c2*10+c3*100+c4*1000+c5*10000) + 549755813888,
                 cast(c1+c2*10+c3*100+c4*1000+c5*10000 as numeric(11,3))
                 from (values(1)) t
                 transpose 0,1,2,3,4,5,6,7,8,9 as c1
                 transpose 0,1,2,3,4,5,6,7,8,9 as c2
                 transpose 0,1,2,3,4,5,6,7,8,9 as c3
                 --transpose 0,1,2,3,4,5,6,7,8,9 as c4
                 transpose 0 as c4
                 --transpose 0,1,2,3,4,5,6,7,8,9 as c5
                 transpose 0 as c5
  ;
+>+>+>+>+>+>+>+>+>+>+>+>+>
--- 1000 row(s) inserted.
>>UPDATE STATISTICS FOR TABLE f00 ON EVERY COLUMN;

--- SQL operation complete.
>>SELECT COUNT(DISTINCT colintk) FROM f00;

(EXPR)
--------------------

                1000

--- 1 row(s) selected.
>>prepare XX from UPDATE f00 SET colintk = MOD(colintk, 100);

--- SQL command prepared.
>>execute XX;

--- 1000 row(s) updated.
>>SELECT COUNT(DISTINCT colintk) FROM f00;

(EXPR)
--------------------

                 100

--- 1 row(s) selected.
>>SELECT COUNT(*) FROM f00;

(EXPR)
--------------------

                1000

--- 1 row(s) selected.