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:Microsoft Windows (windows 2000)
Assigned to: CPU Architecture:Any

[12 Nov 2003 10:13] [ name withheld ]
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
[12 Nov 2003 11:26] [ name withheld ]
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
[12 Nov 2003 11:40] Paul Dubois
Functions should be written with no space between the function
name and the paren.  See:

http://www.mysql.com/doc/en/Functions.html
[17 Nov 2003 2:43] [ name withheld ]
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
[23 Dec 2004 17:47] Dmitry Katsubo
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?
[23 Dec 2004 18:57] Sergei Golubchik
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.