As I mentioned in my previous post, I’ve upgraded to mySQL 4.1.11 from 3.23.58.

Great.

However, there were a couple of surprises I’ve run across in the testing of that change that – to me – shouldn’t have been a surprise: It should have been very clearly stated up front in the changeover docs from mySQL.

• Change in timestamp behavior: – it’s now (v4.1+) just like a datestamp. So any code written against this will have issues. That’s a HUGE issue. Fortunately, I had only one timestamp, and it was referenced in the front-end code only once, so no biggie. Could have be a huge pain, however.
• Subselects: Yay! Subselects! Unfortunately, only sorta true.

The mySQL documentation says the following about subqueries (i.e. subselects):

A subquery is a SELECT statement inside another statement.

Starting with MySQL 4.1, all subquery forms and operations that the SQL standard requires are supported, as well as a few features that are MySQL-specific.

— mySQL documentation; subqueries page

Sounds great, doesn’t it? ALL subquery form and operations that the SQL standard requires are supported….

But you scroll to the bottom of the same page (before the user comments), and you see this limitation: “One restriction is that currently you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, and UPDATE.”

In other words, for same table subqueries, all you can do is SELECT. I.e. one of five possible statements, or only 20%. What a letdown. This limits the functionality of the subquery tremendously.

If they had said this up front, it wouldn’t have been that big a deal. Underpromise, overdeliver.

Oh well. Still better than was preceded it…