Bug #15037 if statement does not work as documented in 5.0 manual
Submitted: 17 Nov 2005 23:50 Modified: 18 Nov 2005 16:22
Reporter: Aimee Grimes Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0 OS:Linux (Red Hat 4.0)
Assigned to: CPU Architecture:Any

[17 Nov 2005 23:50] Aimee Grimes
Description:
if statement does not appear to work as documented in 5.0 manual. Simple constructs following the form "IF search_condition THEN statement_list ENDIF;" raise error 1064. Either there is a bug in the implementation or the docs should be expanded with examples to show correct usage. Examples could include simple conditionals like the following and also the use of aggregates in a select. 

mysql> if (1>0) then select "hello"
    -> end if;
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 'if (1>0) then select "hello"
end if' at line 1

mysql> if (select count(*) from if_test) >  1
    -> then drop table if_test
    -> end if;
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 'if (select count(*) from if_test) > 1
then drop table if_test
end if' at line 1

How to repeat:
mysql> if (1>0) then select "hello"
    -> end if;
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 'if (1>0) then select "hello"
end if' at line 1
mysql> if 1 > 0 then select "Hello"
    -> end if;
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 'if 1 > 0 then select "Hello"
end if' at line 1
mysql> show version
    -> ;
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 'version' at line 1
mysql> select version();
+----------------+
| version()      |
+----------------+
| 5.0.15-max-log |
+----------------+
1 row in set (0.01 sec)
[18 Nov 2005 16:22] Hartmut Holzgraefe
the IF statement can only be used within procedures/functions
[16 Dec 2007 7:21] Adam Dorian
So how to run the following type of logic in a schema_update.sql file which will be run each time an application is deployed (but only run when applicable due to conditional logic)?  The following throws an error - but can someone tell me the correct way to run this in MySQL SQL script?

IF((SELECT COUNT(*) AS column_exists FROM information_schema.columns WHERE table_name = 'my_table' AND column_name = 'old_column_name') > 0) 
THEN  
	ALTER TABLE `my_table` CHANGE COLUMN `old_column_name` `old_column_name` VARCHAR(100)
END IF
[28 Dec 2007 18:55] Ron Dorfman
Does anyone have an answer? I have the same problem
[2 Jan 2008 23:22] Joseph Wilwayco
According to the 2nd poster, IF only works in StoredProcs/Functions.  So create a stored proc in a script, then call it at the end of the script!

DELIMITER $$

DROP PROCEDURE IF EXISTS upgrade_database $$
CREATE PROCEDURE upgrade_database()
BEGIN
 -- INSERT NEW RECORD IF PREEXISTING RECORD DOESNT EXIST
 IF((SELECT COUNT(*) AS column_exists FROM information_schema.columns WHERE table_name = 'test' AND column_name = 'test7') = 0) THEN
   ALTER TABLE test ADD COLUMN `test7` int(10) NOT NULL;
   UPDATE test SET test7 = test;
   SELECT 'Altered!';
 ELSE
   SELECT 'Not altered!';
 END IF;
END $$

DELIMITER ;
CALL upgrade_database();
[2 Jan 2008 23:25] Joseph Wilwayco
Oh, and Adam.  Thanks for the 'workaround' for doing a conditional alter-table statement...
[18 Jul 2011 13:51] Paul Poulain
I've just submitted a feature request for this: http://bugs.mysql.com/bug.php?id=61895
[18 Jul 2011 14:19] Paul Poulain
Oh, but as another workaround, you can do conditional insert/update statements as follows:

INSERT INTO my_table(name, description)
SELECT 'new_name', 'description' FROM DUAL WHERE NOT EXISTS (SELECT 'X' FROM my_table WHERE name = 'new_name);

Maybe this is slightly less of a bodge(?) but will only work for insert/update statements, but not for other stuff (like "if (...) drop table...").