Bug #59379 Quoting options incomplete in docs.
Submitted: 9 Jan 2011 13:13 Modified: 1 Jul 2011 14:13
Reporter: Peter Laursen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:any OS:Any
Assigned to: Paul DuBois CPU Architecture:Any
Tags: qc

[9 Jan 2011 13:13] Peter Laursen
Description:
http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html

"The delimiter can be specified as an unquoted or quoted argument. Quoting can be done with either single quote (') or douple quote (") characters."

however `backtick` works too.  And using `backtick`would be more logical (in non-ANSI mode) than 'single quote' and "double quote".

How to repeat:
This works: 

DROP PROCEDURE IF EXISTS p;
DELIMITER !
CREATE PROCEDURE p()
BEGIN
  DECLARE `var!` INT;
END;
!
DELIMITER;

This does not: 

DROP PROCEDURE IF EXISTS p;
DELIMITER !
CREATE PROCEDURE p()
BEGIN
  DECLARE var! INT;
END;
!
DELIMITER;

Also this fails;

DROP PROCEDURE IF EXISTS p;
DELIMITER '!'
CREATE PROCEDURE p()
BEGIN
  DECLARE var! INT;
END;
!
DELIMITER;

.. what contradicts documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html "The delimiter can be specified as an unquoted or quoted argument. Quoting can be done with either single quote (') or douple quote (") characters. ").  Obviously single-quoting the delimiter does not work with this example.

Suggested fix:
I disagree with the conclusion in http://bugs.mysql.com/bug.php?id=59374
 
Please sort out issues rather than closing as *not a bug*.

I still believe that the C-API has an issue when user-defined DELIMITER is a substring of user-defined variable. Refer user's original testcase from http://bugs.mysql.com/bug.php?id=59374:

DROP PROCEDURE IF EXISTS p;
DELIMITER go
CREATE PROCEDURE p()
BEGIN
  DECLARE var_go INT;
END;
go
DELIMITER;

There is no special character here that requires any kind of quoting.  'go' is ASCII. At least documentation should be more detailed on this (but I consider it a client bug - not a doecumetation bug).
[9 Jan 2011 13:37] Valeriy Kravchuk
The only new thing in this report is about using backticks to set delimiter:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.55-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter '!'
mysql> select 1!
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> select 1'!'
    -> !
+---+
| ! |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql> delimiter ;
mysql> delimiter `!`
mysql> select 1
    -> !
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

So, the above works, while the manual does not describe this. I agree that we have a valid documentation request here.

Everything else I consider a duplicate of that other bug report (and not a bug).
[9 Jan 2011 13:54] Peter Laursen
1) Well .. OK. The new thing here in this report is that `backtick` may be used for quoting a delimiter (what is not documented). 

2) I disagree that the other report is *not a bug*.  User's original testcase used 'go' as delimiter and since 'go' is pure ASCII (and his variable name ('var_go') also is) it is *as per docs* (http://dev.mysql.com/doc/refman/5.1/en/identifiers.html) no required to to *quote* at all.  But in practice it is (either to *quote* the DELIMITER or the variable) as the CREATE statement will else fail.

further I think quoting a DELIMITER should be consistent with either how `identifiers`(preferred) or 'aliases' are quoted. 

But also note that in case of "substring-conflict" with the 'go'-substring it works equally well to `quote` the DELIMITER and the variable. In both cases the 'substring-conflict' is 'suppressed'. This is not documented (and IMHO reveals a poor parsing algorithm in the client).  If code will not be changed it should be recommended always to *quote* a DELIMITER (with any sort of supported quotes).

A final comment: The DELIMITErR 'go' is very convenient to use for people migrating from SQL Server.  I have seen it a few times before.
[9 Jan 2011 16:56] Valeriy Kravchuk
Let's say that this is a valid documentation request about non-documented feature of quoting DELIMITER with `` and missing examples of how quoting may help in complex situations described here. 

The rest is presented as Bug #59381.
[9 Jan 2011 17:00] Peter Brawley
My original bug report was mistaken, but a syntax issue which misdirects the user into errors always indicates poor design, inadequate documentation, or both. 

Peter Larsen has reported such an issue. To dismiss it as mostly not new does a disservice to the MySQL product we all wish to support and improve, and in any case is flatly wrong since as Peter says:

User's original testcase used 'go' as delimiter and since 'go' is pure 
ASCII (and his variable name ('var_go') also is) itis *as per docs*.

And as Peter rightly points out, DELIMITER go is conveniend & familiar for SQL Server users.
[9 Jan 2011 17:21] Valeriy Kravchuk
Bug #59381 is exactly a request to make mysql command line client smarter, so that it does NOT use 'go' as a DELIMITER if it is a part of some other word, 'var_go'.

mysql command line client currently is only smart enough to ignore DELIMITER only in quoted strings:

macbook-pro:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.1.55-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> delimiter go
mysql> select 'go_go'
    -> go
+-------+
| go_go |
+-------+
| go_go |
+-------+
1 row in set (0.00 sec)

mysql> select 1 as go_go
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 '' at line 1
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 '_' at line 1

So, 'select 1 as ' and '_' were sent to server, both instances of 'go' were considered delimiters. But quoting helps, as Peter demonstrated several times:

mysql> select 1 as 'go_go'
    -> go
+-------+
| go_go |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

This is how mysql client was designed, and http://dev.mysql.com/doc/refman/5.5/en/mysql-commands.html says nothing to prove that delimiter is not processed when it is a part of other word. So, formally, we do not have a bug (in Bug #59374, if it is about mysql command line client), but we have documentation that is far from perfect (that's why we have this report), and we would like to have more advanced parser in mysql command line client. This is a request for a new feature in Bug #59381.
[10 Jan 2011 10:05] Peter Laursen
I suggest that this doc updation also addresses:
http://bugs.mysql.com/?id=59381
[13 Jan 2011 20:41] Peter Laursen
I modify from 'incomplete and incorrect' to only 'incomplete' in synopsis. The incompleteness is that `backquote` is not listed as a valid quoting option for identifiers

All the 4 here work:

DELIMITER '$$'
SELECT 1 $$ 
DELIMITER ;

DELIMITER "$$"
SELECT 1 $$ 
DELIMITER ;

DELIMITER `$$`
SELECT 1 $$ 
DELIMITER ;

DELIMITER $$ 
SELECT 1 $$
DELIMITER ;

(also in ANSI mode:

mysql> set sql_mode = 'ansi_quotes';
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter `|`
mysql> select 1 |
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

mysql>
)
[1 Jul 2011 14:13] Paul DuBois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Updated text:

The delimiter string can be specified as an unquoted or quoted
argument on the delimiter command line. Quoting can be done with
either single quote ('), douple quote ("), or backtick (`)
characters. To include a quote within a quoted string, either quote
the string with a different quote character or escape the quote with
a backslash ("\") character.

...

mysql interprets instances of the delimiter string as a statement
delimiter anywhere it occurs, except within quoted strings. Be
careful about defining a delimiter that might occur within other
words. For example, if you define the delimiter as X, you will be
unable to use the word INDEX in statements. mysql interprets this as
INDE followed by the delimiter X.