Description:
Oracle NUMBER(19...38) columns can't be converted to MySQL properly.
and, NUMBER(x, y) loses its precision.
How to repeat:
I tested with Oracle 9.2.0.6 and MySQL 5.0.10.
-- Test table for Oracle
create table test007_number (
col1 number(1),
col2 number(2),
col3 number(3),
col4 number(4),
col5 number(5),
col6 number(6),
col7 number(7),
col8 number(8),
col9 number(9),
col10 number(10),
col11 number(11),
col12 number(12),
col13 number(13),
col14 number(14),
col15 number(15),
col16 number(16),
col17 number(17),
col18 number(18),
col19 number(19),
col20 number(20),
col21 number(21),
col22 number(22),
col23 number(23),
col24 number(24),
col25 number(25),
col26 number(26),
col27 number(27),
col28 number(28),
col29 number(29),
col30 number(30),
col31 number(31),
col32 number(32),
col33 number(33),
col34 number(34),
col35 number(35),
col36 number(36),
col37 number(37),
col38 number(38),
fix1 number(38,1),
fix2 number(38,2),
fix3 number(38,3),
fix4 number(38,4),
fix5 number(38,5),
fix6 number(38,6),
fix7 number(38,7),
fix8 number(38,8),
fix9 number(38,9),
fix10 number(38,10),
fix11 number(38,11),
fix12 number(38,12),
fix13 number(38,13),
fix14 number(38,14),
fix15 number(38,15),
fix16 number(38,16),
fix17 number(38,17),
fix18 number(38,18),
fix19 number(38,19),
fix20 number(38,20),
fix21 number(38,21),
fix22 number(38,22),
fix23 number(38,23),
fix24 number(38,24),
fix25 number(38,25),
fix26 number(38,26),
fix27 number(38,27),
fix28 number(38,28),
fix29 number(38,29),
fix30 number(38,30)
)
/
insert into test007_number values (
9,
99,
999,
9999,
99999,
999999,
9999999,
99999999,
999999999,
9999999999,
99999999999,
999999999999,
9999999999999,
99999999999999,
999999999999999,
9999999999999999,
99999999999999999,
999999999999999999,
9999999999999999999,
99999999999999999999,
999999999999999999999,
9999999999999999999999,
99999999999999999999999,
999999999999999999999999,
9999999999999999999999999,
99999999999999999999999999,
999999999999999999999999999,
9999999999999999999999999999,
99999999999999999999999999999,
999999999999999999999999999999,
9999999999999999999999999999999,
99999999999999999999999999999999,
999999999999999999999999999999999,
9999999999999999999999999999999999,
99999999999999999999999999999999999,
999999999999999999999999999999999999,
9999999999999999999999999999999999999,
99999999999999999999999999999999999999,
9999999999999999999999999999999999999.9,
999999999999999999999999999999999999.99,
99999999999999999999999999999999999.999,
9999999999999999999999999999999999.9999,
999999999999999999999999999999999.99999,
99999999999999999999999999999999.999999,
9999999999999999999999999999999.9999999,
999999999999999999999999999999.99999999,
99999999999999999999999999999.999999999,
9999999999999999999999999999.9999999999,
999999999999999999999999999.99999999999,
99999999999999999999999999.999999999999,
9999999999999999999999999.9999999999999,
999999999999999999999999.99999999999999,
99999999999999999999999.999999999999999,
9999999999999999999999.9999999999999999,
999999999999999999999.99999999999999999,
99999999999999999999.999999999999999999,
9999999999999999999.9999999999999999999,
999999999999999999.99999999999999999999,
99999999999999999.999999999999999999999,
9999999999999999.9999999999999999999999,
999999999999999.99999999999999999999999,
99999999999999.999999999999999999999999,
9999999999999.9999999999999999999999999,
999999999999.99999999999999999999999999,
99999999999.999999999999999999999999999,
9999999999.9999999999999999999999999999,
999999999.99999999999999999999999999999,
99999999.999999999999999999999999999999
)
/
commit
/
-- Generated Creates.sql
CREATE TABLE `mi`.`test007_number` (
...
`col18` BIGINT(18) NULL,
`col19` BIGINT(19) NULL, -- shuld be DECIMAL(19)
`col20` BIGINT(20) NULL, -- shuld be DECIMAL(20)
`col21` BIGINT(21) NULL, -- shuld be DECIMAL(21)
`col22` INT NULL, -- INT? shuld be DECIMAL(22)
`col23` BIGINT(23) NULL, -- shuld be DECIMAL(23)
`col24` BIGINT(24) NULL, -- shuld be DECIMAL(24)
`col25` BIGINT(25) NULL, -- shuld be DECIMAL(25)
`col26` BIGINT(26) NULL, -- shuld be DECIMAL(26)
`col27` BIGINT(27) NULL, -- shuld be DECIMAL(27)
`col28` BIGINT(28) NULL, -- shuld be DECIMAL(28)
`col29` BIGINT(29) NULL, -- shuld be DECIMAL(29)
`col30` BIGINT(30) NULL, -- shuld be DECIMAL(30)
`col31` BIGINT(31) NULL, -- shuld be DECIMAL(31)
`col32` BIGINT(32) NULL, -- shuld be DECIMAL(32)
`col33` BIGINT(33) NULL, -- shuld be DECIMAL(33)
`col34` BIGINT(34) NULL, -- shuld be DECIMAL(34)
`col35` BIGINT(35) NULL, -- shuld be DECIMAL(35)
`col36` BIGINT(36) NULL, -- shuld be DECIMAL(36)
`col37` BIGINT(37) NULL, -- shuld be DECIMAL(37)
`col38` BIGINT(38) NULL, -- shuld be DECIMAL(38)
`fix1` DECIMAL(38, 1) NULL,
...
-- Converted Data
root:mi> select * from test007_number\G
*************************** 1. row ***************************
col1: 9
col2: 99
col3: 999
col4: 9999
col5: 99999
col6: 999999
col7: 9999999
col8: 99999999
col9: 999999999
col10: 9999999999
col11: 99999999999
col12: 999999999999
col13: 9999999999999
col14: 99999999999999
col15: 999999999999999
col16: 9999999999999999
col17: 99999999999999999
col18: 999999999999999999
col19: -8446744073709551617 -- overflow
col20: 9223372036854775807 -- overflow
col21: 9223372036854775807 -- overflow
col22: 2147483647 -- overflow
col23: 9223372036854775807 -- overflow
col24: 9223372036854775807 -- overflow
col25: 9223372036854775807 -- overflow
col26: 9223372036854775807 -- overflow
col27: 9223372036854775807 -- overflow
col28: 9223372036854775807 -- overflow
col29: 9223372036854775807 -- overflow
col30: 9223372036854775807 -- overflow
col31: 9223372036854775807 -- overflow
col32: 9223372036854775807 -- overflow
col33: 9223372036854775807 -- overflow
col34: 9223372036854775807 -- overflow
col35: 9223372036854775807 -- overflow
col36: 9223372036854775807 -- overflow
col37: 9223372036854775807 -- overflow
col38: 9223372036854775807 -- overflow
fix1: 9999999999999999538762658202121142272.0 -- incorrect
fix2: 999999999999999999999999999999999999.99 -- incorrect
fix3: 99999999999999996863366107917975552.000 -- incorrect
fix4: 9999999999999999455752309870428160.0000 -- incorrect
fix5: 999999999999999945575230987042816.00000 -- incorrect
fix6: 99999999999999999999999999999999.999999
fix7: 9999999999999999635896294965248.0000000 -- incorrect
fix8: 999999999999999999999999999999.99999999
fix9: 99999999999999991433150857216.000000000 -- incorrect
fix10: 9999999999999999583119736832.0000000000 -- incorrect
fix11: 999999999999999999999999999.99999999999
fix12: 99999999999999999999999999.999999999999
fix13: 9999999999999999999999999.9999999999999
fix14: 999999999999999983222784.00000000000000 -- incorrect
fix15: 99999999999999999999999.999999999999999
fix16: 9999999999999999999999.9999999999999999
fix17: 999999999999999999999.99999999999999999
fix18: 99999999999999999999.999999999999999999
fix19: 9999999999999999999.9999999999999999999
fix20: 999999999999999999.99999999999999999999
fix21: 99999999999999999.999999999999999999999
fix22: 9999999999999999.9999999999999999999999
fix23: 999999999999999.99999999999999999999999
fix24: 99999999999999.999999999999999999999999
fix25: 9999999999999.9999999999999999999999999
fix26: 999999999999.99999999999999999999999999
fix27: 99999999999.999999999999999999999999999
fix28: 9999999999.9999999999999999999999999999
fix29: 999999999.99999999999999999999999999999
fix30: 99999999.999999999999999999999999999999
1 row in set (0.00 sec)
Suggested fix:
1) NUMBER(19...38) shuld be converted to DECIMAL, not to BIGINT.
2) DECIMAL(x, y) values shuld keep their precision.