MySQL Bugs: #30233: incorrect column type mapping for columns derived 5.1 XML functions

Bug #30233	incorrect column type mapping for columns derived 5.1 XML functions
Submitted:	3 Aug 2007 18:18	Modified:	6 Sep 2007 10:37
Reporter:	Lynn Eriksen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	5.07, 5.0.8GA	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
For queries that have data extracted from Xml using the XML functions in 5.1 the column type is being mapped as System.Byte[] instead of System.String.

I have tracked this issue to what I belive to be an error in the MySqlField in the 'SetTypeAndFlags' method/

How to repeat:
Here is the breakdown for the scenario:

1) The type is determined to 'LongBlob'

2) The MySqlField property 'IsBlob' is only returning 'true' when 'colFlags & ColumnFlags.BLOB > 0'. The column flags for the xml-function derived column are only showing as 'Not_Null', or 1. The 'mySqlDbType' is never checked.

3) The 'IsBinary' funcion seems to be reporting correctly that the Xml-function derived column is not null. 

Since the 'IsBlob' property is not returning correctly the section of the routine that changes non-binary blob types to corresponding text types ('if (IsBlob && !IsBinary)') is not called.

Suggested fix:
I would suggest testing the mySqlDbType additionaly in the 'IsBlob' method:

Here is the old code:

        public bool IsBlob
        {
            get { return (colFlags & ColumnFlags.BLOB) > 0; }
        }

Here is what I propose:

        public bool IsBlob
	{
	    get
            {
                //primary - determine from colFlags
                //----------------------------------

                //determine if blob from colFlags
                if ((colFlags & ColumnFlags.BLOB) > 0)
                {
                    return true;
                }

                //secondary - determine from mySqlDbType
                //----------------------------------
                switch (mySqlDbType)
                {
                    case MySqlDbType.TinyBlob:
                        return true;
                    case MySqlDbType.MediumBlob:
                        return true;
                    case MySqlDbType.LongBlob:
                        return true;
                    case MySqlDbType.Blob:
                        return true;                   
                }

                //default return
                return false;
            }
	}

Hi Lynn and thanks for another report. Although I agree with your analysis, I would ask two things from you:
 1) Boil down test case to something *much* simpler
 2) Test the behavior of server, as I think this might actually be a server bug/inconsistency (if TYPE is LONG_BLOB then FLAG should have contained BLOB, right?):

Server started with mysqld --stanalone --console on WinXP Pro SP2 localhost.
C:\mysql507\mysql-5-1-22-beta-pb1555\bin>mysql -uroot -p --column-type-info bug3
0233

Server version: 5.1.22-beta-pb1555 MySQL Pushbuild Edition, build 1555

mysql> SELECT * FROM `test`.`contents_catalog_info` LIMIT 1;
Field   1:  `CatalogID`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       LONG
Collation:  binary (63)
Length:     10
Max_length: 0
Decimals:   0
Flags:      NOT_NULL PRI_KEY UNSIGNED AUTO_INCREMENT NUM PART_KEY

Field   2:  `WebSiteGuid`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     120
Max_length: 0
Decimals:   0
Flags:      NOT_NULL MULTIPLE_KEY PART_KEY

Field   3:  `SectionGuid`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     60
Max_length: 0
Decimals:   0
Flags:      NOT_NULL

Field   4:  `CatalogGuid`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     60
Max_length: 0
Decimals:   0
Flags:      NOT_NULL UNIQUE_KEY PART_KEY

Field   5:  `CatalogType`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     512
Max_length: 0
Decimals:   0
Flags:      NOT_NULL MULTIPLE_KEY NO_DEFAULT_VALUE PART_KEY

Field   6:  `ListingType`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     60
Max_length: 0
Decimals:   0
Flags:      NOT_NULL

Field   7:  `IsPublished`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       LONG
Collation:  binary (63)
Length:     11
Max_length: 0
Decimals:   0
Flags:      NOT_NULL NUM

