Error in MySQL query concerning IF statements in SELECT

Question

I can't figure out what is wrong with the following query:

SELECT   t_sh.source_file_id,
         t_sh.id,
         MAX(t_sh.master_event_id),
         IF(t_sh.content REGEXP '[;}[:space:]]break;', 'true', 'false') AS break,
         IF(t_sh.content REGEXP '[;}[:space:]]break ', 'true', 'false') AS break_label,
         IF(t_sh.content REGEXP '[;}[:space:]]continue;', 'true', 'false') AS continue,
         IF(t_sh.content REGEXP '[;}[:space:]]throw ', 'true', 'false') AS throw,
         IF(t_sh.content REGEXP '[;}[:space:]]return;', 'true', 'false') AS void_return
FROM     source_histories AS t_sh,
         (   SELECT DISTINCT source_file_id
             FROM source_histories
             WHERE content <> NULL 
                 AND content REGEXP '[;}[:space:]]break;|[;}[:space:]]break |[;}[:space:]]continue;|[;}[:space:]]throw |[;}[:space:]]return;'
         ) AS t_uniqueSFI
WHERE t_sh.source_file_id = t_uniqueSFI.source_file_id;

It give me the following error when run with python's MySQLdb:

_mysql_exceptions.ProgrammingError: (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
'continue,\n\t\t\t\t\t\t\tIF(t_sh.content REGEXP '[;}[:space:]]throw ', 'true', 'false') ' at line 6")

I am new to SQL, and your help is much appreciated.

Abhik Chakraborty · Accepted Answer · 2014-06-25 19:33:53Z

1

The problem line is

IF(t_sh.content REGEXP '[;}[:space:]]continue;', 'true', 'false') as continue

continue is a reserved word and you need to backtick something as

IF(t_sh.content REGEXP '[;}[:space:]]continue;', 'true', 'false') as `continue`

Here what happens in mysql when your query comes to this line

mysql> select IF(uname REGEXP '[;}[:space:]]continue;', 'true', 'false') 
AS continue from test ;
ERROR 1064 (42000): 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 'continue from test' at line 1

Now if I use backtick on my test table I get

mysql> select IF(uname REGEXP '[;}[:space:]]continue;', 'true', 'false')
 AS `continue` from test ;
+----------+
| continue |
+----------+
| false    |
| false    |
| false    |
| false    |
| false    |
| false    |
| false    |
+----------+

answered Jun 25, 2014 at 19:33

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Stewart Smith Over a year ago

Thank you very much! I should have known to look at reserved words.

Collectives™ on Stack Overflow

Error in MySQL query concerning IF statements in SELECT

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related