MySQL Bugs: #60652: Query returns BLOB type but no BLOBs are in the database

Bug #60652	Query returns BLOB type but no BLOBs are in the database
Submitted:	25 Mar 2011 21:09	Modified:	2 Jun 2011 18:21
Reporter:	Matt Houser	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S1 (Critical)
Version:	6.3.6	OS:	Any
Assigned to:	Julio Casal	CPU Architecture:	Any
Tags:	BLOB, connector, NET

Description:
Applying the following query (which was generated by MySql Connector/NET 6.3.6) to my table definition results in a column of type BLOB, but the database has no BLOB columns defined.

For this query, column 'C15' is returned as a BLOB.  It should be a bit.

SELECT
`Project5`.`C2` AS `C1`, 
`Project5`.`C1` AS `C2`, 
`Project5`.`AmazonAccessKeyId`, 
`Project5`.`Name`, 
`Project5`.`NextAction`, 
`Project5`.`RepeatType`, 
`Project5`.`UserId`, 
`Project5`.`IsEnabled`, 
`Project5`.`StartDate`, 
`Project5`.`CreatorActionId`, 
`Project5`.`IsDeleted`, 
`Project5`.`C3`, 
`Project5`.`C4`, 
`Project5`.`C5`, 
`Project5`.`C6`, 
`Project5`.`C7`, 
`Project5`.`C8`, 
`Project5`.`C9`, 
`Project5`.`C10`, 
`Project5`.`C11`, 
`Project5`.`C12`, 
`Project5`.`C13`, 
`Project5`.`C14`, 
`Project5`.`C15`, 
`Project5`.`C16`, 
`Project5`.`C17`, 
`Project5`.`C18`
FROM (SELECT
`UnionAll2`.`C1`, 
`Extent4`.`AmazonAccessKeyId`, 
`Extent4`.`CreatorActionId`, 
`Extent4`.`IsDeleted`, 
`Extent4`.`IsEnabled`, 
`Extent4`.`Name`, 
`Extent4`.`NextAction`, 
`Extent4`.`RepeatType`, 
`Extent4`.`StartDate`, 
`Extent4`.`UserId`, 
    CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN ('0X0X0X')  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN ('0X1X0X')  ELSE ('0X2X0X') END AS `C2`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C2`)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL) END AS `C3`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C3`)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL) END AS `C4`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C4`)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL) END AS `C5`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C5`)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL) END AS `C6`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C6`)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL) END AS `C7`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C7`)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL) END AS `C8`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C8`)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL) END AS `C9`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C9`)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL) END AS `C10`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C10`)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL) END AS `C11`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (NULL)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C11`) END AS `C12`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (NULL)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C12`) END AS `C13`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (NULL)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C13`) END AS `C14`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (NULL)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C14`) END AS `C15`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (NULL)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C15`) END AS `C16`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (NULL)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (`UnionAll2`.`C16`) END AS `C17`, 
