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

[20 Nov 2008 22:34] Yvan Rodrigues
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;
[21 Nov 2008 4:45] Valeriy Kravchuk
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.
[22 Nov 2008 21:15] Yvan Rodrigues
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.
[9 Jan 2009 19:15] Paul DuBois
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
[12 Jan 2009 17:21] Yvan Rodrigues
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;