[ https://issues.apache.org/jira/browse/TRAFODION-249?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14708609#comment-14708609 ]
Atanu Mishra commented on TRAFODION-249: ---------------------------------------- Anoop Sharma (anoop-sharma) on 2014-05-30 Changed in trafodion: status: In Progress → Fix Committed Julie Thai (julie-y-thai) wrote on 2014-06-02: #2 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. Changed in trafodion: status: Fix Committed → Fix Released > LP Bug: 1316767 - update involving mod() func results in data corruption > ------------------------------------------------------------------------ > > Key: TRAFODION-249 > URL: https://issues.apache.org/jira/browse/TRAFODION-249 > Project: Apache Trafodion > Issue Type: Bug > Components: sql-cmp > Reporter: Julie Thai > Assignee: Anoop Sharma > Priority: Critical > > 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]). -- This message was sent by Atlassian JIRA (v6.3.4#6332)