MySQL Bugs: #50071: Results from a view containing an IFNULL() incorrectly shown as a BLOB type

Bug #50071	Results from a view containing an IFNULL() incorrectly shown as a BLOB type
Submitted:	5 Jan 2010 0:50	Modified:	8 Jan 2010 16:36
Reporter:	Chris Arndt	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Workbench	Severity:	S2 (Serious)
Version:	5.2.11.beta3	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:

I have a view on left join of a couple of tables that does not work in the new WB.  The IFNULL() column in the result set should either be a decimal or a varchar type depending on the join, but it comes back as a BLOB.

FWIW, I also see no way of seeing the underlying value of the BLOB column in a given row.  That's perhaps a different issue but isn't my concern because I don't actually have a BLOB.

These same views and tables do work properly in the old Query browser as well as the CLI.

How to repeat:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_price_trends` AS select `c`.`name` AS `name`,`c`.`rarity` AS `rarity`,ifnull(`m`.`price`,`p1`.`price_md5`) AS `price`,`p1`.`timestamp` AS `timestamp` from (((`cards` `c` join `v_cards_changed` `vcc` on((`c`.`id` = `vcc`.`card`))) join `card_prices_trending` `p1` on((`c`.`id` = `p1`.`card`))) left join `map_prices` `m` on((`p1`.`price_md5` = `m`.`md5`))) order by `c`.`name`,`p1`.`timestamp`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_cards_changed` AS select `p`.`card` AS `card`,count(0) AS `num` from `card_prices_trending` `p` group by `p`.`card` having (`num` > 1);

CREATE TABLE `cards` (
  `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `rarity` enum('common','uncommon','rare') NOT NULL DEFAULT 'common',
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB;

CREATE TABLE `card_prices_trending` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `card` smallint(5) unsigned NOT NULL,
  `price_md5` varchar(100) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `map_prices` (
  `md5` varchar(100) NOT NULL,
  `price` decimal(6,2) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`md5`)
) ENGINE=InnoDB;

Suggested fix:

How did you figure out that it is blob?

The column on the result set tab has the little 'BLOB' icon rather than a value I'd expect, ether a decimal like '0.02' or an md5 string.

What exact version of server you are wroking with? I'd say that this can be a server-related problem mostly. Look, with your tables and views on 5.1.41:

mysql> create table tvb as select * from v_price_trends;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tvb\G
*************************** 1. row ***************************
       Table: tvb
Create Table: CREATE TABLE `tvb` (
  `name` varchar(50) NOT NULL,
  `rarity` enum('common','uncommon','rare') NOT NULL DEFAULT 'common',
  `price` varbinary(300) NOT NULL DEFAULT '',
  `timestamp` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

you price column has varbinary(300) data type. This leads WB to show it as BLOB, I assume.

I am using "Server version: 5.4.3-beta-log MySQL Community Server (GPL)" right now but I'm willing and able to upgrade or downgrade if needed.

Maybe the only issue then is that the new Work Bench doesn't *show* me the value of that column the way the old Query Browser does?  If that's the case it makes the WB bench pretty useless for me because the whole point of running queries on the view is to see the value.  If all I get is a 'BLOB' icon I might as well not run the query.

You misunderstood us.

You will have same problem with old Query Browser too.

The problem is related to MySQL server not to a tool at all.

Server IFNULL() returns either decimal or varbinary not varchar. It looks like our documentation has a bug there.

I just checked our documentation here again.

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html
http://dev.mysql.com/doc/refman/5.4/en/control-flow-functions.html
http://dev.mysql.com/doc/refman/5.5/en/control-flow-functions.html

Doesn't matter if you take 5.0, 5.1, 5.4 or 5.5 documentation ... the text is the same on all ...

"If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used."

There is nothing written about IFNULL would return data type decimal or varchar ... there is written it returns a numeric or string value.

Nothing said about data types there at all.

And when you just look to the example that is given at the IFNULL() section then you will see that it is returning VARBINARY and not VARCHAR.

Example of the difference between QB and WB

Attachment: example.png (image/png, text), 397.98 KiB.

But that's the point, I don't see the same behaviour from the Query Browser.  Take a look at the screen shot I captured and you'll see it.

In the Query Browser I get meaningful, useful information.  In the Work Bench I get a 'BLOB' icon...  am I supposed to guess what the value is?  As I said before, not showing me that value is the same as me not running the query at all.  And since I need to run the query I suppose I have to use a tool that works as well.