MySQL Bugs: #2936: Crash the server

Bug #2936	Crash the server
Submitted:	24 Feb 2004 8:43	Modified:	17 Mar 2004 22:51
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1	OS:	Windows (Windows 2000 Advanced Server)
Assigned to:		CPU Architecture:	Any

Description:
Hello!
Sorry for my English because it is very poor.

I'm having trouble while trying to run this query:

SELECT IDOFERTA , ARTICULO , NOMBRE FROM ((SELECT     articulos.IDARTICULO,ofertas.IDOFERTA,ofertas.NOMBRE,ofertas.PRECIO,ofertas.FECHAINICIO,ofertas.FECHAFIN,ofertas.TEXTO,articulos.ARTICULO,artcat.IDCATEGORIA FROM articulos 
INNER JOIN artcat ON articulos.IDARTICULO = artcat.IDARTICULO 
INNER JOIN ofertas ON articulos.IDARTICULO = ofertas.IDARTICULO 
WHERE ofertas.FECHAINICIO <= CURDATE() AND ((ofertas.FECHAFIN >= CURDATE()) OR (ofertas.FECHAFIN IS NULL))) AS ofertasCat)  ;

it causes the mysqld service to crash with no error report (after the results are correctly returned).
I've tried the query on both jdbc connector and MySQL control center with identical results.

How to repeat:
Allways you try to run this query causes mysquld service to crash.

If I drop the third condition, it works ok. Same if I use a different operator than "IS NULL", or if I drop the first two conditions.

mysqld trace

Attachment: mysqld_partial.trace (application/octet-stream, text), 198.15 KiB.

Not enough information was provided for us to be able
to handle this bug. Please re-read the instructions at
http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it
to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Additional info:

Please provide structure of your tables as shown by 
SHOW CREATE TABLE

as well as some minimum data which is enough to reproduce the problem

Ok, i post an script where i put SQL code for create the tables and to run the query. If you execute that script without data in the table the server crashes. If you put any data in the tables the query returns the correct result but crashes too.

CREATE TABLE Articulos (
	IDARTICULO int NOT NULL ,
	ARTICULO varchar (25) NOT NULL ,
	DESCRIPCION text ,
	INFOTECNICA text  ,
	FOTO blob,
	PRECIO float NOT NULL ,
	UNIDADES int DEFAULT 0 NOT NULL ,
	REFERENCIA varchar (10) NOT NULL 
) TYPE = InnoDB;

CREATE TABLE ArtCat (
	IDCATEGORIA int NOT NULL ,
	IDARTICULO int NOT NULL 
) TYPE = InnoDB;

CREATE TABLE Ofertas (
	IDOFERTA int NOT NULL,
	IDARTICULO int NOT NULL,
	PRECIO float NOT NULL,
	FECHAINICIO datetime NOT NULL,
	FECHAFIN datetime,
	NOMBRE varchar(50) NOT NULL,
	TEXTO text
)TYPE = InnoDB;

ALTER TABLE Articulos  ADD 
	CONSTRAINT PK_Articulos PRIMARY KEY 
	(
		IDARTICULO
	);
	
	
ALTER TABLE ArtCat ADD 
	CONSTRAINT PK_ArtCat PRIMARY KEY 
	(
		IDCATEGORIA,
		IDARTICULO
	);

CREATE INDEX IndiceArt ON ArtCat (IDARTICULO);
ALTER TABLE ArtCat ADD 
	CONSTRAINT FK_ArtCat_Articulos FOREIGN KEY 
	(
		IDARTICULO
	) REFERENCES Articulos (
		IDARTICULO
	);

CREATE INDEX IndiceOf ON Ofertas(IDARTICULO);
ALTER TABLE Ofertas ADD
	CONSTRAINT FK_Ofertas_Art FOREIGN KEY(
		IDARTICULO
	)REFERENCES Articulos(
		IDARTICULO
	);

SELECT IDOFERTA , ARTICULO , NOMBRE FROM ((SELECT    
articulos.IDARTICULO,ofertas.IDOFERTA,ofertas.NOMBRE,ofertas.PRECIO,ofertas.FECHAINICIO,ofertas.FECHAFIN,ofertas.TEXTO,articulos.ARTICULO,artcat.IDCATEGORIA
FROM articulos 
INNER JOIN artcat ON articulos.IDARTICULO = artcat.IDARTICULO 
INNER JOIN ofertas ON articulos.IDARTICULO = ofertas.IDARTICULO 
WHERE ofertas.FECHAINICIO <= CURDATE() AND ((ofertas.FECHAFIN >= CURDATE()) OR
(ofertas.FECHAFIN IS NULL))) AS ofertasCat)  ;

It is still not repeatable. With your tables I don't get any crash no matter if tables are empty or there are some rows. Here is for example result after inserting single row with default values into each table:

mysql> SELECT IDOFERTA , ARTICULO , NOMBRE FROM ((SELECT
    -> Articulos.IDARTICULO,Ofertas.IDOFERTA,Ofertas.NOMBRE,Ofertas.PRECIO,Ofertas.FECHAINICIO,Ofertas.FECHAFIN,Ofertas.TEXTO,Articulos.ARTICULO,ArtCat.IDCATEGORIA
    -> FROM Articulos
    -> INNER JOIN ArtCat ON Articulos.IDARTICULO = ArtCat.IDARTICULO
    -> INNER JOIN Ofertas ON Articulos.IDARTICULO = Ofertas.IDARTICULO
    -> WHERE Ofertas.FECHAINICIO <= CURDATE() AND ((Ofertas.FECHAFIN >=
    -> CURDATE()) OR
    -> (Ofertas.FECHAFIN IS NULL))) AS OfertasCat)  ;
+----------+----------+--------+
| IDOFERTA | ARTICULO | NOMBRE |
+----------+----------+--------+
|        0 |          |        |
+----------+----------+--------+

I was able to repeat the crash reported with the version 4.1.1a,
however with a server built from BK tree 5 days older the crash
not happens anymore. Please wait the next release or if you
have conditions you can build from our source following the
instructions in our Manual.

Thank you for the bug report.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html