Bug #31134 ENUM columns are returned by C API as MYSQL_TYPE_STRING
Submitted: 21 Sep 2007 15:59 Modified: 6 May 2008 19:26
Reporter: Umut Emin Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: C API (client library) Severity:S3 (Non-critical)
Version:libmysqlclient.so.15, 4.1, 5.0, 5.1, 6.0 BK OS:Linux (64bit distro gentoo)
Assigned to: CPU Architecture:Any
Tags: API, C, enum, ENUM_FLAGS, MYSQL_TYPE_ENUM
Triage: D3 (Medium)

[21 Sep 2007 15:59] Umut Emin
Description:
1) the offered types from MYSQL_TYPE_ENUM does not work for a table column defined as 
Insert_priv` enum('N','Y') character set utf8 NOT NULL default 'N',

switch(field->type){

case MYSQL_TYPE_ENUM:
   /* enum found do sth */ 
  break;

case MYSQL_TYPE_STRING:
   /* string found char or binary */
  break;
default:
break ;
}

In this piece of code thE field type never hits the MYSQL_TYPE_ENUM.

2) Use of ENUM_FLAG doesn't work as described in the c-api..

case MYSQL_TYPE_STRING:
    if(field->flags & ENUM_FLAG)
       /* enum field found do sth */ 

this can never be executed.. 

so a field with enum cannot be recognized via c-api.

How to repeat:

everytime the binary is executed

Suggested fix:
Please do a tested update/patch and also for all distros .
[21 Sep 2007 17:31] Valeriy Kravchuk
Thank you for a problem report. What exact version of MySQL server (5.x.y?) that library came from?

With 5.0.44 and mysql command line client (C API-based) I've got:

C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -uroot -proot test -T -P3308
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 5.0.44-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table te (c1 enum('N','Y'));
Query OK, 0 rows affected (0.11 sec)

mysql> select * from te;
Field   1:  `c1`
Catalog:    `def`
Database:   `test`
Table:      `te`
Org_table:  `te`
Type:       STRING
Collation:  latin1_swedish_ci (8)
Length:     1
Max_length: 0
Decimals:   0
Flags:      ENUM

0 rows in set (0.00 sec)

So, ENUM flags are surely recognizable, hence the question above.
[21 Sep 2007 20:45] Umut Emin
Hei,
thank you for response. I just updated mysql.and the prob is still there.
/usr/lib64/mysql/libmysqlclient.so.15 -> client shared object
mysql-5.0.44 server

you can reproduce with these entities the same problem using standard "mysql" database, "user" table. 

the string type "enum" is not recognized with the given info from mysql.h and mysql_com.h

here is a main snippet which doesn't catch enum string field:

switch(field->type){
                        
                        case MYSQL_TYPE_DECIMAL:
                            fprintf(stdout, "field is decimal %s\n", field->name) ;
                            break ;
                        
                        case MYSQL_TYPE_ENUM:
                            fprintf(stdout, "ENUM FLAG is:%d\n", ENUM_FLAG) ;
                            break;
                        
                        case FIELD_TYPE_LONG:
                            fprintf(stdout, "field %s type:%s length:%lu\n",field->name, LONG_INTEGER, field->length ) ;
                            break ;
                        
                        case MYSQL_TYPE_STRING:
                            
                            if( (field->flags & BINARY_FLAG) )
                                fprintf(stdout, "field %s type:%s length:%lu\n",field->name, BINARY_CHAR, field->length ) ;
                            
                            else if( (field->flags & ENUM_FLAG) )
                                fprintf(stdout, "ENUM FLAG is:%d\n", ENUM_FLAG) ;
                               
                                else
                                fprintf(stdout, "field %s type:%s length:%lu\n",field->name, CHAR , field->length ) ;
                            
                            break;
                        
                        case MYSQL_TYPE_BLOB:
                            fprintf(stdout, "field %s type:%s length:%lu\n",field->name, BLOB, field->length ) ;
                            break ;
                        
                        default:
                            break;                                                                                                                 
                    }

Greetz,
Umut
[22 Sep 2007 18:51] Valeriy Kravchuk
Please, send the entire code.
[22 Oct 2007 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[6 May 2008 19:26] Sveta Smirnova
Thank you for the report.

Verified using following test:

#include <stdio.h>
#include <mysql.h>
#include <assert.h>
#include <string.h>

int main(int argc, char **argv)
{
	MYSQL conn;
	MYSQL_RES *results;
	MYSQL_FIELD *field;
	int OK;
	
	const char* query1= "drop table if exists bug31134";
	const char* query2= " create table bug31134(var_exp ENUM('false', 'true') DEFAULT NULL)";
	const char* query3= "insert into bug31134 values('false')";
	const char* query4= "SELECT  var_exp  AS var_exp_out from bug31134";

	mysql_init(&conn);

	if (!mysql_real_connect(&conn, "127.0.0.1", "root", "", "test", 3350, NULL,0))
		printf("Error: %s\n", mysql_error(&conn));
	
	OK = mysql_real_query (&conn, query1, strlen(query1));
	assert(0 == OK);
	
	OK = mysql_real_query (&conn, query2, strlen(query2));
	assert(0 == OK);
	
	OK = mysql_real_query (&conn, query3, strlen(query3));
	assert(0 == OK);

	OK = mysql_real_query (&conn, query4, strlen(query4));
	assert(0 == OK);
	
	results = mysql_store_result(&conn);

	while((field = mysql_fetch_field(results))) {
		printf("field name %s\n", field->name);
		switch(field->type){
			case MYSQL_TYPE_ENUM:
				printf("field type enum"); 
				break;
			case MYSQL_TYPE_STRING:
				printf("field type string"); 
				break;
			default:
				break;
			}
	}
	
	mysql_free_result(results);
	mysql_close(&conn);
	mysql_server_end();
	
	return 0;
}
[9 May 2008 2:58] Paul Dubois
Valeriy's example of [21 Sep 2007 19:31] does show ENUM, but it's for the Flags value, and that is determined from field->flags, not field->type.

This is a problem because http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html says:

"
Use of the BLOB_FLAG, ENUM_FLAG, SET_FLAG, and TIMESTAMP_FLAG flags is deprecated because they indicate the type of a field rather than an attribute of its type. It is preferable to test field->type against MYSQL_TYPE_BLOB, MYSQL_TYPE_ENUM, MYSQL_TYPE_SET, or MYSQL_TYPE_TIMESTAMP instead.
"

So the method that we recommend in the manual (test field->type against MYSQL_TYPE_ENUM) does not work, and the method that we deprecate (test field->flags against ENUM_FLAG) does work.

Not so good.
[7 Oct 2008 11:02] Konstantin Osipov
See also:
Bug#17758 SET columns returned to/by C API as MYSQL_TYPE_STRING.
[17 Jul 2013 15:51] Igor Solodovnikov
Now, when manual is fixed and properly describes current situation (see
http://bugs.mysql.com/bug.php?id=17758), this is definitely not S2 any more.
Set severity 3 for this bug.