MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Archive for the ‘SQL_MODE’ tag

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 , , ,