MySQL Bugs: #59381: if DELIMITER is a substring of statement it fails

Bug #59381	if DELIMITER is a substring of statement it fails
Submitted:	9 Jan 2011 15:51	Modified:	13 Jan 2011 21:00
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:		OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
A clarification on http://bugs.mysql.com/bug.php?id=59374
(3 test cases)

It is not only a problem with local variables in stored procedures.  Whenever a substring identical to a user-defined DELIMITER is encountered the client fails to parse for DELIMITER.

How to repeat:
1) 
USE test; 
DELIMITER go
DROP TABLE IF EXISTS no_go go
CREATE TABLE no_go (no_go INT) go
DELIMITER ;

mysql> USE test;
DATABASE CHANGED
mysql> DELIMITER go
mysql> DROP TABLE IF EXISTS no_go go
QUERY OK, 0 ROWS affected, 1 warning (0.00 sec)

ERROR:
NO QUERY specified

mysql> CREATE TABLE no_go (no_go INT) go
ERROR 1113 (42000): A TABLE must have AT LEAST 1 COLUMN
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 'no_'
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 'INT)'
 AT line 1
mysql> DELIMITER ;
mysql>

2) 
USE test; 
DELIMITER go
DROP TABLE IF EXISTS nogo go
CREATE TABLE nogo (nogo INT) go
DELIMITER ;

mysql> USE test;
DATABASE CHANGED
mysql> DELIMITER go
mysql> DROP TABLE IF EXISTS nogo go
QUERY OK, 0 ROWS affected, 1 warning (0.00 sec)

ERROR:
NO QUERY specified

mysql> CREATE TABLE nogo (nogo INT) go
ERROR 1113 (42000): A TABLE must have AT LEAST 1 COLUMN
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 'no' a
t 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 'INT)'
 AT line 1
mysql> DELIMITER ;
mysql>

3) 
USE test; 
DELIMITER go
DROP TABLE IF EXISTS go go
CREATE TABLE go (go INT) go
DELIMITER ;

mysql> DELIMITER go
mysql> DROP TABLE IF EXISTS 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:
NO QUERY specified

mysql> CREATE TABLE go (go INT) 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
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 'INT)'
 AT line 1
mysql> DELIMITER ;
mysql>

Suggested fix:
case 3) is understandable and acceptable. 'SQL aware' parsing is not expected in the client (but maaybe docs should have a note).

