MySQL Bugs: #14083: inserts not possible in 'strict mode' with autoincr PK

Bug #14083	inserts not possible in 'strict mode' with autoincr PK
Submitted:	17 Oct 2005 18:47	Modified:	18 Oct 2005 2:31
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.14	OS:	Windows (WinXP SP2)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
The docs say:

"If you are not running in strict mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that doesn't name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 13.1.5, “CREATE TABLE Syntax”. See also Section 1.8.6.2, “Constraints on Invalid Data”. 

If you want an INSERT statement to generate an error unless you explicitly specify values for all columns that do not have a default value, you should use STRICT mode. See Section 5.3.2, “The Server SQL Mode”. "

However it does not seem possible to create a new row of data when there is a autoincremented PK.

Is that expected behaviour or an unforeseen side-effect of the implementation of 'strict mode' ?

How to repeat:
Start server in 'strict mode' and

CREATE TABLE `tablename1` (                 
             `id` bigint(20) NOT NULL auto_increment,  
             `t` varchar(20) default NULL,             
             PRIMARY KEY  (`id`)                       
           ) ENGINE=MyISAM DEFAULT CHARSET=latin1

now: 

insert into `test`.`tablename1` ( `id`, `t` ) values (  '',  'a' );

fails with the infamous error "you got an error in yor SQL syntax ... blabla"

Suggested fix:
autoincremented values should be an 'implicit default value'.

Peter,

please test

insert into `test`.`tablename1` ( `id`, `t` ) values (  NULL,  'a' );

The empty sring '' is not the same as NULL.

Regards,

Heikki

Thanks!  That was it!

In 'non-strict-mode' the server substitues the NULL-value for an empty string with a numerical value.  But not in 'strict-mode'.  That would not be very strict either!