MySQL Bugs: #10420: INTEGER columns without DEFAULT generate bad SQL

Bug #10420	INTEGER columns without DEFAULT generate bad SQL
Submitted:	6 May 2005 17:52	Modified:	20 May 2005 18:46
Reporter:	Joel Dare	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S2 (Serious)
Version:	1.1.9	OS:	Windows (Windows (XP Pro))
Assigned to:	Vladimir Kolesnikov	CPU Architecture:	Any

Description:
When you add a new INTEGER column to a table, and you DO NOT select a default, the resulting SQL contains errors.

Here is the resulting SQL:

CREATE TABLE `freedom`.`Test` (
  `mycol` INTEGER UNSIGNED NOT NULL DEFAULT  AUTO_INCREMENT,
  PRIMARY KEY(`mycol`)
)
TYPE = InnoDB;

Here is what it probably should be:

CREATE TABLE `freedom`.`Test` (
  `mycol` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY(`mycol`)
)
TYPE = InnoDB;

Or, maybe even:

CREATE TABLE `freedom`.`Test` (
  `mycol` INTEGER UNSIGNED NOT NULL DEFAULT '' AUTO_INCREMENT,
  PRIMARY KEY(`mycol`)
)
TYPE = InnoDB;

The work-around to this problem, is to specify a default value or to manually fix and execute the resulting query.

How to repeat:
1. Right-click on a database, and select "Create New Table".
2. Click on the blank record (where you add a new column) and press ENTER.
3. Type the column name 'mycol' and press TAB.
4. Type INT (INTEGER appears automatically) and press ENTER.
5. Hit Apply changes, and notice the error in the SQL syntax.

Suggested fix:
Remove the word "DEFAULT" from the query, if no default was specified.

A side effect of this bug is that you can type the text "NULL" as a default value to actually set the default value *to* NULL. The generated SQL refers to the column as:

  `mycol` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,

So, when fixing this bug, it would seem essential to provide a mechanism to set this collumn to NULL correctly.

Saying this, it would be handy if you could do the same whilst editing results in the results area. Or is this possible already? If it isn't, it shuold be!

I also see this problem in QB 1.1.9. It makes the table creation nearly useless as I use INTs as keys in my tables.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html