MySQL Bugs: #2150: Server crash caused by extra spaces in query

Bug #2150	Server crash caused by extra spaces in query
Submitted:	18 Dec 2003 2:28	Modified:	18 Dec 2003 6:59
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.1	OS:	Linux (Linux)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
When running some long/complex queries, the server tends to crash, only if the 
query is "over-indented" or w/ unnecessary spaces...  
The same query with no extra spaces works fine. 
 
This bug didn't exist in 4.1.0. 
 
 

How to repeat:
Let's create a quite simple DB : 
 
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; 
LOCK TABLES da4adresse WRITE; 
INSERT INTO da4adresse VALUES 
('AB100A','A','AB','100','A','P','1995-01-11','','0000-00-00','O','2002-08-09','P','A','A',0.0,0.0,1,'','0000-00-00',''),('AD078C','A','AD','078','C','P','1995-01-11','','0000-00-00','O','0000-00-00','R','A','B',0.0,0.0,1,'','0000-00-00',''); 
UNLOCK TABLES; 
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; 
LOCK TABLES da4paletier WRITE; 
INSERT INTO da4paletier VALUES 
(1363240,'N0131453','AB100A','16533','LW98305','19C1','94400455','','2003-06-10 
00:00:00','',12.0,12.0,0,4,'','944','',6.00),(1403232,'N0133364','AD078C','16791','LW98305','19C1','94400555','','2003-09-09 
00:00:00','',24.0,0.0,0,4,'','944','',6.00); 
UNLOCK TABLES; 
 
 
Run the query : 
 
SELECT palette, sum(qte_disp) as qte_disp_hetero 
FROM (SELECT p.* 
FROM da4paletier p 
JOIN da4adresse a ON a.adresse = p.adresse 
WHERE p.artrefdist='LW98305' 
AND a.type_adresse = 'R' 
AND a.etat = 'O' 
AND p.qte_affectee = 0 
AND ( p.reservation = '' OR p.reservation = '' OR p.reservation = '' ) 
AND p.particularite = '' 
ORDER BY p.reservation DESC) p 
GROUP BY palette 
order by qte_disp_hetero DESC 
 
It runs fine. 
 
Run the same one, with extra spaces added : 
 
SELECT palette, sum(qte_disp) as qte_disp_hetero 
                                FROM (SELECT p.* 
                                        FROM da4paletier p 
                                        JOIN da4adresse a ON a.adresse = p.adresse 
                                        WHERE p.artrefdist='LW98305' 
                                        AND a.type_adresse = 'R' 
                                        AND a.etat = 'O' 
                                        AND p.qte_affectee = 0 
                                        AND ( p.reservation = '' OR p.reservation = '' OR 
p.reservation = '' ) 
                                        AND p.particularite = '' 
                                        ORDER BY p.reservation DESC) p 
                                GROUP BY palette 
                                ORDER BY qte_disp_hetero DESC 
 
Crash !

copy & paste issues... : 
To actually repeat this bug, you must add at least about 40 spaces before each 
line.

I cannot repeat this using 4.1.2, regardless of how many spaces I use.

If you have any additional information regarding this, let us know and we will test again.  (such as whether you are using our 4.1.1 binary release...)