Bug #20621 query with adddate in group by makes server crash
Submitted: 22 Jun 2006 8:09 Modified: 22 Jun 2006 9:07
Reporter: Michiel Betel Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.021 OS:Linux (Linux fedora core 4)
Assigned to: CPU Architecture:Any

[22 Jun 2006 8:09] Michiel Betel
Description:
This query makes mysql crash with an int 11:

create table jb_betalingen_x3 
select distinct declaratiebestand.id as dbid,categorie,
  diensten_uitbetalingswijze.kosten_toerekening,indiendatum,termijn,adddate(indiendatum,interval aantal_eenheden month) as betaaldatum,
  declvzb_id, percentage_betaald/100*sum(bedrag_hulp)/100 as totaal from (diensten_uitbetalingswijze,declaratiebestand,declaraties,debiteuren) 
left join facturen on factuur_id=fact_id 
left join verzekeraars on verzekeraars.agb=D_agb and verzekeraars.begindatum<=declaratiebestand.datum_uzovi and verzekeraars.einddatum>declaratiebestand.datum_uzovi 
left join stam_declaratiewijzen on stam_declaratiewijzen.declaratiewijze=facturen.declaratiewijze 
where declaratiebestand.id=declaratie_bestand_id and declaraties.debiteur_id=debiteuren.debiteur_id 
and declvzb_id=11842 and (not ( uitbet_LDD="N") or uitbet_LDD is null) 
and instr(diensten_uitbetalingswijze.kosten_toerekening,stam_declaratiewijzen.kosten_toerekening)>0 
and declaraties.status<>"N" 
and declaraties.status<>"P" 
and diensten_uitbetalingswijze.dienst_id=8 
group by declaratiebestand.id,categorie,diensten_uitbetalingswijze.kosten_toerekening, indiendatum ,termijn,adddate(indiendatum,interval aantal_eenheden month)

Replacing the "aantal_eenheden" column with an integer or removing the adddate in the group by makes it run fine.

How to repeat:
run query
[22 Jun 2006 8:33] Tonci Grgin
Hi Michiel and thanks for your problem report.
Can you please provide script with structure of all tables in question along with sample data in form suitable for import so I can test?
[22 Jun 2006 9:07] Tonci Grgin
Hi Michiel. I think you problem is fixed with patch for http://bugs.mysql.com/bug.php?id=19490 since it no longer happens in  5.0.23-debug BK.

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

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

mysql> drop table if exists da;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table da(id int, d date);
Query OK, 0 rows affected (0.01 sec)

mysql> select adddate(d, interval id month),id from da group by id,adddate(d, interval id month);
Empty set (0.02 sec)

mysql> select adddate(d, interval id month),id from da group by id,adddate(d,
    -> interval id month);
Empty set (0.00 sec)

mysql>
[22 Jun 2006 9:10] Tonci Grgin
Sorry, I should have said "Please upgrade to 5.0.23 and test".