MySQL Bugs: #41934: BOOL in dropdown, but BOOLEAN as data type, Integer instead of INT

Bug #41934	BOOL in dropdown, but BOOLEAN as data type, Integer instead of INT
Submitted:	7 Jan 2009 22:07	Modified:	10 Jun 2009 10:55
Reporter:	Karsten Wutzke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	5.0.29	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	BOOL, boolean, column, dropdown, INT, INTEGER, type

Description:
When editing a column's type, the dropdown box shows "BOOL" when choosing the type, when selecting that, it becomes "BOOLEAN". Same holds true for integers: entering "INTEGER" in the colum type field changes to "INT" when pressing return.

How to repeat:
Set a column type to BOOL and BOOLEAN will appear (dropdown). Enter INTEGER type manually and INT will appear.

Suggested fix:
Either offer BOOLEAN in the dropdown or display BOOL in the model.

The same holds true for INT/INTEGER. It would be nice if the selected didn't change to some default type the MySQL tool developers selected. This of course means to offer both variants BOOL/BOOLEAN and INT/INTEGER in the dropdown box.

It would be very nice, because any forced change of INTEGER to INT or BOOL to BOOLEAN might conflict with the naming convention you have to use. In effect the MySQL tool uses BOOLEAN (the longer form) and INT (the shorter form). This might collide with some corporal naming conventions set, as they do with mine. Leave the user the choice of preferring INT or INTEGER and BOOL or BOOLEAN. In any case, you shouldn't mix BOOLEAN and INT... short and long forms.

tags changed

Many thanks for pointing this out.

According to SQL Standard the data types should be: Boolean and Integer (neither int nor bool).

I agree with you that this will occur conversion conflicts. It is annyoing that INTEGER will be converted to INT.

I don't want to turn this into a discussion, but I couldn't find the information that the long forms "INTEGER" and "BOOLEAN" are SQL standards (preferred words). Is there any such source on the internet?

BOOL, BOOLEAN, INTEGER and some others are aliases supported in the server.

http://dev.mysql.com/doc/refman/5.0/en/other-vendor-data-types.html

In WB 5.0, they are synonyms for their corresponding types, but in 5.1 they are implemented as user defined types with the same definition as in the server. Because of that, the automatic replacing/transformation of BOOL into BOOLEAN and others do not happen anymore in WB 5.1.

Ok. Then the next bug (docs bug?) would be as to why these keywords aren't listed here:

http://dev.mysql.com/doc/mysqld-version-reference/en/mysqld-version-reference-reservedword...

... at least not fully.

FLOAT4
FLOAT8
INT1
INT2
INT3
INT4
INT8
LONG
MIDDLEINT
NUMERIC

are in the list of reserved keywords. OTOH,

BOOL
BOOLEAN
CHARACTER VARYING(M)
FIXED
LONG VARBINARY
LONG VARCHAR

are *not* in that list. This is especially disturbing with BOOL and BOOLEAN, given that it is used so widely spread.

They may be the missing because they aren't supposed to be reserved keywords in the server. Then however, they would still be *non-reserved keywords* as they have a special meaning in certain contexts as seen by the parser. For BOOL and BOOLEAN however, I think it's really best to put them into the reserved keywords list. It's probably a good thing to do for the other missing types.

I tried a few things on the client. Indeed, the tables

FLOAT4, FLOAT8, ..., NUMERIC

can't be created, so they are already reserved keywords.

Tables BOOL, BOOLEAN and FIXED

*can* be created, so they are no reserved keywords (or rather non-reserved keywords, since they have some, even if only somewhat semantic, meaning in MySQL. Anyway, I think those should be put into the list of reserved keyword (IMHO).

The remaining table names CHARACTER VARYING(M), LONG VARBINARY and LONG VARCHAR produce errors of course. I don't know what to do with them... they don't qualify very well as keywords at all (parentheses and/or spaces).

Sorry if this has become a little OT.

One last thing: INT and INTEGER are reserved keywords in ISO/ANSI SQL from version 92 up to 2008, BOOLEAN is a reserved keyword since SQL-99 up to SQL-2008, BOOL is not a keyword at all in any version (not even non-reserved keyword).

If you ask me for a last opinion: I find it wrong to include the short form INT of INTEGER in the standard but leave the short form BOOL out of it. The ISO standard should include INT/INTEGER and BOOL/BOOLEAN alike.

So go for BOOL and BOOLEAN as MySQL reserved keywords.... strangely they're in MySQL Workbench already. ;-)

entered datatypes will now stay as specified in model.
fix will be in 5.1.13

An entry has been added to the 5.1.13 changelog:

When editing a column's type in the Columns tab of the Table Editor, the drop down listbox for selecting the data type displayed BOOL. However, once that had been selected the data type was displayed as BOOLEAN.

Further, if a column data type was set by entering INTEGER in the Datatype column, it then changed to INT on pressing return.