MySQL Bugs: #64990: New table in model with Double datatyp..Synchronize generates invalid syntax sql

Bug #64990	New table in model with Double datatyp..Synchronize generates invalid syntax sql
Submitted:	16 Apr 2012 1:25	Modified:	24 Jul 2012 0:40
Reporter:	David Oliver	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.38, 5.2.39	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
In MySql Workbench, create a new table in the model with a Double column.
Then synchronize that with a local database, the SQL that is generated will include a line like this:
`value` DOUBLE(11) NULL DEFAULT NULL ,

DOUBLE(11) is invalid SQL syntax and you get the typical error when you try to run it:
Error Code: 1064: You have an error in your SQL syntax; check the manual...

Workbench should be generating the line without parenthesis. It should just be
`value` DOUBLE NULL DEFAULT NULL

Putting parenthesis after the word DOUBLE is nonstandard, but even if it is to place parenthesis, it needs 2 parameters not one see: http://dev.mysql.com/doc/refman/5.5/en/floating-point-types.html

I did not specify a precision or a length of decimal places (the app doesn't support this) so I would expect it to generate a default double which should have double precision. I don't want the precision truncated down to just 11 that makes no sense. If I wanted that i would have specified a FLOAT at least which is smaller than a Double.

This is similar to Bug 61165 that was closed with 5.2.38 according to the comments. However, the issue I'm seeing occurs in both 5.2.38 and 5.2.39 (the latest)

Documentation says the following
>>For maximum portability, code requiring storage of approximate numeric data values should use FLOAT or DOUBLE PRECISION with no specification of precision or number of digits.

This is what I expect and want MySql workbench to do.

MySql workbench generated SQL should never generate invalid syntax SQL so this is a serious issue.

How to repeat:
In MySql Workbench, create a new table in the model with a Double column.
Then synchronize that with a local database, the SQL that is generated will include a line like this:
`value` DOUBLE(11) NULL DEFAULT NULL ,

This is invalid Sql syntax.

Suggested fix:
Instead of putting the number 11 in parenthesis after the DOUBLE just put the word DOUBLE without any parenthesis or precision/length specification that way it will do the expected default and will not generate a SQL syntax error.

Yes, the following code is generated:

Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER S' at line 3

CREATE  TABLE IF NOT EXISTS `mydb`.`table2` (
  `id` INT(11) NOT NULL ,
  `c1` DOUBLE(11) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1
COLLATE = latin1_swedish_ci

SQL script execution finished: statements: 3 succeeded, 1 failed

Proper code is generated during forward engineering though.

Bug #64920 was marked as a duplicate of this one.

This has been fixed as of the soon-to-be-released Workbench 5.2.41, and 
here's the changelog entry:

Creating and then synchronizing a table in a model with a "DOUBLE"
typed column would generate invalid SQL, thus generating an "Invalid SQL
syntax" error.