MySQL Bugs: #13361: FORMAT function on NULL DECIMAL field crashes MySQL service

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

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  :-)

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>

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

Fixed in 5.0.14.

Noted in 5.0.14 changelog.