MySQL Bugs: #20377: enum implemented incorrectly?

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

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.

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).

Well, let me turn it into feature request ...
(and documentation clarification request as well)

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.

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.

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