| 2014-05-06 19:46:21 |
Julie Thai |
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.
Also, tested update of non-key column involving mod() function and encountered discrepancies too. Following the update involving mod() function, subsequent select count(distinct colint) returns incorrect count (value varies).
>>-- integer non-key column
>>-- expected: 1000
>>SELECT COUNT(DISTINCT colint) FROM f01;
(EXPR)
--------------------
1000
--- 1 row(s) selected.
>>prepare XX from UPDATE f01 SET colint = MOD(colint, 100);
--- SQL command prepared.
>>-- expected: 1000 row(s) updated.
>>execute XX;
--- 1000 row(s) updated.
>>-- expected: 100
>>-- but got 966(varies) instead???
>>SELECT COUNT(DISTINCT colint) FROM f01;
(EXPR)
--------------------
845
--- 1 row(s) selected.
>>SELECT COUNT(*) FROM f01;
(EXPR)
--------------------
1000
--- 1 row(s) selected.
To reproduce, see contents of attachment, updcorrupt.tar:
- obey file upd_pkey.sql to reproduce discrepancies seen updating a key column.
- obey file upd_nkey.sql to reproduce discrepancies seen updating a non-key column.
updcorrupt.tar also contains logs generated without/with explain output (see upd_pkey.out[_wexp] and upd_nkey.out[_wexp]. |
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]). |
|
