Bug #10550 not null not working

• Submitted: 11 May 2005 12:16
• Modified: 11 May 2005 17:11
• Reporter: raj k
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: mysql 5.0.3
• OS: Windows (windows xp)
• CPU Architecture: Any

[11 May 2005 12:16] raj k

Description:
mysql> create table raa(no int(3) not null);
Query OK, 0 rows affected (0.16 sec)

mysql> desc raa;
+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| no    | int(3) | NO   |     |         |       |
+-------+--------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> alter table raa add column (name varchar(8));
Query OK, 0 rows affected (0.27 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc raa;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| no    | int(3)     | NO   |     |         |       |
| name  | varchar(8) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into raa (name) values('khgf');
Query OK, 1 row affected (0.06 sec)

mysql> select * from raa;
+----+------+
| no | name |
+----+------+
|  0 | khgf |
+----+------+
1 row in set (0.00 sec)

mysql> insert into raa (name) values('khgjhgf');
Query OK, 1 row affected (0.02 sec)

mysql> select * from raa;
+----+---------+
| no | name    |
+----+---------+
|  0 | khgf    |
|  0 | khgjhgf |
+----+---------+
2 rows in set (0.00 sec)

mysql> insert into raa (name) values('khgjuyugf');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from raa;
+----+----------+
| no | name     |
+----+----------+
|  0 | khgf     |
|  0 | khgjhgf  |
|  0 | khgjuyug |
+----+----------+
3 rows in set (0.00 sec)

How to repeat:
i created a table with not null field.when i insert into that table with out selecting that field  0 is inserting ,but it should not allow to insert,should get error while inserting

Suggested fix:
0 should not inserted into that field

[11 May 2005 17:11] MySQL Verification Team

Notice that 0 isn't NULL, then according the Manual:

http://dev.mysql.com/doc/mysql/en/create-table.html

.......

Prior to MySQL 5.0.2, if a column definition includes no explicit DEFAULT value, MySQL determines the default value as follows:

If the column can take NULL as a value, the column is defined with an explicit DEFAULT NULL clause.

If the column cannot take NULL as the value, MySQL defines the column with an explicit DEFAULT clause, using the implicit default value for the column data type. Implicit defaults are defined as follows:

    *    For numeric types other than those declared with the AUTO_INCREMENT attribute, the default is 0. For an AUTO_INCREMENT column, the default value is the next value in the sequence. 

......