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: | |
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
[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;