CASE WHEN (`UnionAll2`.`C18` = cast(1 as decimal(0,0))) THEN (NULL)  WHEN (`UnionAll2`.`C19` = cast(1 as decimal(0,0))) THEN (NULL)  ELSE (`UnionAll2`.`C17`) END AS `C18`
FROM ((SELECT
`UnionAll1`.`ActionId` AS `C1`, 
`UnionAll1`.`ImageId` AS `C2`, 
`UnionAll1`.`InstanceCount` AS `C3`, 
`UnionAll1`.`InstanceType` AS `C4`, 
`UnionAll1`.`SpotPrice` AS `C5`, 
`UnionAll1`.`Days` AS `C6`, 
`UnionAll1`.`Hours` AS `C7`, 
`UnionAll1`.`IsTerminate` AS `C8`, 
`UnionAll1`.`Minutes` AS `C9`, 
`UnionAll1`.`Seconds` AS `C10`, 
`UnionAll1`.`C1` AS `C11`, 
`UnionAll1`.`C2` AS `C12`, 
`UnionAll1`.`C3` AS `C13`, 
`UnionAll1`.`C4` AS `C14`, 
`UnionAll1`.`C5` AS `C15`, 
`UnionAll1`.`C6` AS `C16`, 
`UnionAll1`.`C7` AS `C17`, 
`UnionAll1`.`C8` AS `C18`, 
`UnionAll1`.`C9` AS `C19`
FROM ((SELECT
`Extent1`.`ActionId`, 
`Extent1`.`ImageId`, 
`Extent1`.`InstanceCount`, 
`Extent1`.`InstanceType`, 
`Extent1`.`SpotPrice`, 
`Extent1`.`Days`, 
`Extent1`.`Hours`, 
`Extent1`.`IsTerminate`, 
`Extent1`.`Minutes`, 
`Extent1`.`Seconds`, 
NULL AS `C1`, 
NULL AS `C2`, 
NULL AS `C3`, 
NULL AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
cast(1 as decimal(0,0)) AS `C8`, 
cast(0 as decimal(0,0)) AS `C9`
FROM `ec2s_requestspotinstanceactions` AS `Extent1`) UNION ALL (SELECT
`Extent2`.`ActionId`, 
NULL AS `C1`, 
NULL AS `C2`, 
NULL AS `C3`, 
NULL AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
NULL AS `C9`, 
NULL AS `C10`, 
NULL AS `C11`, 
NULL AS `C12`, 
NULL AS `C13`, 
NULL AS `C14`, 
NULL AS `C15`, 
`Extent2`.`InstanceId`, 
cast(0 as decimal(0,0)) AS `C16`, 
cast(0 as decimal(0,0)) AS `C17`
FROM `ec2s_stopinstanceactions` AS `Extent2`)) AS `UnionAll1`) UNION ALL (SELECT
`Extent3`.`ActionId`, 
NULL AS `C1`, 
NULL AS `C2`, 
NULL AS `C3`, 
NULL AS `C4`, 
NULL AS `C5`, 
NULL AS `C6`, 
NULL AS `C7`, 
NULL AS `C8`, 
NULL AS `C9`, 
`Extent3`.`InstanceId`, 
`Extent3`.`Days`, 
`Extent3`.`Hours`, 
`Extent3`.`IsStop`, 
`Extent3`.`Minutes`, 
`Extent3`.`Seconds`, 
NULL AS `C10`, 
cast(0 as decimal(0,0)) AS `C11`, 
cast(1 as decimal(0,0)) AS `C12`
FROM `ec2s_startinstanceactions` AS `Extent3`)) AS `UnionAll2` INNER JOIN `ec2s_actions` AS `Extent4` ON `UnionAll2`.`C1` = `Extent4`.`ActionId`
 WHERE (`Extent4`.`UserId` = 1) AND (`Extent4`.`CreatorActionId` IS  NULL)) AS `Project5`
 ORDER BY 
`Name` ASC

How to repeat:
1.  Engineer the attached model.
2.  Run the InsertValues.sql script to insert sample data
3.  Run the Problem.sql script (same as above) to demonstrate the problem.

Suggested fix:
Fix the return type.

MySql Model

Attachment: EFTest3.mwb (application/octet-stream, text), 11.82 KiB.

Problem Query

Attachment: Problem.sql (application/octet-stream, text), 5.74 KiB.

Sample data

Attachment: InsertValues.sql (application/octet-stream, text), 520 bytes.

The return type is the following (in mysql command line client with --column-type-info):

Field  24:  `C15`
Catalog:    `def`
Database:   ``
Table:      `Project5`
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      BINARY

So, it is a string with binary flag and collation, not BLOB:

mysql> create table tblob(c1 blob);
Query OK, 0 rows affected (0.13 sec)

mysql> select * from tblob;
Field   1:  `c1`
Catalog:    `def`
Database:   `eftest3`
Table:      `tblob`
Org_table:  `tblob`
Type:       BLOB
Collation:  binary (63)
Length:     65535
Max_length: 0
Decimals:   0
Flags:      BLOB BINARY

