MySQL Bugs: #30989: too many complex ORDER BY-statements crashes server

Bug #30989	too many complex ORDER BY-statements crashes server
Submitted:	13 Sep 2007 2:39	Modified:	13 Sep 2007 11:46
Reporter:	Georg Kapeller	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.0.45	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	complex, crash, order by

Description:
following query executed on the testdatabase will cause mysql-server crash:

SELECT kaputt.* 
FROM kaputt
	LEFT JOIN wohnungen AS wo_sort ON zi_ref_wo_id = wo_sort.wo_id
	LEFT JOIN objekte ON zi_wo_ref_ob_id = objekte.ob_id
	LEFT JOIN wohnungen ON zi_ref_wo_id = wohnungen.wo_id
	LEFT JOIN zimmer ON kaputt.zi_id = zimmer.zi_id
				
WHERE cod_status_zuweisbar = 1
AND beziParamsMatch = ''
				

ORDER BY 
IF(beziParamsMatch = '', CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), '0') DESC,
IF(wo_sort.wo_cod_kategorie_wr = 31, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 32, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 33, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 41, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 40, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 39, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 38, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 37, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 8, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 34, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 22, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 21, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 20, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC, 
IF(wo_sort.wo_cod_kategorie_wr = 42, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC,
IF(wo_sort.wo_cod_kategorie_wr = 19, CONCAT(objekte.ob_name, wohnungen.wo_name, zimmer.zi_name), 0) DESC

on 5.0.45 i had to reboot the machine, as the service stopped reacting.
on 5.0.27 service restart is possible.

happend with both, MyISAM and MEMORY storage engine.

seems to be some kind of buffer-overflow. but maybe it is just one row with specific data that causes the server to crash, executing the same query on table kaputt2:
CREATE TABLE kaputt2 SELECT * FROM kaputt LIMIT 10
workes fine.

the query contains 16 ORDER-BY expressions. if the last or whatever ORDER-BY expression is deleted, so that no more than 15 are left, query works with testdata.

no information about the crash shows up in the error-log.

How to repeat:
use database dbcrash from the uploaded file and execute the query from above.

Thank you for the bug report. I can't to repeat the crash with a server
build from current source server, so looks somehow it was fixed:

<cut>

*************************** 194. row ***************************
        zi_wo_ref_ob_id: 1
           zi_ref_wo_id: 1
                  zi_id: 1
                zi_name: 1
        cod_typ_wohnung: 3
       cod_kategorie_wr: 5
   cod_status_zuweisbar: 1
            $offsetLeft: +5M 7T
           $offsetRight:
             offsetLeft: 157
            offsetRight: NULL
        offsetLeft_dat_: 2008-02-01
       offsetRight_dat_: NULL
             statusInfo: (Z)<>%
        beziParamsMatch:
beziParamsMatchExtended:
      _f_params_matched: 1
194 rows in set (0.17 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.50-nt |
+-----------+
1 row in set (0.00 sec)

mysql>

ok, i'll try it when 5.0.50 gets available.
but because of Bug #30371 i can't update to .45 or .50, so the only fix for now seems to be reducing the number of statements in the order by clause.

thanks for the quick help.