MySQL Bugs: #25534: DATEDIFF function not highlighted in query browser

Bug #25534	DATEDIFF function not highlighted in query browser
Submitted:	10 Jan 2007 19:57	Modified:	1 Feb 2007 8:56
Reporter:	Adam Wood	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Query Browser	Severity:	S3 (Non-critical)
Version:	5.0.20-nt	OS:	Windows (Windows XP SP2)
Assigned to:	Mike Lischke	CPU Architecture:	Any

Description:
The DATEDIFF function cannot be used as part of a WHERE subexpression in a query. Although it works fine as part of the field listing in the SELECT portion of a query, if used as a WHERE condition (eg WHERE DATEDIFF(NOW(), `expirydate`) < 0) the query fails with the error:

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 'WHERE DATEDIFF(NOW(), `expirydate`) < 0' at line 10

Furthermore, if typed into a query in MySQL Query Browser, the word "DATEDIFF" is not highlighted in blue like other functions/keywords, implying that it is not recognised by Administrator as a valid function(?).

How to repeat:
Construct any query in which DATEDIFF is used in the WHERE clause. It doesn't appear to make any difference whether either or both arguments are constant terms, functions such as NOW() or CURDATE(), or field names.

Forgot to mention - the error number of the message cited above is #1064

OK, strange...

I now appear to be able to make it work in WHERE clauses, so panic over, but slightly disconcerting is the fact that DATEDIFF along with TO_DAYS and probably a few other date/time functions are still not highlighted in Query Browser. Is this a bug or are only the really key functions highlighted?

changed bug synopsis and category to reflect new situation ...

Thank you for a problem report. Please, specify the exact version of Query Browser used (latest is 1.2.8 beta).

I am using Query Browser version 1.1.20, Administrator version 1.1.9, and MySQL Server version 5.0.20-nt on WinXP Pro SP2.

Please, try to repeat with a newer versions of MySQL Query Browser and MySQL Administrator, from GUI Tools 5.0r9a, and inform about the results.

ave installed Query Browser 1.2.9 and problem persists. Having tried a random selection of other date/time functions, some others appear not to be higlighted, namely DATE_FORMAT and ADDTIME. There are probably others but I have not tried every function to generate a comprehensive list.

This does not appear to be a problem as such but merely an inconsistency in the keyword highlighting system.

Thank you for the report.

Verified as described.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional note: syntax highlighting is only done for reserved words (http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html) and special syntactic elements like strings, numbers, identifiers. How can we highlight every possible function? We would end up with a query with almost everything highlighted, which has the same value as if nothing were highlighted, not to mention the difficulties to get a comprehensive list of all possible functions (what about UDF?) and keep the highlighter up to date.