Comment 2 for bug 1316767

Revision history for this message
Julie Thai (julie-y-thai) wrote :

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.