MySQL Bugs: #43939: After inserting record, next query with "WHERE id IS NULL" returns it.

Bug #43939	After inserting record, next query with "WHERE id IS NULL" returns it.
Submitted:	29 Mar 2009 12:51	Modified:	30 Mar 2009 5:51
Reporter:	Vadim Fint	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.72-log	OS:	Linux (also tried 5.0.70-log)
Assigned to:		CPU Architecture:	Any

Description:
I'm feeling idiot, but I dont understand what is going on here (: After inserting new record - "select ANY from table where PRIMARY_KEY_COL IS NULL" returns it. Next (same) query - dont.

Tried myisam and innodb. The same occurs from python/c api.

How to repeat:
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb;
Database changed
mysql> create table test (id int not null primary key auto_increment) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values();
Query OK, 1 row affected (0.00 sec)

mysql> select * from test where id is null;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> select * from test where id is null;
Empty set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> create table test (id int not null primary key auto_increment) engine=innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test values();
Query OK, 1 row affected (0.01 sec)

mysql> select * from test where id is null;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.00 sec)

mysql> select * from test where id is null;
Empty set (0.00 sec)

Oh.. seems to be duplicate of #41371. Sorry.

Duplicate of bug #41371.

Actually, this may be NOT a duplicate and NOT a bug formally, if you have sql_auto_is_null != 0. Please, send the results of:

show session variables like 'sql_auto%';

Read http://dev.mysql.com/doc/refman/5.1/en/comparison-operators.html for the details:

"To work well with ODBC programs, MySQL supports the following extra features when using IS NULL:

    - You can find the row that contains the most recent AUTO_INCREMENT value by issuing a statement of the following form immediately after generating the value:

      SELECT * FROM tbl_name WHERE auto_col IS NULL

      This behavior can be disabled by setting sql_auto_is_null = 0."

Yes, "set session sql_auto_is_null = 0" did the right thing. Althought, "show session variables like 'sql_auto%';" didnt show anything. I tried to set sql_auto_is_null on and off - in any way show statement does not show it. Why?

I'm an idiot again.

Manual clearly states that session variables mostly dont displayed via "show variables" statement, but their value can be obtained via SELECT:

SELECT @@sql_auto_is_null;

Thanks for answer a lot!!

100% not a bug.