Monthly Archives: July 2013

MySQL “NOT FOUND” errors precedence (docs bug)

Posted on by

This page in the MySQL documentation says something totally wrong:

http://dev.mysql.com/doc/refman/5.6/en/handler-scope.html

“The precedence of NOT FOUND depends on how the condition is raised:

Normally, a condition in the NOT FOUND class can be handled by an SQLWARNING or NOT FOUND handler, with the SQLWARNING handler taking precedence if both are present. Normal occurrence of NOT FOUND takes place when a cursor used to fetch a set of rows reaches the end of the data set, or for instances of SELECT … INTO var_list such that the WHERE clause finds no rows.

If a NOT FOUND condition is raised by a SIGNAL (or RESIGNAL) statement, the condition can be handled by a NOT FOUND handler but not an SQLWARNING handler.

That page caused a great waste of time for me: a Stored Procedure had a bug, and to solve it I relied on the documentation; so I wrote a more complex code, which introduced a new bug, because… no part of the text I reported is true.

Not that I liked the described behavior: it would be illogical, and would make NOT FOUND error class useless in non-trivial cases. However, an important difference between the docs and the software is even worse than an illogical behavior.

That page is only in the 5.6+ documentation, but what it tells is not true for any version.

The best thing to do, in these cases, is to report a bug. An optional but good thing is to comment the page reporting a link to the bug. But Oracle registration page requires tons of info about the user and his/her company. And I don’t want to tell Oracle everything they want to know. One could ask me: “Why don’t you enter fake data?”. I tried, and mysql.com didn’t complain… but the confirmation email was never sent, so Oracle detects fake data. A bug report is a good thing for them, so why the hell don’t they try to make it easier?

Since I couldn’t report a bug or comment the docs, I described the problem in a Facebook post. Valeriy Kravchuk reported the bug for me:

http://bugs.mysql.com/bug.php?id=69805

He reported the SQL code I used for my tests. My conclusions:

  • Bugs that are in the NOT FOUND class are not in the SQLWARNING class.
    • This makes the discussion about the precedences useless.
  • No difference between an error produced bu the server and an error SIGNALed by the user.
  • No difference between CONTINUE and EXIT handlers.

If there are exceptions, I was not able to find them.

The reason why I am posting this article is that I don’t know if Oracle will process the bug. If they will not, this article says the truth (unless my tests were incomplete).

Enjoy!