MySQL Bugs: #10243: -SUM() crash

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:	Windows (winXP)
Assigned to:	Igor Babaev	CPU Architecture:	Any

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;

Thank you for the bug report.

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)

The server crashes even when the following statement is executed.

SELECT -avg(f1) FROM tb1 //

The server crashes even when the following statement is executed.

SELECT -avg(f1) FROM tb1 //

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

This bug was fixed by the patch for bug #10599.

Why did you set status to 'Can't repeat' ??

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.

He wrote: "This bug was fixed...", so I think 'Fixed' would be more appropriate.
But it is unimportant...