But in case 1) + 2) it is not understandable and not acceptable. Here 'go' occurs as substrings of other tokens ('nogo' and 'no_go').  This is much too simple parsing. If not 'go' is not surrounded by SPACEs, TABs, LINEBREAKS (+ COMMAs or whatever) a token with the substring 'go' ('nogo', no_go') should not be mistaken for 'go'. 

(mark one as duplicate if you want - as long as one is 'verified'),

In any case this has nothing to do with C API. DELIMITER parsing is performed by client, mysql command line client in this case.

I agree that it would be nice to have more smart parser in mysql command line client (to work as you expect in cases 1 and 2). That would be a reasonable feature request for mysql command line client.

Note though that "use delimiter that is never used in the code of your storage procedures, neither standalone nor as a part of some other word", like // or $$, will be a workaround acceptable in 90% of cases...

Probably the most simple example possible:

DELIMITER go
SET @nogo = 1 go
DELIMITER ;

mysql> DELIMITER go
mysql> SET @nogo = 1 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 '= 1'
at line 1
mysql> DELIMITER ;
mysql>

I do not know if this happens inside the API code or the 'mysql' application code. But I can see you have changed the category .. so OK with me so far!

So, I consider this a reasonable feature request for a more advanced parser in the mysql command line client.

@Davi ..

I had a notification that you have updated here.  But I cannot see *with what* you updated. Why keep it private/secret?

@Davi ..

I had a notification that you have updated here.  But I cannot see *with what* you updated. Why keep it private/secret?

Sorry for duplicating my last comment.  It was a gmail issue.

Please consider it as a documentation request in the client section of the docs.

Reopening as a docs request.

OK .. thanks!

What surprises me (and Jiri it seems too) is that there is even not the most simple *tokenizer code* in operation when locating a DELIMITER in the client.  Any matching substring - even when part of another word/string/token - will be understood and handled as DELIMITER.

This is IMHO not obvious. For those knowing the client internals (and people using similar command line tools a lot) it may be obvious - but people used to GUI tools primarily will expect smarter parsing based on tokenization. And here *nogo* is a token that should not be confused with *go*.

But I do not claim that most GUI clients handle it better at the moment.  Our own does not!

I am unsure what is to be clarified or documented here. The examples behave exactly as I would expect. If a delimiter is not to be treated as such, what is its purpose?

"And here *nogo* is a token that should not be confused with *go*."

No? Why not?

If ";" is a delimiter, should "select 1;" treat "1;" as a token? It seems the same logic would apply.

Take a compiler for instance.  It would not identify the token *go* in the token *nogo*.  The MySQL parser will also not find the token *char* inside the token *charset*.  Because *nogo* and *charset* are identified as *tokens* - exactly like it is in the mysql client when you quote.

another example: 

DELIMITER FR
SELECT * FROM sometable FR
DELIMITER ;

.. requries either `FR` or `FROM` to be quoted. As typed above it will fail.

Maybe it was naive to expect a similar tokenization happening in the command line client (without quoting) but that was what I (and Jiri I believe) did expect. And I believe taht every Windows user would expect same in my opinion.  The (simple) client behavior is *not obvious*. Most users are used to smarter behavior from softwares.

Of course a parser (compiler as you say) could identify such tokens, it just depends on the language. The main point is that the parsing in a mysql client is very simple. It only looks for a delimiter anywhere (quoting apart) in a given string. To make it aware of the "tokens" you talk about, we would need to have a SQL parser in the client, which is a nogo :-)

Another example:

If I have the table `sometable` with the column `selection` I can execute

SELECT * FROM sometable where selection IS NULL;

The MySQL parser does not find the reserved word "select" inside "selection".  I need not quote the column (like `selection`).  If the MySQL parser was equally non-smart as the client is, I would have to. As a naive Windows user I expect the software I use to be *smart* (at least *that much smart*) :-)

"Take a compiler for instance.  It would not identify the token *go* in the token *nogo*. "

Because "go" is not a delimiter. If it were, how would it parse it?

The MySQL parser will also not find the token *char* inside the token *charset*."

Because "char" is not a delimiter.

"The MySQL parser does not find the reserved word "select" inside "selection"."

Again, because "select" is not the delimiter.

@Davi .. I understand now.  It surprises me.  The manual does not explains *how simple* the client behaves here whne identifying DELIMITER.  I think it should as lots of users would expect less simple behavior.

I do not buy Paul's way of arguing.  He is making assumption that people know what is not explained.

@Paul

Where will I find the exact meaning of the term DELIMITER in the MySQL docs?

We cannot read your thoughts to find the meaning of term. We can only read the manual.

"I do not buy Paul's way of arguing.  He is making assumption that people know what is not
explained."

I didn't know I was arguing with you. I'm trying to understand how what you describe as "smart" software is not in fact incorrectly behaving software. Let's suppose I take your point of view, and I define the delimiter as "able" and the statement I want to issue is "select * from mytable".

How do I write it?

"Where will I find the exact meaning of the term DELIMITER in the MySQL docs?"

Eh? There is no mystery. It's nothing more than: Delimiter = that which delimits

I am sorry if the English term 'argue' is a bad translation of Danish 'argumentere'. 

I have another report (about quoting a DELIMTER).  If I should propose something it could be like this:

" A DELIMITER can be quoted as an identifier and *must be* if it is used with a statement where the DELIMITER appears as a substring of any un-quoted word in the statement. "

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.

I have added this to the delimiter description as a caution:

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.

That is also fine!