Short Puzzles about Names and Handlers
Here are three short routines that raise tough questions. I’ll bet that many MySQL stored-procedures experts will fail to answer all three.
Question 1. The Ambiguous Identifier
Given one table and one stored procedure:
DELIMITER //
CREATE TABLE t (x INT)//
INSERT INTO t VALUES (1)//
CREATE PROCEDURE p ()
BEGIN
DECLARE x INT DEFAULT 2;
SELECT x FROM t;
END//
CALL p()//
Notice that x is both a column and a variable.
MySQL will return
(a) ‘1′ because that’s the value of column x.
(b) ‘2′ because that’s the value of variable x.
(c) an error message because x is ambiguous.
?
Question 2. The same-level handler
Given one table and one stored procedure:
DELIMITER //
SET @@sql_mode=”//
CREATE TABLE t (x SMALLINT)//
CREATE PROCEDURE p ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLWARNING SELECT z FROM y;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION INSERT INTO t VALUES (99999);
SELECT y FROM t;
END//
CALL p()//
Notice that t.y does not exist so the SELECT fails and the SQLEXCEPTION handler catches it.
MySQL will return:
(a) A warning, because 99999 is an “out of range” value for a SMALLINT column.
(b) An error, because the SQLWARNING handler will catch the warning for “out of range”.
(c) An infinite loop, because the SQLWARNING handler causes an exception, the SQLEXCEPTION handler catches the exception and causes a warning, the SQLWARNING handler catches the warning and causes an exception, and so ad infinitum.
?
Question 3: The note and the warning
Given no table and the following stored procedure:
DELIMITER //
SET @@sql_mode=”//
DROP TABLE IF EXISTS t//
DROP TABLE IF EXISTS t//
SHOW WARNINGS//
CREATE PROCEDURE p ()
BEGIN
DECLARE EXIT HANDLER FOR SQLWARNING SELECT * FROM t;
DROP TABLE IF EXISTS t;
END//
CALL p()//
Notice that t doesn’t exist, so SHOW WARNINGS will say:
+——-+——+——————-+
| Level | Code | Message |
+——-+——+——————-+
| Note | 1051 | Unknown table ‘t’ |
+——-+——+——————-+
1 row in set (0.00 sec)
MySQL will return:
(a) a warning, because “DROP TABLE IF EXISTS t” within the stored procedure causes an “unkown table ‘t’” warning.
(b) an error, because the SQLWARNING handler catches the warning and selects from t, which does not exist.
?
For fun, take a few moments to answer the questions in your head before continuing.
These questions might puzzle you because in the past they have puzzled us. So we’ve sometimes given mixed signals. After some comparisons with other DBMSs and some hard looks at the SQL standard and (not least!) some hints about what our users really want the answers to be, we’re going to address the puzzling behaviour in MySQL 6.x. Or even earlier, if everyone says we’re looking at bugs not features.
Question 1. The Ambiguous Identifier
The answer is (b). But we think that for MySQL 6.x it should be (c).
Let’s look first at what happens with the other major DBMS products: DB2, Oracle, SQL Server. That means we’re looking at five possibilities because DB2 splits three ways. (I’ll always insist “there is only one DB2″ but this is one of the rare exceptions where the different product lines look less than 100% the same on the outside.)
For DB2 i5/OS 5.4:
“If the tables and views specified in an SQL routine body exist at the time the routine is created, the name will first be checked as a column name. If not found as a column, it will then be checked as an SQL variable name in the compound statement, and then checked as an SQL parameter name.
If the referenced tables or views do not exist at the time the routine is created, the name will first be checked as an SQL variable name in the compound statement and then as an SQL parameter name. … If not found, it will be assumed to be a column.”
http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic=/com.ibm.etools.iseries.langref2.doc/rbafzmst895.htm
For DB2 Version 9.1 for z/OS:
“If an SQL statement contains an SQL variable or parameter and a column reference with the same name, DB2 interprets the name as an SQL variable or parameter name.”
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.sqlref/db2z_compoundstatement4externalsqlpl.htm
For DB2 Version 9.1 for Linux, UNIX, and Windows:
“If an SQL statement contains an identifier with the same name as an SQL variable and a column reference, DB2 interprets the identifier as a
column.”
http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=/com.ibm.db2.udb.admin.doc/doc/r0004239.htm
For Oracle, I tried the following statements:
create table t (s1 number);
create procedure p as
s1 number;
begin
s1 := 4;
delete from t where s1 = 4;
end;
/
insert into t values (5);
call p();
select * from t;
Result: I got one row.
Therefore Oracle gives precedence to column name.
For SQL Server, variable names must begin with @. Therefore SQL Server doesn’t matter.
For standard SQL, I had a slow and tedious task, merging the clauses of SQL/Foundation 6.6 (the rules for columns and parameters) with the clauses of SQL/PSM 6.2 (the rules for variables). I now have enough confidence to say that this supersedes any nonsense I may have said previously on this topic.
“The statement is a syntax error if x could be any two of: a parameter, a variable, a column.”
For those who have the SQL:2003 standard document handy … The justification of the above statement can be seen in SQL/Foundation 6.6 Syntax Rules, after merging with SQL/PSM 6.2. The key part follows “If N = 1″ (true since there is only one identifier in the chain) and “If the number of possible scope tags … is 1″ (false since there are 2 or 3 possible scope tags), so the phrase to watch is: “Otherwise, each possible scope tag shall be a range variable”. But that’s not the case, and “shall” means (I quote SQL/Framework) “required to be true of syntactically conforming SQL language”. Therefore, syntax error.
(I suppose one could object that there doesn’t have to be a beginning statement label. Technically true, but if there wasn’t an implicit invisible beginning label, simple statements like
CREATE PROCEDURE p () BEGIN DECLARE v INT; END
would be illegal. The oversight will probably be addressed in a Technical Corrigendum for the standard document someday.)
Summary:
DBMS Result of ambiguous identifier
—- ——————————
DB2 i5/OS If table exists: column. Else: variable.
DB2 z/OS Variable.
DB2 Linux/Windows Column.
Oracle Column.
SQL Server Not Applicable.
ANSI/ISO Syntax Error.
Given the frequency of bug reports caused by ambiguous identifiers (Bug#5967 Bug#13771 Bug#16052 Bug#16888 Bug#17878 Bug#20834 Bug#29779 Bug#30163), I think we’d be doing users a favour by causing a syntax error. Often MySQL will accept a something that’s a possible mistake and just give a warning (for example the warning that you get when you try to insert 99999 into a SMALLINT column). In this case, since people who migrate from another DBMS will have the wrong assumption, we should go with the standard. To avoid the syntax error, users will merely have to use “table_name . column_name”
Question 2. The same-level handler
The answer is (b). But we think it shouldn’t be. As one of our engineers (Marc Alff) put it: “The server gives up (without notice) when finding that an exception handler is invoked recursively.” I was a bit worried that somebody would want to claim this is appropriate behaviour, but it didn’t happen. For MySQL 6.x we’ll want to handle this correctly.
A comparison with other DBMSs is unnecessary this time, since Oracle and SQL Server have implemented SQL-procedure syntax which doesn’t offer the same capabilities as MySQL/DB2/standard handlers. Instead let’s look once again at the fine print in the standard document.
Here is the test of “existing behaviour”.
delimiter //
drop procedure if exists p//
drop table if exists t1, t2//
create table t1 (s1 smallint)//
create table t2 (s1 smallint)//
create procedure p()
begin
declare continue handler for sqlwarning
create table t2 (s1 int);
declare continue handler for sqlexception
insert into t1 values (99999);
create table t1 (s1 int);
end//
call p()//
Result: “ERROR 1050 (42S01): Table ‘t2′ already exists”.
Apparently the exception handler’s INSERT was caught by the warning handler.
Is it a bug if a handler causes a condition and it’s caught by a handler within the same handler declaration list? I’ll call this “same level” handling rather than “recursive”.
Well, it does seem to be outside the standard requirements. I think that the following statements are “logic”. You can tell because I use the word “therefore” frequently.
Quotations are from SQL:2008 9075-4 draft.
From 4 4.8 Condition Handling:
“If there is no most appropriate handler and the condition is an exception condition, then the SQL-statement raising the exception
condition is terminated with that exception condition.”
Therefore, in order to be a handler for itself or for another handler at the same level, a handler must be “most appropriate”.
From 14.1 compound statement (edited for clarity)
”
Format:
<compound statement> ::=
BEGIN
[ <local handler declaration list> ]
[ <SQL statement list> ]
END
…
Syntax Rules:
1) Let CS be the <compound statement>.
16) The scope of a <handler declaration> simply contained in a <local handler declaration list> simply contained in CS is the <SQL statement list> SSL of CS excluding every <SQL schema statement> contained in SSL.
”
Therefore if the handler declaration list contains declarations of handler H1 (a warning handler) and handler H2 (an exception handler), then statements in H1 are not in the scope of declaration of H2, and statements in H2 are not in the scope of declaration of H1.
From 14.2 handler declaration (edited for clarity)
”
Format:
<handler declaration> ::=
DECLARE <handler type> HANDLER FOR <condition value list>
<handler action>
…
Syntax Rules:
1) Let HD be the <handler declaration>.
General Rules:
1) When the handler H associated with the conditions specified by HD is created, it is the most appropriate handler for any condition CN raised during execution of any SQL-statements that are in the scope of HD that has an SQLSTATE value or condition name that is the same as an SQLSTATE value or condition name associated with this handler, until H is destroyed.
”
The important phrase is surely “that are in the scope of HD”. Since H2’s statements are not within the H1 handler declaration’s scope, H1 cannot be a most appropriate handler for H2’s statements’ conditions.
Therefore the H1 handler cannot be most appropriate for H2 statements.
Therefore the above example is a violation of the SQL standard.
Question 3: The note and the warning
The answer is (a). But we think that for MySQL 6.x it should be (b).
Another of MySQL’s engineers, Davi Arnaut, posed the puzzle thus:
“MySQL warnings are not the same beast as the SQL standard warnings. I.e. they have a different life cycle, and they work with continue handlers differently, at least now. A MySQL note could be considered a subtype of MySQL warning, only with a different severity.”
Yes. There is some evidence that was the concept of the Ur-Engineer, Monty Widenius, back in 2002. He wrote “one should get a warning (severity=note) for each database / table that didn’t exists.”
Sometimes the MySQL Reference Manual says notes are a subtype of warning:
“warnings of Note level are recorded”
“Note warnings are suppressed”
“cause Note-level warnings not to be recorded”
But as Mr Arnaut said, sometimes the manual says they’re distinct from warnings:
“the error, warning, and note messages”
“the total number of errors, warnings, and notes”
I’d like to clear this up by writing an email to our documentation department:
“Please be consistent, say note-level warnings, not notes and warnings”.
The SQL standard is clear: there are only types of “completion conditions” success (class ‘00′) and warning (class ‘01′).
If we finally accept that notes are warnings, then the appropriate class is ‘01′, and it’s a bug if any note isn’t ‘01′.
So we’ll have to make several adjustments because at the moment the SQLSTATE for various notes isn’t alway in class ‘01′ ‘warning’.
This is what I see in 6.0 source code when I grep for cc files
containing “…_warning…(…WARN_LEVEL_NOTE…)”:
sql/sql_trigger.cc: ER_NO_SUCH_USER, ER_TRG_DOES_NOT_EXIST,
ER_OLD_FILE_FORMAT
sql/event_parse_data.cc: ER_EVENT_CANNOT_CREATE_IN_THE_PAST,
ER_EVENT_EXEC_TIME_IN_THE_PAST
sql/field.cc: WARN_DATA_TRUNCATED ('01000'),
ER_WARN_DATA_OUT_OF_RANGE ('22003')
sql/item_subselect.cc: ER_SELECT_REDUCED ('01000')
sql/sql_db.cc: ER_DB_CREATE_EXISTS, ER_DB_DROP_EXISTS,
ER_BAD_DB_ERROR ('42000')
sql/event_db_repository.cc: ER_EVENT_ALREADY_EXISTS,
ER_SP_DOES_NOT_EXIST ('42000')
sql/sql_view.cc: ER_NO_SUCH_USER, ER_BAD_TABLE_ERROR ('42S02'),
ER_WARN_VIEW_WITHOUT_KEY
sql/sql_insert.cc: ER_TABLE_EXISTS_ERROR ('42S01')
sql/table.cc: ER_VIEW_NO_CREATION_CTX,
ER_VIEW_INVALID_CREATION_CTX, ER_NO_SUCH_USER
sql/set_var.cc: ER_UPDATE_LOG_DEPRECATED_TRANSLATED ('42000'),
ER_UPDATE_LOG_DEPRECATED_IGNORED ('42000')
sql/sql_parse.cc: ER_NO_SUCH_USER, ER_SP_DOES_NOT_EXIST ('42000'),
ER_YES
sql/sql_yacc.cc:31818: ER_NATIVE_FCT_NAME_COLLISION
sql/sql_repl.cc: ER_MISSING_SKIP_SLAVE, ER_UNTIL_COND_IGNORED,
ER_SLAVE_WAS_RUNNING, ER_SLAVE_WAS_NOT_RUNNING,
ER_SLAVE_IGNORED_SSL_PARAMS
sql/sql_table.cc: ER_BAD_TABLE_ERROR ('42S02'),
ER_DUPLICATED_VALUE_IN_TYPE, ER_AUTO_CONVERT,
ER_TABLE_EXISTS_ERROR ('42S01'), ER_ILLEGAL_HA,
ER_WARN_USING_OTHER_HANDLER
sql/item.cc: ER_WARN_FIELD_RESOLVED
storage/maria/ha_maria.cc: ER_ILLEGAL_HA_CREATE_OPTION
Unless otherwise specified, the associated SQLSTATE value is ‘HY000′. Only WARN_DATA_TRUNCATED and ER_SELECT_REDUCED have SQLSTATE class ‘01′.
The fix doesn’t mean that we should get rid of the note concept. Severity levels may be a standard de facto …
Oracle has “PL/SQL Warning Categories”
http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/07_errs.htm
… and DB2 has “information”, “warning”, “error”, “severe”
… and SQL Server has a severity level
http://blogs.msdn.com/sqlprogrammability/archive/2006/04/03/567550.aspx
… and NDB has severity levels for events: INFO, WARNING, ALERT, ERROR
http://dev.mysql.com/doc/refman/6.0/en/mysql-cluster-log-events.html
If the general idea is to ignore anything below a certain severity level, a continue handler intercept note-level warnings if and only if SQL_NOTES <> 0. Or maybe a syntax extension, e.g.
DECLARE x CONTINUE HANDLER FOR SQLWARNING SEVERITY_LEVEL > 1 …;
But that’s for after version 6.x, when MySQL decides what severity levels to have.
Leave a Reply