MySQL Bugs: #10256: MySQL accepts literal "0" (zero) string passed to ENUM NOT NULL field

Bug #10256	MySQL accepts literal "0" (zero) string passed to ENUM NOT NULL field
Submitted:	29 Apr 2005 3:01	Modified:	7 Sep 2006 0:36
Reporter:	Hal	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.0.4	OS:	Any (All)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	enum

Description:
(Please note that this is not the same issue as 10120.  Thank you.)

I can pass a literal string "0" to a field defined as ENUM NOT NULL.  mysql accepts it (although the value field is still left empty as before, see bug 10120, which was not a bug).  The server is now running with the TRADITIONAL mode option.  I don't think this was the intended behavior, but perhaps you can clear my understanding once again on this point.

(I had asked this question as an add-on comment to bug 10120, but it looks like it has already been closed).

How to repeat:
Run MySQL server with TRADITION mode option.

create table custdata (
custname varchar(20) primary key,
custtype enum ('S2','S1','S4') not null
);

insert into custdata (custname,custtype) values ("monroe","0");

Likewise for UPDATEs.

Suggested fix:
Since the field in question is declared with the NOT NULL attribute, it should not be allowed to have a NULL (empty) value.  Attempts to pass or set invalid values or NULL should be an error in the strict modes (I would think).

Right?  Or am I misinterpreting the meaning of "NOT NULL" -- does it only apply to values passed in from INSERTs and UPDATEs perhaps???  (Just occurs to me ...)

Sorry to keep bugging you.

switched category to MySQL Server

Thanks for your bug report.  This is not a QB bug but rather a bug in MySQL.

I was able to reproduce this bug under 5.0 and 5.1:

mysql> use test;
Database changed
mysql> create table custdata (
    -> custname varchar(20) primary key,
    -> custtype enum ('S2','S1','S4') not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> insert into custdata (custname,custtype) values ("monroe","0");
Query OK, 1 row affected (0.00 sec)

mysql> select * from custdata;
+----------+----------+
| custname | custtype |
+----------+----------+
| monroe   |          |
+----------+----------+
1 row in set (0.00 sec)

mysql>

I'm marking this as a documentation bug, because this is actually the intended behavior, despite what it says at http://dev.mysql.com/doc/refman/5.0/en/enum.html

When setting the value of an ENUM field, a numeric value (even if it is quoted) is used as the index into the possible values. And 0 (or "0" or '0') always means ''.

I disagree about this being a documentation defect.

What happens if the literal string "0" is one of the enumerated values for some application, along with, say, "1", "2", "4" and "G"?  In other words, we might have a drop down box indicating one of several specific states reflecting a real-world set of choices.

If we are saying that "0" is equivalent to NULL, then there is no way to set or select the enumerated choice "0" because it will be considered to be NULL.  This will be particularly problematic where the NOT NULL modifier is required for the field definition for an application.  The only way around this would be to prefix the choices with some neutral string or character, then trim it for display purposes, and re-prepend the selection for use in data queries, etc.

I think that is how I came to discover this problem.  That was over a year ago, so I don't recall what my application was for, sorry.  I changed the category back to data types; it is not a documentation problem.

The use of numeric strings is covered in the documentation. It is discouraged for exactly this reason. But if you do have an ENUM with numeric strings, they will be handled properly -- except for the confusion that will arise when you insert a numeric string that is not an ENUM value but is a valid index. So for ENUM("1", "2", "4", "G"), inserting "3" will result in a value of "4".

We are not saying that "0" is equal to NULL. It is equal to the 0th element of all ENUM fields: '' (an empty string).

The documentation problem is that the conversion of numeric strings to indexes is not documented, and is in fact documented wrongly. (They have been interpreted as indexes into the list of ENUM values since 2001, and it was a feature of ENUM handling that was deliberately added.)

My example was this:  ENUM("0","1","2","4","G");  (Yours is missing the "0" literal string).  Are you saying this is not a valid ENUM type in mysql?

I am not talking about selecting the 0th element of an enum (which is fine, if that need be required by an application); rather, I was talking about being able to have the literal string containing the numeral zero, not the number zero, as a value in an ENUM data type.  I do not see how that difference can be trivialized.

Perhaps what you mean is passing a bare 0 (not quoted) as an argument; in that context, I would probably agree that it could be valuated as NULL for that purpose.

At any rate, could you please address the issue of using the literal string (not number, or NULL) "0" as an enum data type value?

There is nothing to prevent having an ENUM value of "0", and it works exactly as intended. Numeric strings are only interpreted as an index if there is no matching string value.

mysql> CREATE TABLE t1 (a ENUM ("3","2","1","0") NOT NULL);
mysql> INSERT INTO t1 VALUES ("2"),("0"),(0),(3),(4);
mysql> SELECT a FROM t1;
+---+
| a |
+---+
| 2 |
| 0 |
|   |
| 1 |
| 0 |
+---+

This is a documentation issue. It is behaving exactly as intended, and how it should be documented.

At no point is 0, unquoted or not, stored  as NULL in an ENUM field.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Clarified that quoted number is inserted as is if it matches
an enumeration value, but treated as an index if it doesn't
match.  Added example to demonstrate.