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

[25 Mar 2011 21:09] Matt Houser
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.
[25 Mar 2011 21:10] Matt Houser
MySql Model

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

[25 Mar 2011 21:10] Matt Houser
Problem Query

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

[25 Mar 2011 21:10] Matt Houser
Sample data

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

[26 Mar 2011 7:20] Valeriy Kravchuk
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
[26 Mar 2011 7:23] Valeriy Kravchuk
So, in what tool/application do you see this column as BLOB, and why you expect it to be "a bit"?
[27 Mar 2011 2:55] Matt Houser
Workbench showing C15 as BLOB

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

[27 Mar 2011 3:10] Matt Houser
Image showing expected bit data

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

[27 Mar 2011 3:22] Matt Houser
Error shown when performing the problem query using Connector/NET

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

[27 Mar 2011 3:38] Matt Houser
Image showing data at time of error using Connector/NET.  Datatype is "BLOB".

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

[27 Mar 2011 3:39] Matt Houser
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.
[27 Mar 2011 6:17] Valeriy Kravchuk
So, it's Workbench... 

Please, check in Preferences > SQL Editor if "Treat BINARY/VARBINARY as nonbinary character string" checkbox is checked.
[27 Mar 2011 13:46] Matt Houser
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.
[20 Apr 2011 13:18] Bogdan Degtyariov
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...
[20 Apr 2011 14:34] Bogdan Degtyariov
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'.
[20 Apr 2011 14:59] Bogdan Degtyariov
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.
[21 Apr 2011 14:25] Matt Houser
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.
[27 Apr 2011 7:24] Bogdan Degtyariov
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.
[23 May 2011 18:26] Julio Casal
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?
[23 May 2011 19:51] Matt Houser
Sample application using Connector/NET

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

[23 May 2011 19:52] Matt Houser
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
[2 Jun 2011 18:21] Julio Casal
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