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: | |
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
[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/