[
https://issues.apache.org/jira/browse/TRAFODION-249?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Atanu Mishra closed TRAFODION-249.
----------------------------------
Resolution: Fixed
Assignee: (was: Anoop Sharma)
Fix Version/s: 0.8 (pre-incubation)
> 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
> Priority: Critical
> Fix For: 0.8 (pre-incubation)
>
>
> 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)
