Description:
SQL statements to create a procedure or function require a DELIMITER statement to remove ambiguity between statement terminators within the body of the procedure and statement terminator for the CREATE statement.
The DELIMITER statement is case-sensitive in Query Browser. That is, "delimiter" (all lower-case) works, but "DELIMITER" or "Delimiter" results in a syntax error.
The USE statement is also case-sensitive in Query Browser, though in the opposite manner: "use test" is ignored, but "USE test" functions as expected.
This bug may be related to symptoms reported in bugs #10955 and #9428
How to repeat:
Show "delimiter" (in lower-case) syntax working in QB's Query Tab:
1. File->New Query Tab
2. Enter statements in Query Tab:
delimiter $$
create procedure foo1 (out param1 int)
begin
select 42 into param1;
end $$
3. Observe success; procedure foo1 has been created.
Show "DELIMITER" (in upper-case) syntax fails in QB's Query Tab:
4. File->New Query Tab
5. Enter statements in Query Tab:
DELIMITER $$
create procedure foo2 (out param1 int)
begin
select 42 into param1;
end $$
6. Observe error:
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
'DELIMITER $$
create procedure foo2 (out param1 int)
begin
select 42 into pa' at line 1
Show that the template procedure syntax generated by the GUI fails.
7. Script->Create Stored Procedure/Function, and choose "foo3" as the name of the procedure.
8. A new Script Tab opens, with a template statement pre-generated:
DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`foo3` $$
CREATE PROCEDURE `test`.`foo3` ()
BEGIN
END $$
DELIMITER ;
9. Execute the script, and observe the error:
Script line: 1 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
'DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`foo3` $$
CREATE PROCEDURE `tes' at line 1
10. Edit the content in the Script Tab, and change "DELIMITER" to "delimiter".
11. Execute the script again. Observe success.
Demonstrate that "USE" is also case-sensitive:
12. File->New Query Tab
13. Enter statement and execute:
use mysql
14. Observe error dialogue:
You tried to enter an empty string. Please type an SQL command
into the SQL edit field and execute again.
15. Change statement and execute:
USE mysql
16. Observe success: the `mysql` database becomes highlighted in bold in the Schemata pane, and that it is now the default database.
Suggested fix:
Statements that are parsed and executed client-side before being sent to the server should ignore case, to make them consistent with usage in the mysql CLI.
"DELIMITER" and "delimiter" and "DeLiMiTeR" should work identically.
"USE" and "use" should work identically.
Description: SQL statements to create a procedure or function require a DELIMITER statement to remove ambiguity between statement terminators within the body of the procedure and statement terminator for the CREATE statement. The DELIMITER statement is case-sensitive in Query Browser. That is, "delimiter" (all lower-case) works, but "DELIMITER" or "Delimiter" results in a syntax error. The USE statement is also case-sensitive in Query Browser, though in the opposite manner: "use test" is ignored, but "USE test" functions as expected. This bug may be related to symptoms reported in bugs #10955 and #9428 How to repeat: Show "delimiter" (in lower-case) syntax working in QB's Query Tab: 1. File->New Query Tab 2. Enter statements in Query Tab: delimiter $$ create procedure foo1 (out param1 int) begin select 42 into param1; end $$ 3. Observe success; procedure foo1 has been created. Show "DELIMITER" (in upper-case) syntax fails in QB's Query Tab: 4. File->New Query Tab 5. Enter statements in Query Tab: DELIMITER $$ create procedure foo2 (out param1 int) begin select 42 into param1; end $$ 6. Observe error: 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 'DELIMITER $$ create procedure foo2 (out param1 int) begin select 42 into pa' at line 1 Show that the template procedure syntax generated by the GUI fails. 7. Script->Create Stored Procedure/Function, and choose "foo3" as the name of the procedure. 8. A new Script Tab opens, with a template statement pre-generated: DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`foo3` $$ CREATE PROCEDURE `test`.`foo3` () BEGIN END $$ DELIMITER ; 9. Execute the script, and observe the error: Script line: 1 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 'DELIMITER $$ DROP PROCEDURE IF EXISTS `test`.`foo3` $$ CREATE PROCEDURE `tes' at line 1 10. Edit the content in the Script Tab, and change "DELIMITER" to "delimiter". 11. Execute the script again. Observe success. Demonstrate that "USE" is also case-sensitive: 12. File->New Query Tab 13. Enter statement and execute: use mysql 14. Observe error dialogue: You tried to enter an empty string. Please type an SQL command into the SQL edit field and execute again. 15. Change statement and execute: USE mysql 16. Observe success: the `mysql` database becomes highlighted in bold in the Schemata pane, and that it is now the default database. Suggested fix: Statements that are parsed and executed client-side before being sent to the server should ignore case, to make them consistent with usage in the mysql CLI. "DELIMITER" and "delimiter" and "DeLiMiTeR" should work identically. "USE" and "use" should work identically.