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 ()
…

You’ve probably seen the MySQL Reference Manual page “What’s New in MySQL 6.0″ and so you’ve seen this description of a new feature: “Support for extended comments for tables, columns, and indexes.” Here’s what “extended” means.

You can do a little more with COMMENTs in ALTER or CREATE statements. The easy way to see the difference is by running this statement in both MySQL 5.1 and MySQL 6.0:

SELECT table_schema, table_name, column_name, character_maximum_length

FROM information_schema.columns

WHERE column_name LIKE '%comment';

From that, you’ll see that the differences are:

TABLE_NAME     COLUMN_NAME      CHARACTER_MAXIMUM_LENGTH CHARACTER_MAXIMUM_LENGTH

                                in MySQL 5.1             in MySQL 6.0

COLUMNS        COLUMN_COMMENT   255                      1024

PARTITIONS …

For those who haven’t memorized the names of every symbol: the tilde is the wavy line that appears occasionally over specific characters, particularly over N in Spanish. But it can appear alone. If you’re North American and you look at the top left corner of your keyboard, you’ll probably see it:

~

Anyway, we ran into this bug during a comprehensive re-test of all the Chinese character sets:

mysql> create table tbig5 (s1 char(5) character set big5);

Query OK, 0 rows affected (0.11 sec)mysql> insert into tbig5 values ('Y'),('~');

Query OK, 2 rows affected (0.08 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from tbig5 where s1 = 'Y';

+------+

| s1   |

+------+

| Y    |

| ~    |

+------+

2 rows in set (0.04 sec)

This is Bug#25420 “Tilde = ‘Y’ in Chinese”. It has been around for years, ever since the contribution of the BIG5 Chinese character set. No Chinese …

The verdict is in: DTrace will be on by default.

This is a wildly different thing from saying “DTrace will be there”. No right-minded person would want to eschew the benefits of DTrace, the performance-analysis tool par excellence from Sun Microsystems for all varieties of Solaris operating systems. Even if you aren’t a Solaris user, I’d suggest you have a quick glance at the literature about it because it’s the concept that non-Sun folk will surely try to imitate someday. And who knows, maybe you’ll become a Solaris user just because of this. I’ve heard of a consultant in California who does Oracle gigs thus: he goes into the shop where they’re running (say) some other Unix variant, asks them to copy their data to a Solaris machine that he handily brings with him, and re-runs their problem queries with DTrace to find the bottlenecks.

But the controversy (if I can call it that) was over whether the standard MySQL …

While we were batting forth ideas about the SIGNAL statement (MySQL 7.0, we appreciate your patience), the question arose: what do we call the thingabummie number that MySQL uses for errors and warnings? You know, this thing …

mysql> crete table t;
ERROR 1064 (42000): You have an error in your SQL syntax; …

What do we call that ‘1064′? An old-timer might say SQLCODE but actually the SQL standard committee decided a decade ago to dump SQLCODE in favour of SQLSTATE, which is a string rather than a number. Besides, MySQL’s number doesn’t follow the old standard conventions for SQLCODE.

There were 7 choices for the term for the numeric return, that we’ll use eventually in SIGNAL / RESIGNAL / GET DIAGNOSTICS syntax and descriptions.

1. SQLCODE.
SQLCODE does not appear in the standard, but it is the DB2 term
…

A recent patch for 6.0 has given users the ability to delay SELECT statements in certain circumstances.

If there’s an INSERT DELAYED going on, then the SELECT can wait for it, or not, depending on the option that the user chooses. This is not the same as saying “there will be a SELECT DELAYED like INSERT DELAYED” — the conditions are not the same. But the new option (whatever it’s finally called) will help out in some debugging and synchronization scenarios.

A bunch of people held a meeting in California just before the last MySQL User Conference. They decided that tablespaces are not inside databases, and databases are not part of tablespaces. They are separate objects which overlap, for example table A can belong to tablespace X and database Y, while table B can belong to tablespace Y and database X. The result will be some restrictions regarding what you can do with tablespace maintenance statements, particularly for Falcon tablespaces.

The meeting participants also decided that there must be a new privilege, CREATE TABLESPACE, which will be required for CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE.

And there was talk about a new table for metadata: INFORMATION_SCHEMA.TABLESPACES.

Suppose you use Linux and suppose you have two tables, and their names are alike except for an uppercase / lowercase difference: ‘City’ and ‘CITY’.

Consider the statement
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = ‘City’;

In MySQL 5.0 you’ll see both tables.

In recent versions of MySQL 5.1 you’ll see only one table: ‘City’.

In later versions there’s a proposal — definitely not an accepted proposal, but this blog can be speculative — that the collation for INFORMATION_SCHEMA.TABLES.TABLE_NAME should be changed to reflect this fact. Currently TABLE_NAME is UTF8_GENERAL_CI. If the proposal goes through, then the collation becomes UTF8_BIN for TABLE_NAME and for a few other columns that end with _NAME (but not all of them), except for Falcon tables, but only on some platforms where case is significant. On other platforms, such as Windows, the collation would be …

The things that might change in future versions for the BINARY and VARBINARY data types are:

* We’ll drop the last vestiges of the idea that they’re not really data types, that they’re just variants of char/varchar data types. The product has some quirks that reflect that old idea, but it will no longer be tenable after the next version of the SQL standard says firmly: they’re data types. This will affect, for example, some connectors.

* The result of some functions will cease to be VARBINARY. For example if you say
CREATE TABLE xm AS SELECT CONCAT(’a',0);
SHOW CREATE TABLE tm;
you’ll see that the result of concatenating a char with an integer is a varbinary. But we now feel that a more useful result data type would be char or varchar with the connection character set.

* We’ll allow BINARY VARYING as an equivalent for VARBINARY in definitions.

* We’ll allow X’AB’ ‘CD’ …