Bug #17758 SET columns returned to/by C API as MYSQL_TYPE_STRING
Submitted: 27 Feb 2006 23:02 Modified: 30 Jun 2011 15:45
Reporter: Andy Dustman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:4.1.12, 5.0.18, 5.1.54 OS:Linux (Linux)
Assigned to: Paul Dubois
Tags: C_API
Triage: Needs Triage: D2 (Serious)

[27 Feb 2006 23:02] Andy Dustman
Description:
Given this table schema:

CREATE TABLE `tb2aaaacaf1090` (
  `col1` set('ash','birch','cedar','larch','pine') default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

col1 is is returned to the C API as FIELD_TYPE_STRING. It should be returned as FIELD_TYPE_SET. (or MYSQL_TYPE_*, by the newer conventions)

Note that the current sitation makes SET indistinquishable from other string types, so it is impossible for an API to translate this into a native type.

How to repeat:
With the Python bindings:

Python 2.4.2 (#2, Sep 30 2005, 21:19:01)
[GCC 4.0.2 20050808 (prerelease) (Ubuntu 4.0.1-4ubuntu8)] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> import _mysql
>>> db=_mysql.connect(db="test",read_default_file="~/.my.cnf")
>>> db.query("select * from `tb-48610fb4`")
>>> r=db.store_result()
>>> r.describe()
(('col1', 254, 15, 78, 78, 0, 1),)
>>>

The second value of this tuple is the C API field type as returned by mysql_fetch_fields. 254 corresponds to FIELD_TYPE_STRING. FIELD_TYPE_SET is 248.

Python bindings available at http://sourceforge.net/projects/mysql-python or from several OS vendors (Red Hat, Debian, Gentoo, Ubuntu, some BSDs). 1.2.0 should work fine for this purpose.

If you really need it, I could probably come up with a C version to replicate it.

Systems tested:

4.1.12-Debian_1ubuntu3.1-log on x86
5.0.18-log on Gentoo x86-64

Suggested fix:
Return FIELD_TYPE_SET/MYSQL_TYPE_SET for SET columns
[28 Feb 2006 0:10] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this under 5.0 from bk
[4 Mar 2006 23:20] Andy Dustman
I found a solution for this: Even though the field type is never set to FIELD_TYPE_SET, the field flag does have SET_FLAG set, so I can translate this into the correct type.

I decided to review the documentation and found this:

http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html

SET_FLAG is supposed to be deprecated, and MYSQL_TYPE_SET (same as FIELD_TYPE_SET) used instead. However, this does not seem to be possible at the present time. I guess either the documentation needs to be updated, or else someone forgot to implement this.
[28 Mar 2006 17:59] Konstantin Osipov
ENUM columns also return MYSQL_TYPE_STRING typecode.
The manual needs to be fixed, this is an incompatible change that we shouldn't do in a stable release.
The bug itself shall be fixed in 5.1 or 5.2.
[5 Dec 2010 17:08] Valerii Kravchuk
We still have this problem in 5.1:

macbook-pro:5.1 openxs$ bin/mysql -uroot --column-type-info test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.54-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE TABLE `tb2aaaacaf1090` (
    ->   `col1` set('ash','birch','cedar','larch','pine') default NULL
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
    -> ;
Query OK, 0 rows affected (0.07 sec)

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

0 rows in set (0.01 sec)
[5 Dec 2010 17:11] Valerii Kravchuk
While manual, http://dev.mysql.com/doc/refman/5.1/en/c-api-data-structures.html, still says the SET flag is deprecated and suggests to check for type (that is never returned by server!). 

I am making this a documentation bug. Feel free to make it C API bug again after fixing the manual.
[30 Jun 2011 15:46] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated text:

Some of these flags indicate data type information and are superseded
or used in conjunction with the MYSQL_TYPE_xxx value in the
field->type member described later:

* To check for BLOB or TIMESTAMP values, check whether type is
  MYSQL_TYPE_BLOB or MYSQL_TYPE_TIMESTAMP. (The BLOB_FLAG and
  TIMESTAMP_FLAG flags are unneeded.)

* ENUM and SET values are returned as strings. For these, check that
  the type value is MYSQL_TYPE_STRING and that the ENUM_FLAG or
  SET_FLAG flag is set in the flags value.