MySQL Bugs: #41970: small details with DELIMITER syntax

Bug #41970	small details with DELIMITER syntax
Submitted:	8 Jan 2009 15:46	Modified:	4 Mar 2009 15:07
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S3 (Non-critical)
Version:	5.0.67, 5.1	OS:	Any
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	qc

Description:
http://dev.mysql.com/doc/refman/5.0/en/mysql-commands.html

1) those statements are contradictory: 
"delimiter (\d) Set statement delimiter. NOTE: Takes the rest of the line as   new delimiter." 
and 
"Note that all text commands must be first on line and end with ';' "

(hint: delimiter command should not be conluded with ";" as the ";" will be included in the DELIMITER defined)

2) SPACE seems not to be supported in delimiters.  Defining DELIMITER as "; ;" actually sets the defult ";" only! This should be documented and all other restrictions to if they are some (NULL character, non-pritable characters in general, special Unicode characters - whatever limitations there are with client implementation on various platforms)

How to repeat:
mysql> delimiter ;;
mysql> select 1;;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

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

ERROR:
No query specified

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

Suggested fix:
Not sure really.  Maybe only a small documentation issue here. Depends on whether SPACE is the only printable character affected!

corrected typo in synopsis!

Ok .. I now noticed this

"You should avoid the use of the backslash (“\”) character within the delimiter because that is the escape character for MySQL."

.. but SPACE not mentioned here!  

I can also see that latin special character are supported ('æøå') as DELIMITER. But no option to test with multibyte unicode characters on this (Windows) system.

I also noticed

mysql> delimiter \\
ERROR:
DELIMITER cannot contain a backslash character
mysql>

.. so same could be added for SPACE!

The space issue is not related to semicolon as this shows:

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

ERROR:
No query specified

I think this is a request for more clear documentation. Do you agree? Do you consider some of these a bug in mysql command line client?

I already wrote that I am not sure if it is documentation only! Because I am not sure if SPACE is the only character affected.  Likely it depends too on the client platform! For instance from Windows clipboard I can copy strings into cmd.exe that contain non-printable characters. Are they ignored or what happens?

I think that besides documentation also the client should return an error if SPACE is used or client should be modified to accept SPACE (search till end of line as documented)

A radical solution would be to restrict DELIMITER to use printable ASCII-characters (with the exception of \ and possibly SPACE). That would solve issues occuring with different encodings of non-ASCII characters and would make it easier to achieve a uniform implementation in different clients.

All this is of course rare issues.  I do not want to give the impression to be a 'grumbler'.  Actually I typed "; ;" by mistake in the first place (and was using another client (our own SQLyog) before I tried in 'mysql').  I find that practically every client has issues with SPACE in DELIMITERS, but not exactly same way!  Now every client can do as they like, but I think we all want to get as close to 'mys

I already wrote that I am not sure if it is documentation only! Because I am not sure if SPACE is the only character affected.  Likely it depends too on the client platform! For instance from Windows clipboard I can copy strings into cmd.exe that contain non-printable characters. Are they ignored or what happens?

I think that besides documentation also the client should return an error if SPACE is used or client should be modified to accept SPACE (search till end of line as documented)

A radical solution would be to restrict DELIMITER to use printable ASCII-characters (with the exception of \ and possibly SPACE). That would solve issues occuring with different encodings of non-ASCII characters and would make it easier to achieve a uniform implementation in different clients.

All this is of course rare issues.  I do not want to give the impression to be a 'grumbler'.  Actually I typed "; ;" by mistake in the first place (and was using another client (our own SQLyog) before I tried in 'mysql').  I find that practically every client has issues with SPACE in DELIMITERS, but not exactly same way!  Now - every client can do as they like, but I think we all want to get as close to 'mysql' as possible as long as a COMMAND syntax (and not some GUI functionality) is used.  So first and foremost behavior and documentation should be in accordance!

This became a little confusing (why does it always happen to me here? :-( ).  I will summarize

AS REGARDS SPACE
================

1) This 

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

ERROR:
No query specified

.. is an erroneous behavior by a program.  "DELIMITER | |" did not have the effect specified and expected by user but user had no information about it as no error or warning occurrred!

2) This

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

ERROR:
No query specified

and this passage from docs: 
"delimiter .... Takes the rest of the line as new delimiter."

.. are in conflict!

3)

The two statements from docs

"delimiter ... Takes the rest of the line as new delimiter." 

and
 
"Note that all text commands must be first on line and end with ';' "

.. are in conflict (as regards DELIMITER statement)!

In conclusion I think SPACE should not be supported.  Also because leading and trailing spaces cannot be supported anyway. But then
a) the client should print an error
b) docs should tell that SPACE character cannot be used with a DELIMITER specification!

AS REGARDS ALL OTHER (silly) ISSUES WITH OTHER WEIRD CHARCTERS
==============================================================

No clue!  I do not know if there are any issues!

Thank you for the feedback.

You are right: current behavior is not clear and affects not only described case. So I verify it as client bug and if development is not agree this should be re-qualified as documentation.

Space can be really used inside delimiter:

mysql> \d 'df dss
mysql> select version() df dss
+------------------+
| version()        |
+------------------+
| 5.1.31-debug-log | 
+------------------+
1 row in set (0.09 sec)

mysql> \d \ f
mysql> select version() f     
+------------------+
| version()        |
+------------------+
| 5.1.31-debug-log | 
+------------------+
1 row in set (0.00 sec)

mysql> \d f\ f    
mysql> select version() f f
+------------------+
| version()        |
+------------------+
| 5.1.31-debug-log | 
+------------------+
1 row in set (0.00 sec)

Although is not possible to set single space as delimiter and is not clear how quotes affect delimiter:

mysql> \d \ 
ERROR: 
Unknown command '\ '.
    -> \c
mysql> \d ' '
    -> \c
mysql> \d 'foo bar'
    '> '
    -> \c
mysql> \d `foo bar

Already fixed in other bug.

Description of delimiter parsing has been updated in the manual per Bug#26724.