[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    mysql: Field type enum allows empty strings ???
From:       Michael Widenius <monty () monty ! pp ! sci ! fi>
Date:       1999-01-30 20:50:09
[Download RAW message or body]

>>>>> "Peter" == Peter Andreasen <pandr@mail100.image.dk> writes:

<cut>

Peter>   While the manual says:

Peter>     An enumeration. A string object that can have only one
Peter> 	value, chosen from the list of values 'value1', 'value2',...
Peter> 	(or NULL).

Peter>   in reality enum's accept the empty string ''. Furthermore, if
Peter>   an invalid value is assigned to an enum field, no warning or
Peter>   error is given, but instead the field takes the empty string ''
Peter>   as value.

Enum doesn't accept empty strings;  It however sets all wrong enum to
enum value 0 and displays the empty string for those.

>> How-To-Repeat:
mysql> create table a ( b enum ('x') );
Peter> 	Query OK, 0 rows affected (0.04 sec)

mysql> insert into a values ('y');      
Peter> 	Query OK, 1 row affected (0.00 sec)

mysql> insert into a values (''); 
Peter> 	Query OK, 1 row affected (0.00 sec)

mysql> select * from a;
Peter> 	+------+
Peter> 	| b    |
Peter> 	+------+
Peter> 	|      |
Peter> 	|      |
Peter> 	+------+
Peter> 	2 rows in set (0.00 sec)

mysql> 
>> Fix:
Peter> 	No workaround or fix is known (to me, at least!)

The above is ok, as MySQL doesn't return an 'info' row for simple
inserts to speed up the insert protocol.

You can verify this with:

mysql> create table a ( b enum ('x') );
Query OK, 0 rows affected (0.12 sec)
mysql> insert into a values ('b'),('');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 2

mysql> select b+0 from a;
+------+
| b+0  |
+------+
|    0 |
|    0 |
+------+
2 rows in set (0.00 sec)

Compare this with:

mysql> drop table a; create table a ( b enum('x',''));
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> insert into a values ('b'),('');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 1

mysql> select b+0 from a;
+------+
| b+0  |
+------+
|    0 |
|    2 |
+------+
2 rows in set (0.00 sec)

The 'bug' is that single value insert doesn't return warnings
information, but this is not done because of speed reasons.  It would
however be quite easy to add an option so that you will get warnings
even for this.

As always;  It's the client that should check the values;  MySQL will
only store the value as good as it's capable of!

Regards,
Monty
-----------------------------------------------------------
Send a mail to mdomo@tcx.se with
unsubscribe mysql your@subscription.address
in the body of the message to unsubscribe from this list.

[prev in list] [next in list] [prev in thread] [next in thread]