So, in what tool/application do you see this column as BLOB, and why you expect it to be "a bit"?

Workbench showing C15 as BLOB

Attachment: C15Blob.JPG (image/jpeg, text), 17.20 KiB.

Image showing expected bit data

Attachment: ShowingExpectedBit.jpg (image/jpeg, text), 54.12 KiB.

Error shown when performing the problem query using Connector/NET

Attachment: InvalidCastError.JPG (image/jpeg, text), 35.77 KiB.

Image showing data at time of error using Connector/NET. Datatype is "BLOB".

Attachment: ShowingBlobResult.jpg (image/jpeg, text), 55.47 KiB.

I have attached some images showing why it should be a bit in the result.  

1. One image shows MySql Workbench showing the column as a BLOB.
2. One image shows what columns should be present in the result. As shown, it should be the IsStop column which is a BIT column.
3. One image shows details about the error in Connector/NET when executed via Visual Studio 2010.  As seen, it is showing that the return type is a BLOB.

So, it's Workbench... 

Please, check in Preferences > SQL Editor if "Treat BINARY/VARBINARY as nonbinary character string" checkbox is checked.

In Workbench preferences, that setting is *unchecked*.

However, it's not just Workbench.  Connector/NET is treating it exactly the same.

Even if Workbench is treating a BINARY or VARBINARY as a BLOB, why is it that anyways?  It should be a BIT.  Other queries return 0 or 1 for this field.

The query is very long and nearly impossible to understand.
It has to be simplified first. Though, when I remove the UNION part declaring `Extent2` C15 is not shown as BLOB anymore. I cannot find a reasonable for that...

Please ignore the previous comment.

Bit values are returned as binary values. To display them in printable form, add 0 or use a conversion function such as BIN(). Maybe that is the case here:

SELECT * FROM (
(SELECT b'0') UNION ALL 
  (SELECT bit_column from bit_table)) AS `UnionAll2`

bit_column gets reduced to binary because it is used in union with b'0'.

Modify the query by editing this line:

`Project5`.`C15`+0 as `C15`

Other BIT columns in the query (such as IsEnabled) were not combined with constants in UNION and therefore the server returned them with the original metadata.

It works like this:

"bit_column + 1" tells the server to treat the result as the number
"bit_column UNION 1" assumes that all types are reduced to the simplest data type, which is binary.

This does not look like a bug to me.

Please do not close this issue.

As mentioned, the sql was generated by mysql connector .Net.

If the bug is not in the server, then it must be in the connector and must be fixed there as soon as possible.

Please re-open and move to the connector group.  Please fix this right away.  This works properly using Microsoft SQL, so you must make it work with mysql or I must revert to ms sql.

Matt,

Sorry, it was my mistake.
I missed your comment that the query is generated by Connector/NET.
The bug is marked as 'verified'. Indeed, this is the problem in the NET driver, which will be reported to the developers ASAP.

Thanks for your feedback.

Please attach your Visual Studio project with the Entity Framework model and a sample execution code that would produce the mentioned query and exception. I need this to figure out if this is a Connector/Net or a server issue.

The bug is very simmilar to bug #59824 for which there is a workaround, but need your EF model to verify this.

Also, the reported version for this issue is 5.5.8, but I believe that number is for the server version, not the Connector/Net version. Would you confirm in which Connector/Net version and server version you see this issue?

Sample application using Connector/NET

Attachment: EFTest3-TestApp.zip (application/x-zip-compressed, text), 90.75 KiB.

I have added a sample application using VS2010 and entity frameworks.  Run the app and an exception is thrown.

The version is actually for the server.  I originally posted it as a server problem.  The "server guys" says it was a connector issue and they changed the category but did not update the version.

The server is using 5.5.8, the connector version is 6.3.6

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

Fixed versions: 6.1.6, 6.2.5, 6.3.7, 6.4.1+.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html