Bug #13361 FORMAT function on NULL DECIMAL field crashes MySQL service
Submitted: 20 Sep 2005 23:21 Modified: 24 Sep 2005 3:19
Reporter: Fernando Cristovao Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.12-beta-nt/BK source OS:Windows (Windows XP SP2/Linux)
Assigned to: Jim Winstead CPU Architecture:Any

[20 Sep 2005 23:21] Fernando Cristovao
Description:
I can reproduce the following behaviour in any client software; Query Browser for instance.

In a SELECT statement, I try to use the FORMAT function on a field of type DECIMAL(12,2) that can be NULL.
The problem is that, when the selected records have NULL values on that field, the database service gets very busy for a few seconds and finally, it crashes.

If none of the selected records contain NULL valueson the DECIMAL fiels, then there is no crash and the query returns just fine.

How to repeat:
#Create the table:
CREATE TABLE `teste` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `quant` decimal(12,2) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#Insert a record with a NULL value on the field:
INSERT INTO teste(quant) VALUES(NULL);

#Select the formatted field (this one crashes the database service)
SELECT FORMAT(quant, 2) FROM teste

Suggested fix:
I believe that the FORMAT function should return NULL... and the server should not crash  :-)
[20 Sep 2005 23:54] MySQL Verification Team
Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.14-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `teste` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `quant` decimal(12,2) default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO teste(quant) VALUES(NULL);
Query OK, 1 row affected (0.02 sec)

mysql> SELECT FORMAT(quant, 2) FROM teste;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

miguel@hegel:~/dbs/5.0> libexec/mysqld
050920 20:46:04  InnoDB: Started; log sequence number 0 52348
050920 20:46:04 [Note] libexec/mysqld: ready for connections.
Version: '5.0.14-rc-debug'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld got signal 11;

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8e5a450 = SELECT FORMAT(quant, 2) FROM teste
thd->thread_id=1
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
miguel@hegel:~/dbs/5.0>
[22 Sep 2005 18:37] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/30232
[24 Sep 2005 0:55] Jim Winstead
Fixed in 5.0.14.
[24 Sep 2005 3:19] Paul DuBois
Noted in 5.0.14 changelog.