Bug #22573 ENUM Column doesn't error when insert or update with values out of list
Submitted: 21 Sep 2006 22:34 Modified: 23 Sep 2006 10:30
Reporter: lotusxxl Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.11-beta OS:Windows (Windows Server 2003)
Assigned to: CPU Architecture:Any
Tags: enum

[21 Sep 2006 22:34] lotusxxl
Description:
When insert or update in enum column with values out of list, mysql puts blank.

Mysql gives 1 warning but doesn't give any error.

How to repeat:
CREATE TABLE `a` (
  `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
  `val` enum('yes','no') DEFAULT 'no',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

mysql> INSERT INTO a (val) VALUES ('xxx');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from a;
+----+------+
| id | val  |
+----+------+
|  1 |      |
+----+------+
1 row in set (0.00 sec)
[22 Sep 2006 7:06] Valeriy Kravchuk
Thank you for a problem report. Please, send the results of:

select @@sql_mode;

With STRICT_TRANS_TABLES SQL mode set it should give you error, not warning.
[22 Sep 2006 17:05] lotusxxl
mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)
[22 Sep 2006 17:19] lotusxxl
mysql> SET sql_mode = 'strict_trans_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+---------------------+
| @@sql_mode          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.01 sec)

mysql> INSERT INTO a (val) VALUES ('xxx');
ERROR 1265 (01000): Data truncated for column 'val' at row 1

Seems to be right.

Thanks for all.
[23 Sep 2006 10:30] Valeriy Kravchuk
So, it is not a bug, but expected and documented behaviour.