Another reason to hate MySQL

I'm currently about half way through Joe Celko's SQL for Smarties and I've got to tell you - it's spoiling me as a LAMP developer.

Let me start out by saying that this book is a must-read for any developer who works with databases. Even if you've been writing SQL for years, you need to read this book. In fact, unless you sat on the ANSI SQL-92 committee with Joe Celko, you can benefit from reading this book. I've been "doing SQL" for years, and I've learned a bunch of new tricks and rediscovered things I had forgoten from reading this. And not only is it informative on SQL, but it's also entertaining and informative on other subjects (e.g., in one chapter, Joe describes the history of the soundex algorithm).

The problem with getting more SQL-literate is that I have to work with MySQL. Now, I don't want to rag on MySQL too much - it's good for what it is. And what it is is an engine for storing data and getting it back out in an efficient manner. The problem is that it's really not focused on implementing standard SQL in any deep way.

My particular annoyance today came when I was writing code to do a simple update. The situation is that I was working on a new variation on the picture galleries for our site, and realized that I wasn't associating a thumbnail image with the galleries I was creating. By virtue of the way our codebase works (all of which pre-dates my involvement), our picture galleries consist of n+1 rows in our main media table, where n is the number of pictures in the gallery - one row per picture and another row for the gallery meta-item. Because of the way I had written my creation methods in PHP and added my foreign key constraints in the database, the path of least resistance was to just set the gallery thumbnail to the same value as one of the pictures. Here's a simplified example of the basic query I used:

-- Create a sample test and populate it with data.
CREATE TABLE test_tbl (id INT, val2 INT);
INSERT INTO test_tbl VALUES (1, 2);
INSERT INTO test_tbl VALUES (3, NULL);
-- Here's the query I was running.
UPDATE test_tbl SET val2 = (SELECT val2 FROM test_tbl WHERE id = 3) WHERE id=2;

In the routine as I had written it, I had the IDs of the gallery and all the pictures, but that was it. With a query like this, I could just clone the thumbnail of one picture to the gallery in a single query - no need to clutter the code with any lookups or suchlike.

Of course, if you know MySQL, you may already know that this didn't work. When I tried running this, I got:
ERROR 1093 (HY000): You can't specify target table 'test_tbl' for update in FROM clause
I was actually kind of surprised by this. I mean, this query isn't all that exotic. It's perfectly valid in standard SQL and it works in PostgreSQL. Why not MySQL?

Well, the answer seems to be that MySQL disregards the conceptual execution model of subqueries. I say this because, if you do the subquery on a different table than the one being updated, then MySQL doesn't have a problem with it. Example:
-- Create a second table and add a row.
CREATE TABLE extra_tbl (id INT, val2 INT);
INSERT INTO test_tbl VALUES (3, 9);
-- This one works just find in MySQL.
UPDATE test_tbl SET val2 = (SELECT val2 FROM extra_tbl WHERE id = 3) WHERE id=2;

You see, according to Joe Celko (read the book!), the execution model of a subquery is that it creates a temporary table. So, in the case of my first sample query, it would create a copy of test_tbl, find the target row in that, and then update test_tbl. If you didn't use that model, then it would make some sense that you can't update the same table you're searching in. (Note: Of course, other databases don't actually create temporary tables for every subquery - they just produce the same result as if they did.)

I guess my problem is that I'm just starting to expect too much from MySQL. I've reached the point that I find it annoying that the default storage engine (MyISAM) doesn't support foreign key constraints. I find it even more annoying that MySQL doesn't support the enormously useful CHECK constraints at all. And, really, is it absolutely necessary to invent new versions of standard functions, mangle standard functions (what the hell did they do to CAST()?), or to disable the ANSI string concatenation operator by default? It all just just seems superfluous. After all, PostgreSQL didn't have much trouble with the ANSI standard. Why can't MySQL get it?

Oh, well. I guess it could be worse. At least now I have the wisdom to know what I'm missing. In days gone by, I thought MS Access seemed just fine as a database. Back then, I didn't declare primary keys and didn't even know what indexes were! By comparison to that, MySQL looked like freakin' Oracle! If only I hadn't been so young and stupid then, think of where I'd be now....

You can reply to this entry by leaving a comment below. You can send TrackBack pings to this URL. This entry accepts Pingbacks from other blogs. You can follow comments on this entry by subscribing to the RSS feed.

Related entries

Add your comments #

A comment body is required. No HTML code allowed. URLs starting with http:// or ftp:// will be automatically converted to hyperlinks.