Bug #41282 | auto_increment field inconsistent NULL treatment in first select after insert | ||
---|---|---|---|
Submitted: | 6 Dec 2008 20:58 | Modified: | 6 Dec 2008 22:53 |
Reporter: | Tomas Telensky | Email Updates: | |
Status: | Duplicate | Impact on me: | |
Category: | MySQL Server: General | Severity: | S2 (Serious) |
Version: | 5.0.67-log | OS: | Linux |
Assigned to: | CPU Architecture: | Any | |
Tags: | auto_increment, insert, null, SELECT |
[6 Dec 2008 20:58]
Tomas Telensky
[6 Dec 2008 22:07]
MySQL Verification Team
Thank you for the bug report. This is expected behavior please read: http://dev.mysql.com/doc/refman/5.0/en/server-session-variables.html#sysvar_sql_auto_is_nu... c:\dbs>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > " Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.0.74-nt-debug-log Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 5.0 > select @@sql_auto_is_null; +--------------------+ | @@sql_auto_is_null | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql 5.0 > use test Database changed mysql 5.0 > set @@sql_auto_is_null=0; Query OK, 0 rows affected (0.06 sec) mysql 5.0 > select @@sql_auto_is_null; +--------------------+ | @@sql_auto_is_null | +--------------------+ | 0 | +--------------------+ 1 row in set (0.00 sec) mysql 5.0 > create table a ( -> a int auto_increment primary key -> ); Query OK, 0 rows affected (0.14 sec) mysql 5.0 > mysql 5.0 > insert into a (a) values (0); Query OK, 1 row affected (0.06 sec) mysql 5.0 > mysql 5.0 > /* output of this query should be empty!!! */ mysql 5.0 > select * from a where a is null; Empty set (0.02 sec) mysql 5.0 > drop table a; Query OK, 0 rows affected (0.01 sec) mysql 5.0 > set @@sql_auto_is_null=1; Query OK, 0 rows affected (0.00 sec) mysql 5.0 > create table a ( -> a int auto_increment primary key -> ); Query OK, 0 rows affected (0.06 sec) mysql 5.0 > mysql 5.0 > insert into a (a) values (0); Query OK, 1 row affected (0.00 sec) mysql 5.0 > mysql 5.0 > /* output of this query should be empty!!! */ mysql 5.0 > select * from a where a is null; +---+ | a | +---+ | 1 | +---+ 1 row in set (0.05 sec) mysql 5.0 >
[6 Dec 2008 22:11]
MySQL Verification Team
Actually this is a duplicate of bug: http://bugs.mysql.com/bug.php?id=29859
[6 Dec 2008 22:53]
Tomas Telensky
This is really odd behavior! Is this odd behavior needed because of MS Access?