MySQL Bugs: #62051: UNIONed SELECTS of different data types returns BLOB

Bug #62051	UNIONed SELECTS of different data types returns BLOB
Submitted:	1 Aug 2011 22:33	Modified:	2 Aug 2011 21:59
Reporter:	David Berg	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S3 (Non-critical)
Version:	5.2.34 CE	OS:	Windows (XP Pro SP3)
Assigned to:		CPU Architecture:	Any

Description:
A complex UNIONed SELECT of different data types results in a BLOB.

The same query in Query Browser results in a CHAR (preferred).

The genesis of this problem may be a duplicate of several other similar postings, but the immediate cause is different.

Note that if the first SELECT is CAST(... AS CHAR), the results are properly displayed in Workbench, but I don't believe this should be a necessary remedy.

How to repeat:
Perform the following in both Workbench and Query Browser to perceive the different results. Workbench produces BLOB; Query Browser produces CHAR.

CREATE TABLE Tab1 (Col1 BOOLEAN);
INSERT INTO Tab1 VALUES (1),(1),(1),(0),(0);
SELECT * FROM Tab1;
SELECT CASE WHEN Col1 IS TRUE THEN "True" ELSE "False" END AS Col1 FROM Tab1
UNION ALL
SELECT CONCAT(SUM(Col1 IS TRUE), " / ",  SUM(Col1 IS FALSE)) FROM Tab1;

If you ...

SELECT CAST(CASE WHEN Col1 IS TRUE THEN "True" ELSE "False" END AS CHAR) AS Col1 FROM Tab1
UNION ALL
SELECT CONCAT(SUM(Col1 IS TRUE), " / ",  SUM(Col1 IS FALSE)) FROM Tab1;

... in Workbench, then the results are displayed as CHAR.

Suggested fix:
Default to display mixed data types in a UNIONed select as CHAR.

Originally this is a server problem/feature. 

Had you set "Treat BINARY/VARBINARY as nonbinary character string" in properties of SQL Editor?

Valeriy,
Yes, "Treat BINARY/VARBINARY as nonbinary character string" is set in Preferences.

In SQLyog the result here is not detected as BLOB or any other kind of binary data. I also do not understand why the discussed setting for BINARY/VARBINARY is relevant to discuss.  The only data type referred specifically in the query is TINYINT(1) (what a BOOLEAN declaration resolves to in MySQL).

Looks to me like some silly mistake with BOOLEAN in Workbench. Or possibly a C++ connector issue (to my best knowledge WB uses C++ connector whilst QB and SQLyog use the C-API).

Peter
(not a MySQL person)

VARBINARY option should matter. Look:

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 5
Server version: 5.1.59 Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> CREATE TABLE Tab1 (Col1 BOOLEAN);
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO Tab1 VALUES (1),(1),(1),(0),(0);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM Tab1;
Field   1:  `Col1`
Catalog:    `def`
Database:   `test`
Table:      `Tab1`
Org_table:  `tab1`
Type:       TINY
Collation:  binary (63)
Length:     1
Max_length: 1
Decimals:   0
Flags:      NUM 

+------+
| Col1 |
+------+
|    1 |
|    1 |
|    1 |
|    0 |
|    0 |
+------+
5 rows in set (0.00 sec)

mysql> SELECT CASE WHEN Col1 IS TRUE THEN "True" ELSE "False" END AS Col1 FROM Tab1
    -> UNION ALL
    -> SELECT CONCAT(SUM(Col1 IS TRUE), " / ",  SUM(Col1 IS FALSE)) FROM Tab1;
Field   1:  `Col1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       VAR_STRING
Collation:  binary (63)
Length:     51
Max_length: 5
Decimals:   0
Flags:      BINARY 

+-------+
| Col1  |
+-------+
| True  |
| True  |
| True  |
| False |
| False |
| 3 / 2 |
+-------+
6 rows in set (0.03 sec)

Note BINARY flag above, also type and collation. Now, how is this different from real VARBINARY:

mysql> create table tvb(c1 varbinary(10));
Query OK, 0 rows affected (0.05 sec)

mysql> select * from tvb;
Field   1:  `c1`
Catalog:    `def`
Database:   `test`
Table:      `tvb`
Org_table:  `tvb`
Type:       VAR_STRING
Collation:  binary (63)
Length:     10
Max_length: 0
Decimals:   0
Flags:      BINARY 

0 rows in set (0.01 sec)

I see no difference in metadata sent by server, same as Workbench. That's why we have that setting in Preferences. After setting it (and, probably, Workbench restart), I see character data in the results of your original query.

Please, check again.

OK .. I am not an expert in WB internals. :-)

But I wonder if the problem persists if the table is created like

CREATE TABLE Tab1 (Col1 TINYINT(1));

Valeriy,
Restarting Workbench after changing the BINARY/VARBINARY Preference setting does make the difference. It works as desired now.

I will close this posting.

Thank you for your assistance.