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: | |
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
[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.