Field   8:  `CreateDate`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 0
Decimals:   0
Flags:      NOT_NULL BINARY

Field   9:  `ModifyDate`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       TIMESTAMP
Collation:  binary (63)
Length:     19
Max_length: 0
Decimals:   0
Flags:      NOT_NULL UNSIGNED ZEROFILL BINARY TIMESTAMP

Field  10:  `CatalogMeta`
Catalog:    `def`
Database:   `test`
Table:      `contents_catalog_info`
Org_table:  `contents_catalog`
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777215
Max_length: 0
Decimals:   0
Flags:      NOT_NULL BLOB NO_DEFAULT_VALUE

Field  11:  `Name`
Catalog:    `def`
Database:   ``
Table:      `contents_catalog_info`
Org_table:  ``
Type:       LONG_BLOB
Collation:  latin1_swedish_ci (8)
Length:     50331648
Max_length: 0
Decimals:   31
Flags:      NOT_NULL

Field  12:  `Summary`
Catalog:    `def`
Database:   ``
Table:      `contents_catalog_info`
Org_table:  ``
Type:       LONG_BLOB
Collation:  latin1_swedish_ci (8)
Length:     50331648
Max_length: 0
Decimals:   31
Flags:      NOT_NULL

Field  13:  `Comments`
Catalog:    `def`
Database:   ``
Table:      `contents_catalog_info`
Org_table:  ``
Type:       LONG_BLOB
Collation:  latin1_swedish_ci (8)
Length:     50331648
Max_length: 0
Decimals:   31
Flags:      NOT_NULL

Field  14:  `Security`
Catalog:    `def`
Database:   ``
Table:      `contents_catalog_info`
Org_table:  ``
Type:       LONG_BLOB
Collation:  latin1_swedish_ci (8)
Length:     50331648
Max_length: 0
Decimals:   31
Flags:      NOT_NULL

0 rows in set (0.00 sec)

mysql>

Lynn, thanks for info provided, rechecking.

Hmm, I rushed to conclusion... Manual gives explanation of behavior observed here:
http://dev.mysql.com/doc/refman/5.0/en/c-api-datatypes.html
 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.

Simplified query showing the difference in metadata output from 5.0 to 5.1:
mysql> select c.*, CONCAT(c.CatalogMeta, " ", c.ListingType) AS AddFld from contents_catalog AS c LIMIT 1;

+--------------------+
| @@version          |
+--------------------+
| 5.1.22-beta-pb1555 |
+--------------------+
Field  10:  `CatalogMeta`
Catalog:    `def`
Database:   `test`
Table:      `c`
Org_table:  `contents_catalog`
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777215
Max_length: 476
Decimals:   0
Flags:      NOT_NULL BLOB NO_DEFAULT_VALUE

Field  11:  `AddFld`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       LONG_BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777216
Max_length: 484
Decimals:   31
Flags:

+-------------------+
| @@version         |
+-------------------+
| 5.0.48-pb1038-log |
+-------------------+
Field  10:  `CatalogMeta`
Catalog:    `def`
Database:   `test`
Table:      `c`
Org_table:  `contents_catalog`
Type:       BLOB
Collation:  latin1_swedish_ci (8)
Length:     16777215
Max_length: 476
Decimals:   0
Flags:      NOT_NULL BLOB NO_DEFAULT_VALUE

Field  11:  `AddFld`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  latin1_swedish_ci (8)
Length:     8192
Max_length: 484
Decimals:   31
Flags:

I guess you're right again, thanks.

Verified as described with test case provided. According to manual, we should test field->type instead of flags for servers 5.0 and up.

Good deal.

Glad to help.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33057

Fixed in 1.0.11, 5.0.9, 5.1.3, and 5.2+

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/33061

A note has been added to the 5.1.3, 5.0.8 and 1.0.11 changelogs: 

Extracting data through XML functions within a query returns the data as System.Byte[]. This was due to Connector/NET incorrectly identifying BLOB fields as binary, rather than text.