MySQL Bugs: #57545: Forward engineering is quoting boolean values causing incorrect cast values

Bug #57545	Forward engineering is quoting boolean values causing incorrect cast values
Submitted:	19 Oct 2010 2:28	Modified:	24 Nov 2010 17:39
Reporter:	Jonathon Coombes	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: Modeling	Severity:	S2 (Serious)
Version:	5.2.29	OS:	Any
Assigned to:	Alexander Musienko	CPU Architecture:	Any
Tags:	forward engineer, guitools, modelling, regression, workbench

Description:
When a table contains the logical BOOLEAN type for a field it is converted to TINYINT(1) as is expected.

However, the values that are being supplied for any inserts are being quoted such as:

INSERT INTO mytable (id,flag) VALUES (NULL,'TRUE');

The issue is that MySQL will attempt to CAST() the string to the correct type (TINYINT) and since it does not start with any verifiable numeric digit, will cast it to a value of zero, which is always FALSE in BOOLEAN terms.

How to repeat:
1. Open Workbench 5.2.29 and create a new modelling diagram

2. Add a new table and define one of the fields to be of type BOOLEAN

3. Add inserts to the table using BOOLEAN values of TRUE or FALSE

4. Forward engineer the table and check the SQL dump to see the boolean values are quoted and hence will be incorrectly cast.

Suggested fix:
Don't quote the field values

Bug #57399 is related

bug #58045 is also related

fix confirmed in repository

An entry has been added to the 5.2.31 changelog:

The forward engineering process generated SQL code that quoted boolean values, causing incorrect cast values. When a table contained the logical BOOLEAN type for a field it was converted to TINYINT(1) as expected. However, the values that were supplied for any inserts were quoted, as in the following example:

INSERT INTO mytable (id,flag) VALUES (NULL,'TRUE');