Bug #10652 select/round/group query crashes server

• Submitted: 16 May 2005 1:22
• Modified: 16 May 2005 1:43
• Reporter: scott fagg
• Status: Closed
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 5.0.4
• OS: Linux (linux)
• CPU Architecture: Any

[16 May 2005 1:22] scott fagg

Description:
Following query crashes server :

select round(project_num/1000 - 0.5) *1000 as bands from plans

Where the 'project_num' field is an integer with many values between 1 and 100,000. Crashes on both myisam and innodb tables.

Happens on large table ( 2000+ records ) but i cannot reproduce it on a trivial table (10 records).

How to repeat:
CREATE TABLE `plans` (
  `project_num` int(6) NOT NULL default '0',
  KEY `project_num` (`project_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `plans` (`project_num`) VALUES (0);
INSERT INTO `plans` (`project_num`) VALUES (12);
     
select round(project_num/1000 - 0.5) *1000 as bands from plans;

[16 May 2005 1:43] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

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

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

mysql> CREATE TABLE `plans` (
    ->   `project_num` int(6) NOT NULL default '0',
    ->   KEY `project_num` (`project_num`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> INSERT INTO `plans` (`project_num`) VALUES (0);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO `plans` (`project_num`) VALUES (12);
Query OK, 1 row affected (0.00 sec)

mysql>      
mysql> select round(project_num/1000 - 0.5) *1000 as bands from plans;
+-------+
| bands |
+-------+
| -1000 |
| 0     |
+-------+
2 rows in set (0.02 sec)

mysql>