Bug #17487 CASE and IF() used in a group function (e.g. SUM ) results in error #1305

• Submitted: 16 Feb 2006 17:20
• Modified: 19 Feb 2006 9:25
• Reporter: Oswaldt Oldenburg
• Status: Not a Bug
• Category: MySQL Server
• Severity: S4 (Feature request)
• Version: MySQL 5.0.18-nt
• OS: Windows (Windows XP Professional with SP2)
• CPU Architecture: Any

[16 Feb 2006 17:20] Oswaldt Oldenburg

Description:
The use of a CASE statement or the IF() function within some/most(?) of the group functions, like SUM(), results in the error "#1305 - FUNCTION ... does not exist". The AVG() function works correct.

e.g.

SELECT 
SUM
(
CASE x
  WHEN 1 THEN 2
  WHEN 2 THEN 4
  ELSE 8
END
)
FROM ..

How to repeat:
/*

MySQL 5.0.18-nt

Windows XP Professional with SP2

*/

CREATE DATABASE test;

USE test;

CREATE TABLE `dummy` (
  `id` int(11) NOT NULL auto_increment,
  `x` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `dummy` VALUES (1, 1);
INSERT INTO `dummy` VALUES (2, 2);
INSERT INTO `dummy` VALUES (3, 2);

/*

the following queries produce the error:

#1305 - FUNCTION test.sum does not exist 

*/

SELECT 
SUM
(
CASE x
  WHEN 1 THEN 2
  WHEN 2 THEN 4
  ELSE 8
END
)
FROM `dummy` WHERE 1;

SELECT 
SUM
(
IF (x=1, 2, IF(x=2,4,8))
)
FROM `dummy` WHERE 1;

/*

this queries work

*/

SELECT 
AVG
(
CASE x
  WHEN 1 THEN 2
  WHEN 2 THEN 4
  ELSE 8
END
)
FROM `dummy` WHERE 1;

SELECT 
SUM
(
IF (x=1, 2, IF(x=2,4,8))
)
FROM `dummy` WHERE 1;

/*

group functions tested:

AVG .. o.k.
SUM .. error
MIN .. error
MAX .. error
VARIANCE .. error

*/

[17 Feb 2006 14:14] Valeriy Kravchuk

Please, read the manual (http://dev.mysql.com/doc/refman/5.0/en/functions.html):

"Note: By default, there must be no whitespace between a function name and the parenthesis following it. This helps the MySQL parser distinguish between function calls and references to tables or columns that happen to have the same name as a function. However, spaces around function arguments are permitted.

You can tell the MySQL server to accept spaces after function names by starting it with the --sql-mode=IGNORE_SPACE option. (See Section 5.2.5, “The Server SQL Mode”.) Individual client programs can request this behavior by using the CLIENT_IGNORE_SPACE option for mysql_real_connect(). In either case, all function names become reserved words."

Look:

mysql> SELECT
    -> SUM
    -> (
    -> CASE x
    ->   WHEN 1 THEN 2
    ->   WHEN 2 THEN 4
    ->   ELSE 8
    -> END
    -> )
    -> FROM `dummy` WHERE 1;
ERROR 1305 (42000): FUNCTION test.SUM does not exist
mysql> SELECT  SUM( CASE x   WHEN 1 THEN 2   WHEN 2 THEN 4   ELSE 8 END) FROM `
dummy` WHERE 1;
+-----------------------------------------------------------+
| SUM( CASE x   WHEN 1 THEN 2   WHEN 2 THEN 4   ELSE 8 END) |
+-----------------------------------------------------------+
|                                                        10 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select sum (x) from `dummy`;
ERROR 1305 (42000): FUNCTION test.sum does not exist
mysql> select sum(x) from `dummy`;
+--------+
| sum(x) |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

I hope, you understand now that this is not a bug.

[19 Feb 2006 9:25] Oswaldt Oldenburg

The e-mail below demonstrates, it is not a bug.  But I think, it's a cosmetical one then - why it works with AVG? A equal behavior of all functions would be desirable.

ID: 17487
Updated by: Valeriy Kravchuk
Reported by: Oswaldt Oldenburg
-Status: Open
+Status: Not a Bug
Severity: S1 (Critical)
Category: Server
Operating System: Windows XP Professional with SP2
Version: MySQL 5.0.18-nt

[17 Feb 15:14] Valeriy Kravchuk

Please, read the manual
(http://dev.mysql.com/doc/refman/5.0/en/functions.html):

"Note: By default, there must be no whitespace between a function name
and the parenthesis following it. This helps the MySQL parser
distinguish between function calls and references to tables or columns
that happen to have the same name as a function. However, spaces around
function arguments are permitted.

You can tell the MySQL server to accept spaces after function names by
starting it with the --sql-mode=IGNORE_SPACE option. (See Section
5.2.5, “The Server SQL Mode”.) Individual client programs can
request this behavior by using the CLIENT_IGNORE_SPACE option for
mysql_real_connect(). In either case, all function names become
reserved words."

Look:

mysql> SELECT
-> SUM
-> (
-> CASE x
-> WHEN 1 THEN 2
-> WHEN 2 THEN 4
-> ELSE 8
-> END
-> )
-> FROM `dummy` WHERE 1;
ERROR 1305 (42000): FUNCTION test.SUM does not exist
mysql> SELECT SUM( CASE x WHEN 1 THEN 2 WHEN 2 THEN 4 ELSE 8
END) FROM `
dummy` WHERE 1;
+-----------------------------------------------------------+
| SUM( CASE x WHEN 1 THEN 2 WHEN 2 THEN 4 ELSE 8 END) |
+-----------------------------------------------------------+
| 10 |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select sum (x) from `dummy`;
ERROR 1305 (42000): FUNCTION test.sum does not exist
mysql> select sum(x) from `dummy`;
+--------+
| sum(x) |
+--------+
| 5 |
+--------+
1 row in set (0.00 sec)

I hope, you understand now that this is not a bug.