Re: bzr commit into mysql-5.1-bugteam branch (Sergey.Glukhov:2828) Bug#33717

[Peter Gulutzan]

Hi Gluh,

Sergey Glukhov wrote:
> Hi Peter!
>
> Could you comment this ENUM field vagueness(see below)?
>
> Sergei Golubchik wrote:
>> Hi, Sergey!
>>
>> On Feb 27, Sergey Glukhov wrote:
>>> #At file:///home/gluh/MySQL/mysql-5.1-bug-33717/ based on
>>> revid:kgeorge@stripped
>>>
>>>  2828 Sergey Glukhov    2009-02-27
>>>       Bug#33717 INSERT...(default) fails for enum. Crashes CSV
>>> tables, loads spaces for MyISAM
>>>       Table corruption happens during table reading in
>>> ha_tina::find_current_row() func.
>>>       Field::store() method returns error(true) if stored value is 0.
>>>       The fix:
>>>       added special case for enum type which correctly processes 0
>>> value.
>>>       Additional fix:
>>>       INSERT...(default) and INSERT...() have the same behaviour now.
>>>
>>> === modified file 'mysql-test/r/csv.result'
>>> --- a/mysql-test/r/csv.result    2009-01-23 12:22:05 +0000
>>> +++ b/mysql-test/r/csv.result    2009-02-27 10:42:53 +0000
>>> @@ -5394,17 +5394,28 @@ select * from t1;
>>>  ERROR HY000: File 'MYSQLD_DATADIR/test/t1.CSV' not found (Errcode: 2)
>>>  unlock tables;
>>>  drop table t1;
>>> +CREATE TABLE t1 (e enum('foo','bar') NOT NULL) ENGINE = CSV;
>>> +INSERT INTO t1 VALUES();
>>>  Warnings:
>>> +Warning    1364    Field 'e' doesn't have a default value
>>> +INSERT INTO t1 VALUES(default);
>>> +Warnings:
>>> +Warning    1364    Field 'e' doesn't have a default value
>>> +INSERT INTO t1 VALUES(0);
>>> +Warnings:
>>> +Warning    1265    Data truncated for column 'e' at row 1
>>> +INSERT INTO t1 VALUES(3);
>>> +Warnings:
>>> +Warning    1265    Data truncated for column 'e' at row 1
>>> +INSERT INTO t1 VALUES(-1);
>>> +Warnings:
>>> +Warning    1265    Data truncated for column 'e' at row 1
>>> +SELECT * FROM t1;
>>> +e
>>> +foo
>>> +foo
>>> +
>>> +
>>> +
>>> +DROP TABLE t1;
>>>  End of 5.1 tests
>>
>> I'm not sure we can do that :(
>> Although I agree that this makes ENUM to be more consistent with other
>> field types, the manual says explicitly:
>>
>>    * If an *Note `ENUM': enum. column is declared to allow `NULL', the
>>      `NULL' value is a legal value for the column, and the default
>>      value is `NULL'. If an *Note `ENUM': enum. column is declared `NOT
>>      NULL', its default value is the first element of the list of
>>      allowed values.
>>
>> I think that means that there's always a default value, it cannot say
>> "Field ....  doesn't have a default value"
>>
>
> Existing behaviour(5.1) is following:
> ----
> +CREATE TABLE t1 (e enum('foo','bar') NOT NULL);
> +INSERT INTO t1 VALUES(default);
> +Warnings:
> +Warning    1364    Field 'e' doesn't have a default value
> ----
>
> This behaviour contradicts the manual which says that
> ENUM type always have default value(see Serg's comment).
>
> There are ways to fix it:
>
> 1. Remove "Field .... doesn't have a default value"  warning(going to
> be fixed in 6.0)
> 2. Add some info to manual that if default value is not provided
> explicitly then
>    a warning is issued.
>
> Which way would you recommend? or maybe you have another idea about this?

1. Remove "Field .... doesn't have a default value" warning/error.
Sergei Golubchik is correct, it's documented (and obviously intentionally
documented). In fact it's documented twice; I see the sentence
"For |ENUM| <http://dev.mysql.com/doc/refman/6.0/en/enum.html>, the
default is the first enumeration value."
in http://dev.mysql.com/doc/refman/6.0/en/data-type-defaults.html.
So an ENUM always has a default.
So the error cannot occur.

Please also make sure an error does not occur in this situation:
create table t (s1 enum('a','b') not null);
select default(s1) from t;

And in this situation: (assume it's not strict mode):
create table t (s1 enum('a','b') not null);
insert into t values (null);

After
create table t (s1 enum('a','b') not null);
select column_default from information_schema.columns where table_name =
't';
... I do not get 'a', I get NULL.

After
create table t (s1 enum('a','b') not null);
insert into t values ('b');
update t set s1 = default;
The result is not 'a', it is ''.

After
create table t (s1 enum('a','b') not null);
insert into t values ('b');
update t set s1 = null;
The result is not 'a', it is ''.

What about strict mode?
The manual says a variety of things.
a. "If you update a column that has been declared |NOT NULL| by setting
to |NULL|, an error occurs if strict SQL mode is enabled; otherwise, the
column is set to the implicit default value ..."
(I think this is saying UPDATE ... SET column_name NULL fails,
but UPDATE ... SET column_name = DEFAULT succeeds.)
b. "If the column cannot take |NULL| as the value, MySQL defines the
column with no explicit |DEFAULT| clause. For data entry, if an |INSERT|
<http://dev.mysql.com/doc/refman/6.0/en/insert.html> or |REPLACE|
<http://dev.mysql.com/doc/refman/6.0/en/replace.html> statement includes
no value for the column, or an |UPDATE|
<http://dev.mysql.com/doc/refman/6.0/en/update.html> statement sets the
column to |NULL|, MySQL handles the column according to the SQL mode in
effect at the time: ... If strict mode is enabled, an error occurs for
transactional tables and the statement is rolled back. For
non-transactional tables, an error occurs, but if this happens for the
second or subsequent row of a multiple-row statement, the preceding rows
will have been inserted."
c. [For INSERT] "If you want an |INSERT|
<http://dev.mysql.com/doc/refman/6.0/en/insert.html> statement to
generate an error unless you explicitly specify values for all columns
that do not have a default value, you should use strict mode."
(The word "implicit" is not here.)
d. [For INSERT with an empty VALUES list] "In strict mode, an error
occurs if any column doesn't have a default value."
(The word "implicit" is not here.)
I can't figure out how we can have "a." and "b." and "c."
and "d." and still support our "implicit default" for ENUM.
So I suggest:
* The remarks about "implicit" do not apply for this case,
  it was not anticipated. It is in any case difficult for
  users to notice whether a default is implicit or explicit.
* The assignment of DEFAULT to an ENUM is okay in strict mode,
  because there's always an implicit default.
* The assignment of NULL to an ENUM is okay in strict mode,
  even for a NOT NULL column, because we treat it like DEFAULT.

-- 

Peter Gulutzan
Database Group / MySQL www.mysql.com
Sun Microsystems of Canada Inc.
Edmonton, AB, Canada