Bug #36847 Oracle NUMBER structure conversion error
Submitted: 21 May 2008 8:43 Modified: 11 May 2012 13:53
Reporter: Guillaume Bisch Email Updates:
Status: Won't fix Impact on me:
None 
Category:MySQL Migration Toolkit Severity:S2 (Serious)
Version:1.1.12, 1.1.17 OS:Windows
Assigned to: CPU Architecture:Any
Tags: SUPESC

[21 May 2008 8:43] Guillaume Bisch
Description:
MySQL Migration Tools misinterprets the NUMBER format when no parameters are specified 

When converting a table structure from Oracle (8i, 10g confirmed)  from :

CREATE TABLE nbTest (id INT,
nb NUMBER,
nb22_6 NUMBER(22,6), fl FLOAT);
INSERT INTO nbTest VALUES(1,3.1415925692,3.1415925692);

I expect the first NUMBER format to be interpreted as a float because it doesn't have any parameters. See http://www.ss64.com/orasyntax/datatypes.html

What MySQL produces is different: it interprets the NUMBER format as NUMBER(22,0) and produces a DECIMAL(22,0) instead of an DOUBLE.

CREATE TABLE `test`.`nbtest` (
  `id` DECIMAL(22, 0) NULL,
  `nb` DECIMAL(22, 0) NULL, --here DECIMAL instead of DOUBLE
  `nb22_6` DECIMAL(22, 6) NULL,
  `fl` DOUBLE NULL
);

How to repeat:
Use the CREATE Table command above.

Suggested fix:
I think it's possible to write a workaround in MigrationOracle.java. I believe the problems comes from reading a precision in the ALL_COLS metatable in Oracle.
[21 May 2008 14:43] Valeriy Kravchuk
Thank you for a problem report. Indeed, in Oracle you will get the following with NUMBER:

C:\oraclexe\app\oracle\product\10.2.0\server\BIN>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Beta on Wed May 21 17:18:17 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta

SQL> create table tn (c1 number);

Table created.

SQL> desc tn
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 C1                                                 NUMBER

SQL> insert into tn values (1.23456789);

1 row created.

SQL> select * from tn;

        C1
----------
1.23456789

while in MySQL with DECIMAL(22,0):

mysql> create table td(c1 decimal(22,0));
Query OK, 0 rows affected (0.34 sec)

mysql> insert into td values(1.23456789);
Query OK, 1 row affected (0.06 sec)

mysql> select * from td;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

I am not sure FLOAT is the best type still, but DECIMAL(22,0) surely will notwork as expected.
[9 Mar 2009 14:59] Harrison Fisk
From what I can tell, I think that DOUBLE would be the best thing to use for a NUMERIC without any scale or precision specified.

According to some documentation I found:

===
    NUMBER DATATYPE 
 
    The NUMBER datatype is used to store zero, positive and negative 
    fixed and floating point numbers with magnitudes between 1.0 x 
    10 
    digits of precision.  If you specify an arithmetic expression whose 
    value has a magnitude greater than or equal to 1.0 x 10 
    returns an error.  You can specify a fixed point number datatype 
    with this syntax: 
 
    NUMBER(p,s) 
 
where: 
 
p 
    is the precision, or the total number of digits. Oracle guarantees 
    the portability of numbers with precision ranging from 1 to 38. 
s 
    is the scale, or the number of digits to the right of the decimal 
    point. The scale can range from -84 to 127. 
 
You can also use one of these alternate forms: 
 
NUMBER 
    is a floating point number with precision 38.  Note that a scale 
    value is not applicable for floating point numbers. 
==
[10 Mar 2009 7:33] Valeriy Kravchuk
The bug is still repeatable with latest MT, 1.1.17. We get this table in MySQL:

DROP TABLE IF EXISTS `system`.`nbtest`;
CREATE TABLE `system`.`nbtest` (
  `id` DECIMAL(22, 0) NULL,
  `nb` DECIMAL(22, 0) NULL,
  `fl` DOUBLE NULL
)
ENGINE = INNODB;

for Oracle's:

SQL> desc nbTest;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER(38)
 NB                                                 NUMBER
 FL                                                 FLOAT(126)

created as:

C:\oraclexe\app\oracle\product\10.2.0\server\BIN>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.1.0 - Beta on Tue Mar 10 09:21:17 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Beta

SQL> create table nbTest (id int, nb number, fl float);
[19 Oct 2009 10:04] Susanne Ebrecht
Many thanks for writing a bug report. We are on the way to implement full functionality of MySQL Migration Tool into MySQL Workbench. We won't fix this anymore.

But we will take this bug report as necessary feature for the implemenation of Migration Toolkit into Workbench.

More informations about MySQL Workbench you will find here:

http://dev.mysql.com/workbench/