Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.6, 10.11.16
-
Fedora 43 (mariadb-10.11.16-2.fc43.x86_64)
-
Unexpected results
-
Is was previously possible to insert 0 or "0" into an ENUM where the ENUM defination didn't include these values. Now an Error WARN_DATA_TRUNCATED is returned in sql_mode=STRICT_ALL_TABLES.
Description
According to https://mariadb.com/docs/server/reference/data-types/string-data-types/enum an ENUM is internally stored as an integer, starting with the index 1. While the index-value of 0 is interpreted as an empty string.
If you are operating in strict mode and have the ENUM defined with NOT NULL, I would suspect that it should be never possible to have an empty string in there.
If you try to set an undefined value, or give an index of 0 or an index higher than the Enum-list includes, I correctly get an ERROR 1265 (01000): Data truncated.
However: if I use the string '0' I can indeed create the empty-string there, which seems wrong.
Here is my testcase:
|
|
MariaDB [test]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
|
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
|
| @@SQL_MODE | @@GLOBAL.SQL_MODE |
|
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
|
| STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
|
+-------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------+
|
1 row in set (0.000 sec)
|
|
|
MariaDB [test]> CREATE TABLE Demo (Testcolumn ENUM('Value1', 'Value2') NOT NULL DEFAULT 'Value1') engine=innodb;
|
Query OK, 0 rows affected (0.010 sec)
|
|
|
MariaDB [test]> INSERT INTO Demo SET TestColumn='Value1';
|
Query OK, 1 row affected (0.009 sec)
|
|
|
MariaDB [test]> INSERT INTO Demo SET TestColumn='Value2';
|
Query OK, 1 row affected (0.009 sec)
|
|
|
MariaDB [test]> INSERT INTO Demo SET TestColumn = 'Value3';
|
ERROR 1265 (01000): Data truncated for column 'Testcolumn' at row 1
|
|
|
MariaDB [test]> INSERT INTO Demo SET TestColumn = 1;
|
Query OK, 1 row affected (0.001 sec)
|
|
|
MariaDB [test]> INSERT INTO Demo SET TestColumn = 2;
|
Query OK, 1 row affected (0.008 sec)
|
|
|
MariaDB [test]> INSERT INTO Demo SET TestColumn = 3;
|
ERROR 1265 (01000): Data truncated for column 'Testcolumn' at row 1
|
MariaDB [test]> INSERT INTO Demo SET TestColumn = 0;
|
ERROR 1265 (01000): Data truncated for column 'Testcolumn' at row 1
|
MariaDB [test]> INSERT INTO Demo SET TestColumn = '3';
|
ERROR 1265 (01000): Data truncated for column 'Testcolumn' at row 1
|
MariaDB [test]> INSERT INTO Demo SET TestColumn = '0';
|
Query OK, 1 row affected (0.008 sec)
|
|
|
MariaDB [test]> SELECT * FROM Demo;
|
+------------+
|
| Testcolumn |
|
+------------+
|
| Value1 |
|
| Value2 |
|
| Value1 |
|
| Value2 |
|
| |
|
+------------+
|
5 rows in set (0.000 sec)
|
|
|
MariaDB [test]>
|