MySQL Bugs: #14124: DATEDIFF does not work

Bug #14124	DATEDIFF does not work
Submitted:	18 Oct 2005 22:18	Modified:	26 Oct 2005 17:51
Reporter:	Zdenek Rykala	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.14	OS:	Windows (Windows XP SP2)
Assigned to:	MySQL Verification Team	CPU Architecture:	Any

Description:
I have tables with two date colums. 'datum_od', 'datum_do' (type of colums is date) if I use following SQL command, it returns an error.

SELECT * FROM `termin` WHERE DATEDIFF(datum_do,datum_od) < 10

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( datum_do , datum_od ) < 10  LIMIT 0, 30' at line 1 

When I use static values on SQL commands it works fine.
SELECT DATEDIFF('2005-11-01','2005-10-25') returns 7

When I use column name, SQL returns an error.

Is it BUG or feature?

How to repeat:
details are in description.

Could you please provide a complete test case, I was unable for
to repeat the behavior you had reported:

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

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

mysql> CREATE TABLE `termin` (datum_do DATE, datum_od DATE);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO `termin` VALUES ('2005-11-01','2005-10-25');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT DATEDIFF(datum_do,datum_od) FROM `termin`;
+-----------------------------+
| DATEDIFF(datum_do,datum_od) |
+-----------------------------+
|                           7 |
+-----------------------------+
1 row in set (0.03 sec)

SELECT DATEDIFF(datum_do,datum_od) FROM `termin`;
SELECT DATEDIFF('2005-09-20','2005-09-01') FROM `termin`;
This requests works on a mysql client...

But when it's executed in a PHP script or by the way through PhpMyAdmin,
Php answer : FUNCTION test.DATEDIFF does not exist

With PHP
SELECT DATEDIFF('2005-09-20','2005-09-01');
is OK, but 
SELECT DATEDIFF('2005-09-20','2005-09-01') FROM `termin`;
generate errors

I tested using PhpMyAdmin and looking the log, I noticed that the
query is sent to the server like below:

		      8 Init DB     test
		      8 Query       SELECT DATEDIFF ( datum_do , datum_od ) FROM `termin`  LIMIT 0, 30
		      8 Init DB     test

notice the space between DATEDIFF and ( datum_do , datum_od ) and then
the server throws the error message. So PHP need to be fixed for not to change
the original statement or you can use the sql_mode how I show you below:

mysql> CREATE TABLE `termin` (datum_do DATE, datum_od DATE);
Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO `termin` VALUES ('2005-11-01','2005-10-25');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT DATEDIFF ( datum_do , datum_od ) FROM `termin`  LIMIT 0, 30
    -> ;
ERROR 1305 (42000): FUNCTION test.DATEDIFF does not exist
mysql> SELECT DATEDIFF( datum_do , datum_od ) FROM `termin`  LIMIT 0, 30;
+---------------------------------+
| DATEDIFF( datum_do , datum_od ) |
+---------------------------------+
|                               7 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = "IGNORE_SPACE";
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT DATEDIFF ( datum_do , datum_od ) FROM `termin`  LIMIT 0, 30;
+----------------------------------+
| DATEDIFF ( datum_do , datum_od ) |
+----------------------------------+
|                                7 |
+----------------------------------+
1 row in set (0.00 sec)

mysql>

There is 2 bugs with this DATEDIFF function:

SELECT DATEDIFF (datum_do, datum_od) FROM `termin`
- This request don't work because there is a space character between 'DATEDIFF' and '('
SELECT DATEDIFF(datum_do, datum_od) FROM `termin`
- This request don't work because there is not LIMIT
SELECT DATEDIFF(datum_do, datum_od) FROM `termin` LIMIT 0,30
- This request work :p

Hi, i've a similar bug on a  classic distribution (mysql 3.23.58 version) stored on a linux server (fedora)

can i bypass this bug by upgrading to a newer mysql version ? and what version ?

select datediff('2005-11-12','1996-11-12') returns me a sql syntax error

#1064 - You have an error in your SQL syntax near '('1997-12-31 23:59:59','1997-12-30')' at line 1 

 i can jus suppose that datediff is not recognized as a valid function.

thanks for your feedback.

the DATEDIFF() function started on the v4.1.1 of mySQL.

Nordin.