MySQL Bugs: #20805: select with order by date

Bug #20805	select with order by date_add crashes the server
Submitted:	1 Jul 2006 16:25	Modified:	5 Jul 2006 13:55
Reporter:	Dag Nygren	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.22	OS:	Linux (linux)
Assigned to:		CPU Architecture:	Any

Description:
The following select will crash the server. Worked in 5.0.8. Haven't tried the intermediate releases.

select DATE_ADD(made,INTERVAL payment_terms DAY) from processitem,document_files,proces
ses,companies WHERE ref_no=pr_process_no AND pr_process_no=process_no AND processes.customer=companies.cust_no AN
D made>="2006-6-01" AND made<"2006-7-01" AND dtype="Invoice" GROUP by processitem.pr_invoice ORDER BY DATE_ADD(ma
de,INTERVAL payment_terms DAY)

leaving out the ORDER BY from the select finishes successfully.

The sym resolved err file contains the following:

Version: '5.0.22-log'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=4
max_connections=100
threads_connected=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x89f6568
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xb6820828, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8170dbc handle_segfault + 668
0xb7ec872e _end + -1348633578
0x80e919a Item_field::val_int() + 10
0x8130ed1 Item_func_date_format::format_length(String const*) + 177
0x81332c2 Item_date_add_interval::eq(Item const*, bool) const + 242
0x81b1a7a find_item_in_list(Item*, List<Item>&, unsigned int*, find_item_error_report_type, bool*) + 330
0x81c33d8 JOIN::rollup_send_data(unsigned int) + 2472
0x81cb3fe JOIN::prepare(Item***, st_table_list*, unsigned int, Item*, unsigned int, st_order*, st_order*, Item*,
st_order*, st_select_lex + 718
0x81dd271 mysql_select(THD*, Item***, st_table_list*, unsigned int, List<Item>&, Item*, unsigned int, st_order*,
st_order*, Item*, st_ord + 1489
0x81dd679 handle_select(THD*, st_lex*, select_result*, unsigned long) + 297
0x8187a53 mysql_execute_command(THD*) + 6003
0x81904e4 mysql_parse(THD*, char*, unsigned int) + 564
0x8190b38 dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1352
0x81927e6 handle_one_connection + 2198
0xb7ec3881 _end + -1348653719
0x420e33a7 _end + 968829071
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve th
e stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8a03630 = select DATE_ADD(made,INTERVAL payment_terms DAY) from processitem,document_files,proces
ses,companies WHERE ref_n
o=pr_process_no AND pr_process_no=process_no AND processes.customer=companies.cust_no AND made>="2006-6-01" AND m
ade<"2006-7-01" AND dtype
="Invoice" GROUP by processitem.pr_invoice ORDER BY DATE_ADD(made,INTERVAL payment_terms DAY)
thd->thread_id=4
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.

How to repeat:
Repeat the select

Thank you for the report.

Please, also provide us output of SHOW CREATE TABLE processitem \G statement for every table from your query.

Added the requested data some days ago. Is that enough?

Thank you for your bug report.

I can repeat it as you said using mysql-standard-5.0.22, but can not using last developer source tree.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://www.mysql.com/doc/en/Installing_source_tree.html

Ok,

so it is fixed now. Very good.
I don't need the repos access as I found a way to work around the problem in my reporting script.

Thanks for reacting (and confirming) though.

I've run into a similar problem with 5.0.22 (it's okay in 5.0.45) and it's not just due to a DATE_ADD in the ORDER BY.

Here are additional details if it helps in anyway such as for test cases.

It only happens when the following are true:

1) DATE_ADD is in the select
2) DATE_ADD is also in the group by and/or order by
3) The interval in the group by / order by DATE_ADD is calculated based on a selected column

This happens against both InnoDB and MyISAM tables.  It does not matter if there is data in the tables being queried.  DATE_SUB not surprisingly causes the same problem.

Below are examples to reproduce.  They are nonsense queries but it was the easiest way to come up with the simplest test cases.

Create table Foo (
   timex DateTime,
   numx Smallint
) ENGINE = InnoDB;

-- no crash
SELECT DATE_ADD(timex, INTERVAL 1 DAY)
FROM Foo
GROUP BY DATE_ADD(timex, INTERVAL 1 DAY);

-- no crash
SELECT DATE_ADD(timex, INTERVAL 1 DAY)
FROM Foo
ORDER BY DATE_ADD(timex, INTERVAL 1 DAY);

-- no crash
SELECT timex
FROM Foo
GROUP BY DATE_ADD(timex, INTERVAL 1 DAY);

-- crash
SELECT DATE_ADD(timex, INTERVAL 1 DAY)
FROM Foo
GROUP BY DATE_ADD(timex, INTERVAL numx DAY);

-- crash
SELECT DATE_ADD(timex, INTERVAL 1 DAY)
FROM Foo
ORDER BY DATE_ADD(timex, INTERVAL numx DAY);