MySQL Bugs: #67743: INSERT followed by SELECT on fields not null having a default value

Bug #67743	INSERT followed by SELECT on fields not null having a default value
Submitted:	28 Nov 2012 14:42	Modified:	28 Nov 2012 16:45
Reporter:	BRUNO AUGIER	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.32	OS:	Linux (Debian)
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, count, insert, NOT NULL, SELECT

Description:
INSERT INTO `the_table`(ID) values (null);
SELECT COUNT(*) NB FROM `the_table` WHERE `ID` IS NULL;

ID is configured not null, primary key and auto_increment

the above statement may randomly return 0 or 1 even if field ID is set to be NOT NULL 

It seems to be a "delay" problem when inserting before the default value (in this case the next autoincrement value) is set.

the field ID seems to be visible as null for some microsecond after insert

How to repeat:
CREATE TABLE `the_table` 
(
  `ID` int(5) NOT NULL auto_increment,
  `NEWS_ID` int(5) unsigned zerofill NOT NULL default '00000',
  `TYPE` varchar(255) default NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

execute in a row :

INSERT INTO `the_table`(ID) values (null);
SELECT COUNT(*) NB FROM `the_table` WHERE `ID`  IS NULL ;
=> return 1

wait 2 seconds an execute last query again

SELECT COUNT(*) NB FROM `the_table` WHERE `ID`  IS NULL ;
=> return 0

Perhaps you also hit a race condition bug if the timing is really important?

e.g.  http://bugs.mysql.com/bug.php?id=37521

So, please upgrade to 5.5.28 and check if your tests still fail.. 5.0.32 is truly too old for consideration, and full of bugs too.

Thanks for your fast answers!

I think your first answer was right, it seems related to this strange feature "sql_auto_is_null", it should be considered as "not a bug"

 http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sql_auto_is_nul...

Thank you for the feedback.

Closed as "Not a Bug".