Bug #20377 enum implemented incorrectly?
Submitted: 10 Jun 2006 23:17 Modified: 11 Jun 2006 8:24
Reporter: Roman Lamotkin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0.22 OS:Windows (Windows 98 SE)
Assigned to: CPU Architecture:Any

[10 Jun 2006 23:17] Roman Lamotkin
Description:
Orininally posted to forum, but it has been viewed a number of times,
and no replies given (such a hard-to-solve problem?).

mysql> SET NAMES utf8;

mysql> CREATE TABLE `sometable` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `field1` enum( CAST(_cp1251'foreing_word_here' AS CHAR CHARACTER SET utf8),
		'val2','val3'),
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

It results in a syntax error - the parser can't understand non-constant value
for an ENUM entry. Meanwhile the documentation claims all metadata are in UTF8!
So how to get thru?

I tried a sideway:

SET @Var1 = CAST(_cp1251 ...;
...  enum( @Var1, ...

But was out of luck with this too :( ...

How to repeat:
run the same commands @ mysql.exe console.

Suggested fix:
rewrite some part of the syntax parser code, I believe.
And look forward - to make UTF-unabled entirely all the names
categories: databases, tables, fields, etc.
[11 Jun 2006 6:58] Valeriy Kravchuk
Thank you for a problem report. Sorry, but you can not use expressions (including user variables and even CAST) in ENUM. Take a look at sources (syntax specification, similar to BNF), sql/sql_yacc.yy:

field_spec:
...
        | ENUM {Lex->interval_list.empty();} '(' string_list ')' opt_binary
          { $$=FIELD_TYPE_ENUM; }
...
string_list:
        text_string                     { Lex->interval_list.push_back($1); }
        | string_list ',' text_string   { Lex->interval_list.push_back($3); };

So, it is not a bug, but intended behaviour. I can mark this as verified feature request, if you want, or a documentation request (to describe this clearly at http://dev.mysql.com/doc/refman/5.0/en/enum.html).
[11 Jun 2006 8:19] Roman Lamotkin
Well, let me turn it into feature request ...
(and documentation clarification request as well)
[11 Jun 2006 8:24] Valeriy Kravchuk
So, it is a reasonable and verified feature request to support constant-based expressions in ENUM(...) data type declaration.

This page, http://dev.mysql.com/doc/refman/5.0/en/enum.html, should also clearly state that expressions are not allowed now.
[11 Jun 2006 23:21] Jon Stephens
The Manual already says:

        An ENUM is a string object with a value chosen from a list 
        of allowed values that are enumerated explicitly in the column 
        specification at table creation time.

"Enumerated explicitly" means that you can't imply a string value, you must provide one. However, I have added the following to the ENUM section:

        An enumeration value must be a quoted string literal; it may not
        be an expression, even one that evaluates to a string value.
        This means that you also may not employ a user variable as an
        enumeration value.
[10 Oct 2008 11:17] Alexander Barkov
Using CAST in create statement is perhaps too complex.
Adding this syntax would be enough:

CREATE TABLE t1 (a enum(_cp1251'str1','str2','str3')) charset utf8;

Currently it returns syntax error:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '_cp1251'test','test1','test2')) charset utf8' at line 1