Archive for the ‘IGNORE_SPACE’ tag
Placement over substance
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…