MySQL Bugs: #1819: sum (1) doesn't works, sum(1) works

Bug #1819	sum (1) doesn't works, sum(1) works
Submitted:	12 Nov 2003 10:13	Modified:	23 Dec 2004 18:57
Reporter:	[ name withheld ]	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Embedded Library ( libmysqld )	Severity:	S3 (Non-critical)
Version:	4.0.16-nt	OS:	Windows (windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
i tried to make a simple select-statement to get the average of a column. but later it needed a sum of the same column. the first statement works. but the second not.
the mistake lies in the number of the spaces between sum and (. if there was no space between it worked.

there are more functions with the same behaviour: min, max, count, maybe more :-/

that shows the mysql-console:
mysql> select sum (1);
ERROR 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 '(1)' at line 1
mysql> select sum(1);
+--------+
| sum(1) |
+--------+
|   NULL |
+--------+
1 row in set (0.00 sec)

thank you :) and good luck

your michael

How to repeat:
select avg (1); // works
select sum (1); // doesn't work
select sum(1); // works

Suggested fix:
don't know how ;) but sum must work like avg

same problem if you use now, date_format, ...
maybe it is a global problem of all functions. some work and some not. it can be every function/procedure not only the math oder date functions

Functions should be written with no space between the function
name and the paren.  See:

http://www.mysql.com/doc/en/Functions.html

should be written ... yes ... but why you can write it once in this way and the other time in that way. it's better to say no spaces for every function or make allways spaces possible. that thing was the cause for being stressed for 2 hours. :-( please solve this problem. i have to work with it and cannot always thing of how often i must write spaces and where. that is not good for a programmer who writes in more than 8 languages ... :(((

thx

i hope this will get better ...

your michael

Dear Paul DuBois! Your comment seems to contradict with SQL'92 specification (http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt) which says in 5.2 (on page 86):

=== quote ===
         5) Any <token> may be followed by a <separator>. A <nondelimiter
            token> shall be followed by a <delimiter token> or a <separa-
            tor>. If the Format does not allow a <nondelimiter token> to be
            followed by a <delimiter token>, then that <nondelimiter token>
            shall be followed by a <separator>.
=== end of quote ===

For example, "COUNT" is <reserved word> is <key word> is <nondelimiter token>, so, it may be followed by a <separator>.

Am I right here?

Almost.

In MySQL function names are not reserved words - that's why they cannot be followed by a space (if used as a function name, that is).

If you start mysqld with --ansi switch function names will be reserved words, and you will be able to put a <delimiter> after them. Of course, you won't be able to create a table or a column named, e.g. SUM then.