MySQL Bugs: #31032: AUTO_INCREMENT not consistently rejected for non-integer data types

Bug #31032	AUTO_INCREMENT not consistently rejected for non-integer data types
Submitted:	14 Sep 2007 15:11	Modified:	5 Nov 2007 16:13
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1, 5.0, 5.1, ...	OS:	Any
Assigned to:	Sergei Glukhov	CPU Architecture:	Any

Description:
The AUTO_INCREMENT column attribute is intended only for
integer data types. It is correctly rejected for non-numeric
types, and for DECIMAL. It is not rejected for FLOAT or
DOUBLE, but it should be.

How to repeat:
Test script:

DROP TABLE IF EXISTS t;
CREATE TABLE t
  (i CHAR(5) AUTO_INCREMENT NOT NULL PRIMARY KEY);
SHOW CREATE TABLE t\G

DROP TABLE IF EXISTS t;
CREATE TABLE t
  (i DECIMAL AUTO_INCREMENT NOT NULL PRIMARY KEY);
SHOW CREATE TABLE t\G

DROP TABLE IF EXISTS t;
CREATE TABLE t
  (i FLOAT AUTO_INCREMENT NOT NULL PRIMARY KEY);
SHOW CREATE TABLE t\G

DROP TABLE IF EXISTS t;
CREATE TABLE t
  (i DOUBLE AUTO_INCREMENT NOT NULL PRIMARY KEY);
SHOW CREATE TABLE t\G

Result of running script (using 5.0.50):

mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE t
    ->   (i CHAR(5) AUTO_INCREMENT NOT NULL PRIMARY KEY);
ERROR 1063 (42000): Incorrect column specifier for column 'i'
mysql> SHOW CREATE TABLE t\G
ERROR 1146 (42S02): Table 'test.t' doesn't exist
mysql> 
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t
    ->   (i DECIMAL AUTO_INCREMENT NOT NULL PRIMARY KEY);
ERROR 1063 (42000): Incorrect column specifier for column 'i'
mysql> SHOW CREATE TABLE t\G
ERROR 1146 (42S02): Table 'test.t' doesn't exist
mysql> 
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE t
    ->   (i FLOAT AUTO_INCREMENT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` float NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> 
mysql> DROP TABLE IF EXISTS t;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t
    ->   (i DOUBLE AUTO_INCREMENT NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.35 sec)

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `i` double NOT NULL auto_increment,
  PRIMARY KEY  (`i`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

Thank you for a bug report. Verified just as described. This is inconsistency (DECIMAL vs. FLOAT/DOUBLE) and, thus, a bug.

I am new to mysql.And I wanted to ipmlement auto_increment for a column  in a table. I took varchar datatype. but it's failing. But when took as integer its working fine. 
here is the command (failed) :
mysql> create table dataset (DataSetId varchar(10) not null auto_increment primary key,DataSetName varchar(100) not null,DataSetLocation varchar(200) not null);
ERROR 1063 (42000): Incorrect column specifier for column 'DataSetId'

command (passed): 
mysql> create table dataset (DataSetId integer(10) not null auto_increment primary key,DataSetName varchar(100) not null,DataSetLocation varchar(200) not null);
Query OK, 0 rows affected (0.01 sec)

mysql> describe dataset;
+-----------------+--------------+------+-----+---------+----------------+
| Field           | Type         | Null | Key | Default | Extra          |
+-----------------+--------------+------+-----+---------+----------------+
| DataSetId       | int(10)      | NO   | PRI | NULL    | auto_increment |
| DataSetName     | varchar(100) | NO   |     |         |                |
| DataSetLocation | varchar(200) | NO   |     |         |                |
+-----------------+--------------+------+-----+---------+----------------+

Can anyone please look into this matter. 

Thanks,
Praveen Kumar Mall
India

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/36624

ChangeSet@1.2547, 2007-10-30 14:09:19+04:00, gluh@mysql.com +3 -0
  Bug#31032 AUTO_INCREMENT not consistently rejected for non-integer data types
  removed AUTO_INCREMENT_FLAG setting for float&double fields
  (according to manual: http://dev.mysql.com/doc/refman/5.0/en/create-table.html)

It has been decided that because AUTO_INCREMENT currently works without problem for FLOAT/DOUBLE, that behavior will not be changed. I have updated the manual to indicate that AUTO_INCREMENT can be used for integer _or_ floating-point types.