Description:
When running some complex / long queries, especially including unnamed views,
the server may crash.
It appears with 4.1.1 , and occurs with 5.0.0 too.
Doesn't care if there's any data or not in the tables.
How to repeat:
DROP TABLE IF EXISTS da4paletier;
CREATE TABLE da4paletier (
raccrochage int(11) NOT NULL default '0',
palette varchar(8) NOT NULL default '',
adresse varchar(6) NOT NULL default '',
no_dam_lig varchar(9) NOT NULL default '',
artrefdist varchar(10) NOT NULL default '',
bain varchar(10) NOT NULL default '',
no_oa varchar(8) NOT NULL default '',
particularite char(2) NOT NULL default '',
date_reception datetime NOT NULL default '0000-00-00 00:00:00',
reservation varchar(16) NOT NULL default '',
qte_disp decimal(8,1) NOT NULL default '0.0',
qte_affectee decimal(8,1) NOT NULL default '0.0',
longpiece int(11) NOT NULL default '0',
nb_pieces int(11) NOT NULL default '0',
picking_flottant char(1) NOT NULL default '',
code_fournisseur varchar(4) NOT NULL default '',
origine_mouvement varchar(10) NOT NULL default '',
unite_condit decimal(6,2) NOT NULL default '0.00',
PRIMARY KEY (raccrochage),
KEY i_paletier_flottant (picking_flottant),
KEY i_paletier_palette (palette),
KEY i_paletier_artrefdist (artrefdist),
KEY i_paletier_adresse (adresse)
) TYPE=MyISAM DEFAULT CHARSET=latin1;
DROP TABLE IF EXISTS da4adresse;
CREATE TABLE da4adresse (
adresse varchar(6) NOT NULL default '',
ssmag char(1) NOT NULL default '',
ssmag_allee char(2) NOT NULL default '',
casier char(3) NOT NULL default '',
niveau char(1) NOT NULL default '',
cote char(2) NOT NULL default '',
date_creation date NOT NULL default '0000-00-00',
status char(2) NOT NULL default '',
date_maj_status date NOT NULL default '0000-00-00',
etat char(2) NOT NULL default '',
date_maj_etat date NOT NULL default '0000-00-00',
type_adresse char(2) NOT NULL default '',
type_palette char(2) NOT NULL default '',
accessibilite char(2) NOT NULL default '',
seuil decimal(8,1) NOT NULL default '0.0',
qte_max decimal(8,1) NOT NULL default '0.0',
nb_casiers int(11) NOT NULL default '0',
exercice char(2) NOT NULL default '',
date_dern_invent date NOT NULL default '0000-00-00',
flag_mission char(2) NOT NULL default '',
PRIMARY KEY (adresse),
KEY i_adresse_datecreation (date_creation),
KEY i_adresse_typeadresse (type_adresse),
KEY i_adresse_status (status),
KEY i_adresse_typepalette (type_palette),
KEY i_adresse_etat (etat)
) TYPE=MyISAM DEFAULT CHARSET=latin1;
SELECT substring(adr.adresse,1,2) as allee, count(adr.adresse) as nb_adr_pick
FROM (select a.*
from da4adresse a
left outer join da4paletier p ON p.adresse = a.adresse
where a.adresse LIKE 'A%'
AND a.etat = 'L'
AND a.status = ' '
AND a.type_adresse = 'P'
AND substring(a.adresse,6,1) = 'A'
AND p.adresse IS NULL ) adr
GROUP BY allee
ORDER BY nb_adr_pick DESC ;
