User Comments

be very carefult with this "feature":

Certain ODBC applications (including Delphi and Access) may have trouble obtaining the auto-increment value using the previous examples. In this case, try the following statement as an alternative:

SELECT * FROM tbl WHERE auto IS NULL;

for those of us that do not use Access or Delphi, this is a huge trap, because if you do this:

insert into table_a (id, name) values(null, 'jonny');
select * from table_a where id is null;

result
======
id name
1 jonny

this is WRONG!! there is no row with id=null, because column "id" is typically "not null and primary key" as well as "auto_increment", so "primary is null" should never return any ROW! It is just a screwed up feature to be compatible with Access/ODBC.

What is even more crazy is that this works ACROSS tables!

eg

insert into table_b (id, size) values(null, 'big');
select * from table_a where id is null;

result
======
id name
222 somename

where 222 is the id of the new record in "table_b", so this is retrieving what can only be described as a "random row".

This really caught us out because we then proceed to delete the retrieved row...so we were effectively deleting random rows out of the database.

of course you can turn off this madness by

set sql_auto_is_null = 0;

which I suggest EVERYONE who is not using Access/ODBC should do.

Respectfully suggest to mysql devs that this "feature" be OFF by default and documented heavily in connector/ODBC which is the only place it might be useful. For everyone else it is just dangerous.

Add your own comment.