Bug #18922 Nothing should have a value and be null. That is crazy talk :)

• Submitted: 9 Apr 2006 17:40
• Modified: 9 Apr 2006 21:15
• Reporter: Brian Aker
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0
• OS: Linux (FC4 AMD64)
• CPU Architecture: Any

[9 Apr 2006 17:40] Brian Aker

Description:
  mysql> create table t3(n int not null primary key auto_increment, s
   varchar(30))
   ;
   Query OK, 0 rows affected (0.05 sec)

   mysql> insert into t3 (s) values ('first');
   Query OK, 1 row affected (0.03 sec)

   mysql> insert into t3 (s) values ('second');
   Query OK, 1 row affected (0.00 sec)

   mysql> select * from t3;
   +---+--------+
   | n | s      |
   +---+--------+
   | 1 | first  |
   | 2 | second |
   +---+--------+
   2 rows in set (0.00 sec)

   mysql> select * from t3 where n is null;
   +---+--------+
   | n | s      |
   +---+--------+
   | 2 | second |
   +---+--------+
   1 row in set (0.00 sec)

How to repeat:
  mysql> create table t3(n int not null primary key auto_increment, s
   varchar(30))
   ;
   Query OK, 0 rows affected (0.05 sec)

   mysql> insert into t3 (s) values ('first');
   Query OK, 1 row affected (0.03 sec)

   mysql> insert into t3 (s) values ('second');
   Query OK, 1 row affected (0.00 sec)

   mysql> select * from t3;
   +---+--------+
   | n | s      |
   +---+--------+
   | 1 | first  |
   | 2 | second |
   +---+--------+
   2 rows in set (0.00 sec)

   mysql> select * from t3 where n is null;
   +---+--------+
   | n | s      |
   +---+--------+
   | 2 | second |
   +---+--------+
   1 row in set (0.00 sec)

[9 Apr 2006 21:00] Peter Gulutzan

It is not clear what the complaint is, but the
results are compatible with documentation.
Please read the MySQL Reference Manual,
12.1.3. Comparison Functions and Operators:
"
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.
See Section 13.5.3, “SET Syntax”.
"

[9 Apr 2006 21:15] Brian Aker

Huh... I would have thought that it was a bug, I've never seen this feature before.

Pretty crazy I might add :(