MySQL Bugs: #14009: use of abs() on null value causes problems with filesort

Bug #14009	use of abs() on null value causes problems with filesort
Submitted:	13 Oct 2005 17:05	Modified:	1 Nov 2005 19:11
Reporter:	Mat Trudel	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.10/4.1.16 BK source	OS:	apple-darwin8.1.0 on powerpc/Linux
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
A properly formatted SELECT query, using the TIMEDIFF function with a 0 argument, can cause the mysql daemon to crash unexpectedly.

How to repeat:
Run the following SQL (any method of running it seems to crash the server, including console, gui, and php):

---------------------------

CREATE TABLE `foo` (
`url` varchar(250),
`timestamp` datetime NOT NULL,
INDEX `url` (`url`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO foo (`url`, `timestamp`) VALUES ('http://www.foo.com/', NOW());

SELECT *, TIMEDIFF(timestamp, 0) as timediff FROM foo WHERE url='http://www.foo.com/' ORDER BY ABS(TIMEDIFF(timestamp, 0))

---------------------------

and witness how mysqld crashes upon trying to process the SELECT query.

(yes, i know there's not a primary key in the above table, however there is one in the table where I first discovered this bug. The above is the smallest example I can reproduce.)

In particular, the SELECT must match a row for the above to crash. The rest of the above isn't particular, it's just setup for the SELECT statement.

After further research, it seems that it may be the ABS call which is causing the crash to occur. Removing the ABS call around the second TIMEDIFF stops the crash from occurring.

I'm not sure of the correct way to accomplish what I'm trying to do (order the results by their proximity, either before or after, a given time), and was experimenting with solutions when I uncovered this bug.

Thank you for the bug report.

This bug not affects 5.0.

miguel@hegel:~/dbs/4.1> bin/mysqladmin -uroot create db15
miguel@hegel:~/dbs/4.1> bin/mysql -uroot db15
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.16-debug-log

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

mysql> CREATE TABLE `foo` (
    -> `url` varchar(250),
    -> `timestamp` datetime NOT NULL,
    -> INDEX `url` (`url`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> 
mysql> INSERT INTO foo (`url`, `timestamp`) VALUES ('http://www.foo.com/', NOW());
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT *, TIMEDIFF(timestamp, 0) as timediff FROM foo WHERE
    -> url='http://www.foo.com/' ORDER BY ABS(TIMEDIFF(timestamp, 0));
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> 

/home/miguel/dbs/4.1/libexec/mysqld: ready for connections.
Version: '4.1.16-debug-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
[New Thread 1156033456 (LWP 11996)]
[Thread 1156033456 (zombie) exited]
[New Thread 1156033456 (LWP 12123)]

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 1156033456 (LWP 12123)]
0x40254ac7 in memset () from /lib/tls/libc.so.6

(gdb) bt full
#0  0x40254ac7 in memset () from /lib/tls/libc.so.6
No symbol table info available.
#1  0x08c561f0 in ?? ()
No symbol table info available.
#2  0x08154a75 in Field_string::unpack (this=0x8cb9fff, to=0x8c58499 "", from=0x8ca9753 ' ' <repeats 200 times>...) at field.cc:5149
        length = 4864
#3  0x081ec197 in unpack_addon_fields (addon_field=0x8c5db78, buff=0x8ca9750 ' ' <repeats 200 times>...) at filesort.cc:1327
        field = <value optimized out>
        addonf = (SORT_ADDON_FIELD *) 0x8c5db78
#4  0x081e9993 in rr_unpack_from_buffer (info=0x8c5ba14) at records.cc:317
        table = <value optimized out>
#5  0x081b192a in join_init_read_record (tab=0x8c5b9f0) at sql_select.cc:6452
No locals.
<cut>

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

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

mysql> CREATE TABLE `foo` (
    -> `url` varchar(250),
    -> `timestamp` datetime NOT NULL,
    -> INDEX `url` (`url`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.08 sec)

mysql> 
mysql> INSERT INTO foo (`url`, `timestamp`) VALUES ('http://www.foo.com/', NOW());
Query OK, 1 row affected (0.01 sec)

mysql> 
mysql> SELECT *, TIMEDIFF(timestamp, 0) as timediff FROM foo WHERE
    -> url='http://www.foo.com/' ORDER BY ABS(TIMEDIFF(timestamp, 0));
+---------------------+---------------------+----------+
| url                 | timestamp           | timediff |
+---------------------+---------------------+----------+
| http://www.foo.com/ | 2005-10-13 15:03:34 | NULL     |
+---------------------+---------------------+----------+
1 row in set (0.01 sec)

mysql>

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/31204

Fixed in 4.1.16 and 5.0.16.

Noted in 4.1.16, 5.0.16 changelogs.

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/31729

The pushed fix was wrong (The problem was not with ABS() and a lot of other
functions would also have a similar problems in the same context)
The problem was that timediff() didn't report to ABS that it may return a null value.
A proper fix is now pushed into 4.1 tree and will be merged to 5.0 shortly.
The fix also fixes a similar problem in addtime()

The pushed fix was wrong (The problem was not with ABS() and a lot of other
functions would also have a similar problems in the same context)
The problem was that timediff() didn't report to ABS that it may return a null value.
A proper fix is now pushed into 4.1 tree and will be merged to 5.0 shortly.
The fix also fixes a similar problem in addtime()

Revised the 4.1.16, 5.0.16 changelog entries.