| 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 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?
Description: The value of auto_increment field compared wrongly to NULL during the first select after insert - it is said to be NULL when it is not. In all subsequent select's it works as it should. See testcases 1, 3. Output of the 4th query (1st select) should be empty! There is a very strange thing: the condition "a is NULL and a is not NULL" is met during the first select! See testcases 2, 4. Output of the 4th query (1st select) should be empty! How to repeat: TESTCASE 1 ========== drop table if exists a; create table a ( a int auto_increment primary key ); insert into a (a) values (0); /* output of this query should be empty!!! */ select * from a where a is null; select * from a where a is null; TESTCASE 2 ========== drop table if exists a; create table a ( a int auto_increment primary key ); insert into a (a) values (0); /* output of this query should be empty!!! */ select * from a where a is not null and a is null; select * from a where a is not null and a is null; select * from a where a is null; select * from a where a is null; select * from a; TESTCASE 3 ========== drop table if exists a; create table a ( a int auto_increment primary key, b int ); insert into a (b) values (0); /* output of this query should be empty!!! */ select * from a where a is null; select * from a where a is null; select * from a; TESTCASE 4 ========== drop table if exists a; create table a ( a int auto_increment primary key, b int ); insert into a (b) values (0); /* output of this query should be empty!!! */ select * from a where a is null and a is not null; select * from a where a is null and a is not null; select * from a;