[TRAFODION-249] LP Bug: 1316767 - update involving mod() func results in data corruption

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Fix Version/s: 0.8 (pre-incubation)
• Component/s: sql-cmp
• External issue URL: https://bugs.launchpad.net/bugs/1316767
• Assignee: Unassigned
• Reporter: Julie Thai
• Created: 22/Jul/15 18:14
• Updated: 24/Aug/15 00:55
• Resolved: 24/Aug/15 00:55

Description

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]).