Bug #10243 -SUM() crash
Submitted: 28 Apr 2005 20:36 Modified: 8 Jun 2005 17:34
Reporter: Ronald Weiss Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.0.4/BK source OS:Microsoft Windows (winXP)
Assigned to: Igor Babaev CPU Architecture:Any

[28 Apr 2005 20:36] Ronald Weiss
Description:
This is very weird, although it is not critical, because there is easy workaround.

A "SELECT -SUM(column) FROM table WHERE condition" query crashes the server, when there are no suitable rows, but only under some special circumstances.

For example this query crashes:
"SELECT -SUM(y) FROM xx WHERE x>0;"
but only when x is primary key.

Here are few very similar queries which do NOT crash (even if x is primary key):
"SELECT -SUM(y) FROM xx WHERE x=0;"
"SELECT -SUM(x) FROM xx WHERE x>0;"
"SELECT SUM(-y) FROM xx WHERE x>0;"
(this last one is the mentioned easy workaround)

How to repeat:
CREATE TABLE xx ( x INT, y INT, PRIMARY KEY(x) );
SELECT -SUM(y) FROM xx WHERE x>0;
[28 Apr 2005 21:40] Miguel Solorzano
Thank you for the bug report.
[28 Apr 2005 22:15] Miguel Solorzano
On Windows this only affects the optimized server and on Linux the
client hangs without to crash the server.

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.6-beta-nt

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

mysql> CREATE TABLE xx ( x INT, y INT, PRIMARY KEY(x) );
Query OK, 0 rows affected (0.08 sec)

mysql> SELECT -SUM(y) FROM xx WHERE x>0;
ERROR 2013 (HY000): Lost connection to MySQL server during query

c:\mysql\bin>mysql -uroot test
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> drop table xx;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE xx ( x INT, y INT, PRIMARY KEY(x) );
Query OK, 0 rows affected (0.10 sec)

mysql> SELECT -SUM(y) FROM xx WHERE x>0;
+---------+
| -SUM(y) |
+---------+
| 0       |
+---------+
1 row in set (0.01 sec)
[31 May 2005 7:13] Disha
The server crashes even when the following statement is executed.

SELECT -avg(f1) FROM tb1 //
[31 May 2005 12:24] Disha
The server crashes even when the following statement is executed.

SELECT -avg(f1) FROM tb1 //
[6 Jun 2005 11:33] Igor Babaev
The cause of the bug is incorrect behavior of the Item_func_neg::decimal_op() function (file: sql\item_func.cpp).
In a correct case this function must return 0 in the case of null_value.

The same bug is detected for the Item_func_abs::decimal_op() function (file: sql\item_func.cpp).

It can be seen with the following script:

create table t (x integer, y decimal(2,4));
insert into t values (1,NULL),(2,NULL);
select -y from t where x=1;
select abs(y) from t where x=1;
select -sum(y) from t where x>0;
select abs(sum(y)) from t where x>0;

mysql> select -y from t where x=1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select abs(y) from t where x=1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select -sum(y) from t where x>0;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select abs(sum(y)) from t where x>0;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: test
+-------------+
| abs(sum(y)) |
+-------------+
| 0           |
+-------------+
1 row in set (6.37 sec)
mysql> select abs(sum(y)) from t where x>0;
ERROR 2013 (HY000): Lost connection to MySQL server during query
[6 Jun 2005 15:15] Igor Babaev
This bug was fixed by the patch for bug #10599.
[8 Jun 2005 17:24] Ronald Weiss
Why did you set status to 'Can't repeat' ??
[8 Jun 2005 17:34] Miguel Solorzano
Because Igor can't repeat with current source, please see
prior comment from him:

[6 Jun 17:15] Igor Babaev <igor@mysql.com>

This bug was fixed by the patch for bug #10599.
[8 Jun 2005 17:50] Ronald Weiss
He wrote: "This bug was fixed...", so I think 'Fixed' would be more appropriate.
But it is unimportant...