MySQL Bugs: #38154: Synchronize fails to equate BOOLEAN fields with TINYINT(1)

Bug #38154	Synchronize fails to equate BOOLEAN fields with TINYINT(1)
Submitted:	15 Jul 2008 22:13	Modified:	19 Oct 2009 10:46
Reporter:	Ken Ismert	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	boolean, CHECKED, synchronize, workbench

Description:
Synchronize fails to equate BOOLEAN fields with TINYINT(1).

This makes Synchronize flag BOOLEAN fields repeatedly as needing to be changed, when in fact the fields are identical.

How to repeat:
Create a table with a BOOLEAN/TINYINT field:

Step 1:
-------
DROP TABLE IF EXISTS `testbool`;
CREATE TABLE `testbool` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `testbool` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Alternately, make the table in MySQL Administrator. (Note that this program correctly forces BOOLEAN to TINYINT(1) when the table is saved.

Step 2:
-------
a) Reverse Engineer table into Workbench.
b) Edit table and change type of field `testbool` to BOOLEAN
c) Synchronize the table. Note that the field is flagged as different.
d) Repeated synchronizations will always flag the field for change

Suggested fix:
MySQL specifies BOOLEAN as a synonym for TINYINT.

Therefore, Workbench should equate BOOLEAN to TINYINT, and not flag the field for change.

BOOLEAN should be treated as equal to TINYINT(1), TINYINT(2), TINYINT(3) and TINYINT(4).

Thank you for the report.

Verified as described.

The problem is too many unknowns.

One solution would be that the user will get a question during sync if he wants to have boolean or tinyint.

I second this problem.
Since Workbench 5.1 SQL-Files created from Workbench that use the BOOLEAN (pseudo-type) already generate code as TINYINT(1). When afterwards making an update to the schema, and taking the last output from Workbench as input to compare, all columns with BOOLEAN want to be changed to TINYINT(1).

There are no unknowns. Everything needed to solve the problem is known:

1. BOOLEAN == TINYINT
2. The user chooses the alias BOOLEAN in their schema

The fix is display the preferred alias to the user, but regard the underlying types as identical, and do not flag for change. 

In general, all type aliases should map to their base type, and be regarded as identical for field comparison purposes.

You already have this information, as evidenced when you correctly mapped BOOLEAN to TINYINT in the first place. 

All you have to do is use this same function to map aliases to base types when comparing for field changes.

This bug here depends on bug #8485.

I will set this as duplicate of bug #8485.