MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Placement over substance

with 5 comments

I was stunned when a SQL query raised an ERROR 1630 (42000) telling me the SUM function didn’t exist in MySQL 5.5.23. The fix was simple. The opening parenthesis of the SUM function must be on the same line as the SUM keyword without an intervening white space. Alternatively phrased, you can’t have a line return or white space between the SUM function name and the opening parenthesis of the call parameter list. The same rule doesn’t apply to the opening parenthesis of the FORMAT function and it seems to me that this parsing inconsistency is problematic.

Therefore, my surprise, observation, and complaint is that all functions don’t parse the same way, using the same rules. That is, unless you use specialized SQL_MODE settings. This assumption was borne out by Kolbe Kegel’s comment on this post, and there are 30 remaining built in functions that have specialized parsing and resolution markers.

A simplified version of the code that raises the error follows. As you’ll notice the opening parenthesis for the FORMAT and SUM function have intervening white space and a line return.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT   t.transaction_account AS "Transaction"
,        LPAD(FORMAT
           (SUM
             (CASE
                WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                     EXTRACT(YEAR FROM transaction_date) = 2011 THEN
                  CASE
                    WHEN t.transaction_type = cl.common_lookup_type THEN
                      t.transaction_amount
                    ELSE
                      t.transaction_amount * -1
                  END
             END),2),10,' ') AS "JAN"
FROM     TRANSACTION t CROSS JOIN common_lookup cl
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
AND      cl.common_lookup_type = 'DEBIT'
GROUP BY t.transaction_account;

Based on the comments, the SQL_MODE is:

mysql> SELECT @@version, @@sql_mode;
+-----------+----------------------------------------------------------------+
| @@version | @@sql_mode                                                     |
+-----------+----------------------------------------------------------------+
| 5.5.23    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------+----------------------------------------------------------------+
1 ROW IN SET (0.00 sec)

It raises the following error:

ERROR 1630 (42000): FUNCTION studentdb.SUM does NOT exist. CHECK the 'Function Name Parsing and Resolution' SECTION IN the Reference Manual

Moving ONLY the opening parenthesis to the end of the SUM keyword (or removing the line return and white space from between the SUM keyword and opening parenthesis) prevents the error but it would be more convenient if it supported both approaches. It seems odd that an intervening line return and white space for the SUM function raises an exception while the same intervening line return and white space doesn’t raise an exception for the FORMAT function. It strikes me the parser should support both or reject both. Here’s the fixed code that works without enabling the IGNORE_SPACE SQL Mode option.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

SELECT   t.transaction_account AS "Transaction"
,        LPAD(FORMAT
           (SUM(
              CASE
                WHEN EXTRACT(MONTH FROM transaction_date) = 1 AND
                     EXTRACT(YEAR FROM transaction_date) = 2011 THEN
                  CASE
                    WHEN t.transaction_type = cl.common_lookup_type THEN
                      t.transaction_amount
                    ELSE
                      t.transaction_amount * -1
                  END
             END),2),10,' ') AS "JAN"
FROM     TRANSACTION t CROSS JOIN common_lookup cl
WHERE    cl.common_lookup_table = 'TRANSACTION'
AND      cl.common_lookup_column = 'TRANSACTION_TYPE'
AND      cl.common_lookup_type = 'DEBIT'
GROUP BY t.transaction_account;

As noted by the comments, adding the IGNORE_SPACE to the SQL_MODE lets both queries work without moving the open parenthesis. You can do that in a session with the following syntax (which is covered in an older post):

SET SQL_MODE=(SELECT CONCAT(@@sql_mode,',IGNORE_SPACE'));

Hope this helps folks…

Written by maclochlainn

July 3rd, 2012 at 4:10 pm

Posted in MySQL,PSM,sql,SQL/PSM

Tagged with , , ,

«
»

5 Responses to 'Placement over substance'

Subscribe to comments with RSS or TrackBack to 'Placement over substance'.

  1. MySQL does support both styles, via setting sql_mode.

    http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_ignore_space

    IGNORE_SPACE

    Permit spaces between a function name and the “(” character. This causes built-in function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 9.2, “Schema Object Names”.

    Rob Smith

    3 Jul 12 at 11:58 pm

  2. It’s a what’s your “sql_mode” problem:

    root@mysql-5.1.51> SET sql_mode := '';
 
root@mysql-5.1.51> SELECT SUM (population) FROM world.City;
ERROR 1630 (42000): FUNCTION common_schema.SUM does NOT exist. CHECK the 'Function Name Parsing and Resolution' SECTION IN the Reference Manual
 
root@mysql-5.1.51> SET sql_mode := 'IGNORE_SPACE';
 
root@mysql-5.1.51> SELECT SUM (population) FROM world.City;
+------------------+
| SUM (population) |
+------------------+
|       1429559884 |
+------------------+
1 ROW IN SET (0.02 sec)

    Shlomi Noach

    4 Jul 12 at 2:24 am

  3. See also the definition of the IGNORE_SPACE option for SQL_MODE (http://dev.mysql.com/doc/refman/5.5/en/server-sql-mode.html#sqlmode_ignore_space).

    Nigel Thomas

    4 Jul 12 at 2:41 am

  4. I appreciate all the replies but Shlomi, I really enjoyed your reply! :-)

    I modified the post a bit because my issue was that the error is only thrown for the SUM function. Without adding the IGNORE_SPACE option to the SQL_MODE, both the SUM and FORMAT functions should behave the same way and use the same parsing rules. Unfortunately, they don’t.

    maclochlainn

    4 Jul 12 at 5:56 pm

  5. Hi!

    Take a look at function-resolution for a bit more information about this. In particular:

    “The requirement that function calls be written with no white space between the name and the parenthesis applies only to the built-in functions that have special considerations. COUNT is one such name. The exact list of function names for which following white space determines their interpretation are those listed in the sql_functions[] array of the sql/lex.h source file.”

    Here’s the list:

    ADDDATE, BIT_AND, BIT_OR, BIT_XOR, CAST, COUNT, CURDATE, CURTIME, DATE_ADD, DATE_SUB, EXTRACT, GROUP_CONCAT, MAX, MID, MIN, NOW, POSITION, SESSION_USER, STD, STDDEV, STDDEV_POP, STDDEV_SAMP, SUBDATE, SUBSTR, SUBSTRING, SUM, SYSDATE, SYSTEM_USER, TRIM, VARIANCE, VAR_POP, VAR_SAMP

    Kolbe

    Kolbe Kegel

    5 Jul 12 at 3:29 pm

Leave a Reply