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: | |
Category: | MySQL Server: Documentation | Severity: | S3 (Non-critical) |
Version: | OS: | Any | |
Assigned to: | Paul DuBois | CPU Architecture: | Any |
Tags: | qc |
[9 Jan 2011 15:51]
Peter Laursen
[9 Jan 2011 16:04]
Valeriy Kravchuk
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...
[9 Jan 2011 16:30]
Peter Laursen
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>
[9 Jan 2011 16:33]
Peter Laursen
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!
[9 Jan 2011 16:52]
Valeriy Kravchuk
So, I consider this a reasonable feature request for a more advanced parser in the mysql command line client.
[9 Jan 2011 22:50]
Peter Laursen
@Davi .. I had a notification that you have updated here. But I cannot see *with what* you updated. Why keep it private/secret?
[9 Jan 2011 22:51]
Peter Laursen
@Davi .. I had a notification that you have updated here. But I cannot see *with what* you updated. Why keep it private/secret?
[9 Jan 2011 22:54]
Peter Laursen
Sorry for duplicating my last comment. It was a gmail issue.
[10 Jan 2011 9:53]
Peter Laursen
Please consider it as a documentation request in the client section of the docs.
[11 Jan 2011 13:04]
Georgi Kodinov
Reopening as a docs request.
[11 Jan 2011 13:15]
Peter Laursen
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!
[13 Jan 2011 19:08]
Paul DuBois
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?
[13 Jan 2011 19:10]
Paul DuBois
"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.
[13 Jan 2011 19:44]
Peter Laursen
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.
[13 Jan 2011 19:53]
Davi Arnaut
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 :-)
[13 Jan 2011 19:59]
Peter Laursen
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*) :-)
[13 Jan 2011 19:59]
Paul DuBois
"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.
[13 Jan 2011 20:01]
Paul DuBois
"The MySQL parser does not find the reserved word "select" inside "selection"." Again, because "select" is not the delimiter.
[13 Jan 2011 20:03]
Peter Laursen
@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.
[13 Jan 2011 20:08]
Peter Laursen
@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.
[13 Jan 2011 20:10]
Paul DuBois
"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?
[13 Jan 2011 20:24]
Paul DuBois
"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
[13 Jan 2011 20:24]
Peter Laursen
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. "
[13 Jan 2011 20:24]
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. 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.
[13 Jan 2011 21:00]
Peter Laursen
That is also fine!