MySQL Bugs: #13533: Date comparision causes MySql shutdown

Bug #13533	Date comparision causes MySql shutdown
Submitted:	27 Sep 2005 18:33	Modified:	30 Oct 2005 17:46
Reporter:	Felipe Gauúcho	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.0.2-alpha-nt	OS:	Windows (Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
The following store procedure compilation causes the shutdown of the MySql:

DELIMITER '/';
CREATE PROCEDURE INSERT_JOB (title VARCHAR(45), body VARCHAR(255), salary DECIMAL(7,2), owner VARCHAR(45), email VARCHAR(45), workload INTEGER UNSIGNED, typeId INTEGER UNSIGNED)
BEGIN
   DECLARE today DATE;
   DECLARE statusNewId INTEGER;
   DECLARE jobId INTEGER;
   DECLARE sectionId INTEGER;
   SET today = NOW();
   SELECT id INTO statusNewId FROM status s WHERE s.label='NEW';
   INSERT INTO job (title, body, salary, owner, email, workload, date, jobType, status) VALUES(title, body, salary, owner, email, workload, today, typeId, statusNewId);
   SELECT id INTO jobId FROM job j WHERE j.owner=owner AND j.title=title AND j.date=today;
   SELECT id INTO sectionId FROM section c WHERE c.label='JOB';
   INSERT INTO pending (advertisementId, section) VALUES (jobId, sectionId);
END/
DELIMITER ';'/

How to repeat:
download all files from the bellow url and run the createdb.bat:

https://cejug-classifieds.dev.java.net/source/browse/cejug-classifieds/web-app/WEB-INF/sql...

OBS: these files are just MySql scripts.

the line where the bug occours is tat one:

SELECT id INTO jobId FROM job j WHERE j.owner=owner AND j.title=title AND j.date=today;

if one comment the j.date=today, everything runs ok.

SELECT id INTO jobId FROM job j WHERE j.owner=owner AND j.title=title; # AND j.date=today;

Sorry i was unable for to download those files from that link
you showed, could you please attach them here using the Files tab.

Also your MySQL version is very older, are you tried to test with
latest version that you can download from our web site?

Thanks in advance.

the scripts RAR file

Attachment: sql.rar (application/octet-stream, text), 4.50 KiB.

ok, the files are attached.

a question:

I changed the statement:

                         j.date=today

to 

                         DATEDIFF(j.date, today)='00:00:00.000000'

and everything runs ok. 

a question: can I compare dates ion MySql using '=' operator ?

If it is a syntax failure from my part, I ask for excuses.

I was unable to repeat the server crash with current server release
version. So please upgrade your version, also not upgrade installing
the new version over the top of the older.

c:\mysql\bin>mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.13-rc-nt

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

mysql> create database classifieds;
Query OK, 1 row affected (0.00 sec)

mysql> use classifieds;
Database changed
mysql> source c:/1/sql/mysql502.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

<cut>

mysql> source c:/1/sql/procedures.mysql502.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)

<cut>

mysql> source c:/1/sql/test.data.mysql502.sql
Query OK, 1 row affected (0.03 sec)

<cut>

mysql> source c:/1/sql/test.data.mysql502.sql
Query OK, 1 row affected (0.03 sec)

,cut>

Query OK, 1 row affected (0.01 sec)

ERROR 1172 (42000): Result consisted of more than one row
ERROR 1172 (42000): Result consisted of more than one row
ERROR 1062 (23000): Duplicate entry '1-1' for key 1
ERROR 1062 (23000): Duplicate entry '1-2' for key 1
ERROR 1062 (23000): Duplicate entry '1-4' for key 1
ERROR 1062 (23000): Duplicate entry '2-2' for key 1
ERROR 1172 (42000): Result consisted of more than one row
ERROR 1062 (23000): Duplicate entry '1-1' for key 1
ERROR 1062 (23000): Duplicate entry '1-2' for key 1
ERROR 1062 (23000): Duplicate entry '1-5' for key 1
mysql>

Thank you for the feedback.

sorry, I sent you a commented version of the bug.

ont the file 'procedures.mysql502.sql', please remove the comment symbol '#'

SELECT id INTO jobId FROM job j WHERE j.owner=owner AND j.title=title[[;#]]here] AND DATE_SUB(j.date,today) = 0;   

the bug generator line is:

SELECT id INTO jobId FROM job j WHERE j.owner=owner AND j.title=title AND j.date=today;

Could you please send again the file 'procedures.mysql502.sql' because
I was unable to find the comment symbol you said to remove.

Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".