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
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.1.11-beta
• OS: Windows (Windows Server 2003)
• 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.