MySQL Bugs: #40910: SELECT MAX(DATETIME) INTO @var yields LONGBLOB

Bug #40910	SELECT MAX(DATETIME) INTO @var yields LONGBLOB
Submitted:	20 Nov 2008 22:34	Modified:	12 Jan 2009 17:21
Reporter:	Yvan Rodrigues	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	4.1.22, 5.0.70, 5.1.22-rc, 5.1.30, 6.0.7	OS:	Any (7.0)
Assigned to:		CPU Architecture:	Any
Tags:	BINARY, BLOB, datetime, longblob
Triage:	Triaged: D5 (Feature request) / R3 (Medium) / E3 (Medium)

Description:
When using SELECT INTO to select an aggregate function result of a DATETIME, one would expect the result to be of the DATETIME type, but instead it is LONGBLOB.

How to repeat:
# the following describes a LONGBLOB
DROP TABLE IF EXISTS `calendar`;
CREATE TABLE  `calendar` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `thedate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

SELECT MIN(thedate) FROM calendar INTO @uservar;
# the following is just to demonstrate the type of @uservar,
# it is not part of the problem
CREATE TEMPORARY TABLE typeof AS SELECT @uservar;
DESCRIBE typeof;

# workaround:
DROP TABLE IF EXISTS `calendar`;
CREATE TABLE  `calendar` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `thedate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

DECLARE uservar DATETIME;
SELECT MIN(thedate) FROM calendar INTO uservar;
CREATE TEMPORARY TABLE typeof AS SELECT uservar;
DESCRIBE typeof;

Thank you for a problem report. Verified just as described on MySQL 4.1.22, 5.0.70, 5.1.30 and 6.0.7. Workaround will work only in stored procedures/triggers.

I see this person found the same bug but attributed it to Connector/NET:
http://bugs.mysql.com/bug.php?id=40366

In that case Tonci says:
Using functions like MAX set's BINARY flag (same with ad-hoc queries like "SHOW ...", see
Bug#10491).

I haven't found this fact to be documented, and unless there is a valid "useful" reason for it, it seems like bad design to return it as a binary string. For any given datatype one would expect that MIN/MAX would return the corresponding value in the same type as the source field.

I have updated the user-variable section to explain that they have a limited number of types and the conversion takes place for other types:

User variables can be assigned a value from a limited set of data
types: integer, decimal, floating-point, binary or non-binary string,
or NULL value. Assignment of decimal and real values does not
preserve the precision or scale of the value. A value of a type other
than one of the allowable types is converted to an allowable type.
For example, a value having a temporal or spatial data type is
converted to a binary string.

http://dev.mysql.com/doc/refman/5.1/en/user-variables.html

I can confirm that the issue is not the aggregate function returning BLOB, but the user variable converting the value assigned to it to a binary string.

In the following, both tables yield a binary field.

# establish a table from which we can query to make a point
DROP TABLE IF EXISTS `calendar`;
CREATE TABLE  `calendar` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `thedate` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

# populate it with some data
INSERT INTO calendar VALUES
  (NULL, '2009-01-01'),
  (NULL, '2009-02-01');

# query a simple select
SELECT thedate FROM calendar LIMIT 1 INTO @uservar1;
# determine the type 
DROP TABLE IF EXISTS `typeof1`;
CREATE TABLE typeof1 AS SELECT @uservar1;
DESCRIBE typeof1;

# query and aggregate function
SELECT MIN(thedate) FROM calendar INTO @uservar2;
# determine the type 
DROP TABLE IF EXISTS `typeof2`;
CREATE TABLE typeof2 AS SELECT @uservar2;
DESCRIBE typeof2;