MySQL Bugs: #12749: Oracle NUMBER can't be converted properly

Bug #12749	Oracle NUMBER can't be converted properly
Submitted:	23 Aug 2005 9:15	Modified:	23 Aug 2005 10:39
Reporter:	Sadao Hiratsuka (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Migration Toolkit	Severity:	S2 (Serious)
Version:	1.0.13	OS:	Linux (Linux)
Assigned to:	Michael G. Zinner	CPU Architecture:	Any

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.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

Sadao, thanks for your excellent testcase. This bug is now fixed in the source repository and will be in the next release 1.0.14 that will be online tomorrow.

But there is a problem remaining, which turns out to be a server bug. I filed a report for it http://bugs.mysql.com/bug.php?id=12750

The new release is now uploaded. Until the mirrors catch up it can be downloaded from

ftp://ftp.mysql.com/pub/mysql/download/mysql-migration-toolkit-1.0.14-rc-win32.msi
ftp://ftp.mysql.com/pub/mysql/download/mysql-migration-toolkit-1.0.14-rc-win32.msi.md5

Mike

how did you run the migration toolkit in linux?

There is no GUI based Migration Tool for Linux at this time, but
there is a text based version which will be shipped soon
with the GUI tools bundle, release 3 or if it doesn't make it, on release 4.