Bug #7617 Incorrect lettercase-handling on ENUM-types

• Submitted: 2 Jan 2005 13:12
• Modified: 22 Feb 2005 20:53
• Reporter: Hajo Noerenberg
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.8
• OS: Linux (Debian GNU/Linux 3.0)
• Assigned to: Jim Winstead
• CPU Architecture: Any

[2 Jan 2005 13:12] Hajo Noerenberg

Description:
Mysqld4.1.7 correctly handles input on ENUM-types whereas 4.1.8 does not:

UPDATE user SET some_priv='y'

(lowercase 'y' !) results in:

'' (mysqld 4.1.8)
'Y' (mysqld 4.1.7 and IIRC mysqld<4.1)

This change (bug?) has not been documented. It also conflicts with "Lettercase is irrelevant when you assign values to an ENUM column." ( http://dev.mysql.com/doc/mysql/en/ENUM.html ).

Tested with:
- mysql-4.1.7.tar.gz/mysql-4.1.8.tar.gz (identical compile-options)
- mysql-server-4.1.8a-0.backports.org.1 (www.backports.org)

How to repeat:
UPDATE user SET some_priv='y';
SELECT some_priv FROM user;

[2 Jan 2005 13:57] MySQL Verification Team

Tested with latest BK source 4.1.

Notice the warnings:

mysql> update user set Reload_priv='y' where user="miguel";
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1265 | Data truncated for column 'Reload_priv' at row 5 |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

Thank you for the bug report.

[11 Feb 2005 4:31] Jim Winstead

The manual is incorrect to say that enum fields are always case-insensitive, as that actually depends on the collation specified for the enum field. (The same is true for set fields.)

The bug here is that the enum and set columns in the privilege tables were set to a case-sensitive collation, and they should be case-insensitive to avoid this sort of confusion.

[18 Feb 2005 0:02] Jim Winstead

Pushed, will be in 4.1.11.

[22 Feb 2005 20:53] Paul DuBois

Noted in 4.1.11 changelog.

Also updated the ENUM and SET column sections to point out that they
aren't necessarily case-insensitive any more.