A small issue of SQL standards

From a functional perspective, the core SQL support in all major and minor RDBMS-es is reasonably similar. In this light, it's sometimes quite disturbing to find how some very basic things work so differently across different products. Consider this simple statement:

SELECT  'a' /* this is a comment */ 'b'
FROM    onerow

What should the result be? (You can assume that onerow is an existing table that contains one row)

It turns out popular RDBMS-es mostly disagree with one another.

In Oracle XE, we get this:

SELECT  'a' /* comment */ 'b'
                         *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected

PostgreSQL 8.4 also treats it as a syntax error, and thus seems compatible with Oracle's behavior:

ERROR:  syntax error at or near "'b'"
LINE 1: SELECT  'a' /* this is a comment */ 'b'

In Microsoft SQL Server 2008 we get:

b
-
a

(1 rows affected)

As you can see, MS SQL treats the query as SELECT 'a' AS b FROM onerow.

Finally, in MySQL 5.1, we get:

+----+
| a  |
+----+
| ab |
+----+
1 row in set (0.00 sec)

So in MySQL, its as if the comment isn't there at all, and as if the string literals 'a' and 'b' are actually just one string literal 'ab'.

So what does the SQL standard say? In my copy of the 2003 edition, I find this (ISO/IEC 9075-2:2003 (E) 5.3 <literal>, page 145):

Syntax Rules
1) In a <character string literal> or <national character string literal>, the sequence:

<quote> <character representation>... <quote> <separator> <quote> <character representation>... <quote>

is equivalent to the sequence

<quote> <character representation>... <character representation>... <quote>

If we lookup the definition of <separator>, it reads:

<separator> ::= { <comment> | <white space> }...

So in this case, MySQL does the "right" thing, and basically ignores the comment, treating 'a' and 'b' as a single string constant 'ab'.

UPDATE 1: As always, the devil is in the details. And trust me, the SQL standard has many of them (details that is - I'll leave it up to the reader to decide for the devils, although I have a suspicion in a particular direction). Read on, and make sure to read Nick's comment on this post - it turns out PostgreSQL seems to behave exactly according to the standard in this case.

UPDATE 2: Serg also posted a comment citing yet another part of the standard that states that all comments implicitly count as a newline. This would mean that there doesn't have to be a literal newline character in or following the comment. In this case, my original remark that MySQL got it right would hold again.

I should state that I think very highly of both Nick and Serg, and as far as I am concerned, they're both right. I can't help but seeing this as yet more support for my statement that the SQL standard is so complex it is almost or perhaps completely impossible to get it right.

Do you find this too bold? If so, I'd really love to hear your thoughts on it. Please help us solve this paradox, I only want to understand what the standard really says.

If you try the same thing with a single line comment, all products mentioned react the same as with the initial query, except for PostgreSQL, which now treats the query according to the standard.

Now don't get me wrong. This post is not designed to bash or glorify any of the products mentioned. I think all of them are great in their own way. I am quite aware that although MySQL happens to adhere to the standard here, it violates it in other places. Finally, I should point out that I don't have a specific opinion on what the right behavior should be. I just want it to be the same on all platforms.

At the same time, I realize that for SQL it's probably too late - up to an extent, incompatibility is considered normal, and database professionals tend to be specialized in particular products anyway. So I'm not holding my breath for the grand unification of SQL dialects.

When I encountered this issue, I did have to think about that other rathole of incompatibilities I have to deal with professionally, which is web-browsers. An interesting development there is the HTML 5 specification, which actually defines an algorithm for parsing HTML - even invalid HTML. This is quite different from the approach taken by most standards, which typically define only an abstract grammar, but leave the implementation entirely up to the vendors. In theory, providing parsing instructions as detailed as done in HTML 5 should make it easier to create correct parsers, and hopefully this will contribute to a more robust web.

Anyway. That was all. Back to work...

UPDATE: I just heard that Sybase (unsurprisingly) behaves similar to MS SQL for this query (that is, query is valid, and returns 'a' in a column called b). I checked SQLite myself, which is also in that camp.

Nick also pointed out that LucidDB also provides a standard compliant implementation, in other words, it behaves exactly like PostgreSQL for this particular query. However, Julian, who was and is closely involved in LucidDB agrees with Serg that the comment should probably count as a newline, and filed a bug for LucidDB.

I checked Firebird 2.1.3, and they are in the Oracle camp: in both cases, the query gives a syntax error.

MySQL, Oracle and NoSQL: In the grand scheme...

...NoSQL is just larger than a fly's dropping, and MySQL and Oracle are more alike than either of their respective fanboys would like to admit.

Courtesy of Google trends:



I guess I won't be changing my career just yet.

UPDATE: I tried a few terms for "Microsoft SQL Server" before posting (SQL Server, MS SQL) but found none that came up with what I felt like was a realistic volume (they are all much, much lower than I expected). @MarkGStacey suggested trying "SQL 2008", "SQL 2005" and "SQL 2000", and those return much better results indeed (though still much lower than MySQL or Oracle). Anyway - I'd love to have some way of bunching up all those terms and have Google Trends show them as one trend, but I haven't figured out a way to do that. If you know how, please drop a line at let me know.

I'll adjust the blog if I find a more satisfactory solution.

MySQL Conference 2010 Presentation: Optimizing Stored Routines

Yesterday I delivered my presentation for the MySQL User Conference and Expo 2010: Optimizing MySQL Stored Routines. If you are interested in the slides, you can find them on both the MySQL conference site as well as on slideshare.net. Here's the abstract of my presentation so you can decide if this is interesting for you:

MySQL stored routines (functions, procedures, triggers and events) can be useful. But many casually written stored routines are unnecessarily slow. The main reason is that MySQL does not apply even simple code optimizations to stored routine code. Many developers are not aware of this, and as a result, write stored routine code that can quite easily be tuned, increasing performance by 50%-100% by only applying very straightforward code optimizations.

It was very pleased to see so many people attend: I had the impression that MySQL stored routines are quite impopular, due to performance issues, and a syntax that is often regarded as "clunky", so I didn't expect more than about 20 people to show up. Much to my pleasure, the ballroom was filled for about two-thirds, and I estimate there were 70-something people in the room.

A quick survey of the audience indicated that all of them were in fact using stored routines in production, so I assume they didn't show up out of morbid curiosity :) Interestingly, only few people reported performance issues. It would be interesting to do more research to find out what people are in fact doing with MySQL stored routines. Among yesterday's attendees, there were people using MySQL stored routines for managing user privileges, processing astronomical data, and checking complex dynamic business rules. To be sure - these were all different users - not just one isolated fanatic going wild with stored routines.

Co-incidentally, Domas Mituzas from facebook also mentioned stored routines in his presentation on high concurrency MySQL as a way to reduce the lock gap when performing multiple changes in a single transaction. I'm just saying - perhaps MySQL stored routines aren't that bad at all, they just need more love and dedication from the MySQL developers so they can mature and gain wider applicability.

Recently, I already wrote about a recent improvement in MySQL 5.5, the long anticipated SIGNAL /RESIGNAL syntax. I hope more improvements will follow soon now the dust is settling after Oracle's acquisition of Sun. After hearing Edward Screven unfold Oracle's strategy for MySQL in yesterday's keynote, I can tell you without reservation that I am quite optimistic :)

Anyway - that is all for now. Two days of conference ahead :)

MySQL a factor in EU's decision

I just read Björn Schotte's post on the activities of the European Union antitrust regulators concerning the intended takeover of Sun Microsystems by Oracle.

Björn mentions a news article that cites EU Competition Commissioner Neelie Kroes saying that the commission has the obligation to protect the customers from reduced choice, higher costs or both. But to me, this bit is not the most interesting. Later on the article reads:

The Commission said it was concerned that the open source nature of Sun's MySQL database might not eliminate fully the potential for anti-competitive effects.

With both Oracle's databases and MySQL competing directly in many sectors of the database market, MySQL is widely expected to represent a greater competitive constraint as it becomes increasingly functional, the EU executive said.

In other words, the commission is working to protect the MySQL users :)

Personally, I (and many other MySQL community members) don't fear for the future of MySQL as a product. But I do think it is justified to worry about customers that are now paying Sun for some licensed usage of MySQL, most notably OEM customers and a bunch of Enterprise users.

Ever since the news was disclosed concerning the intention of Oracle to acquire Sun, it has been speculated that Oracle my try to "upsell" the Oracle RDBMS to current MySQL enterprise users. However I don't think that that would be the brightest of moves. I did a bit of speculation myself back in April in response to questions put forward in the SSWUG newsletter.

I maintain the opinions I stated there:

• MySQL / Oracle are completely different beasts and customers realize this, and most likely Oracle does so too. People running MySQL for web related applications won't move to Oracle. Period. Oracle may be able to grab some customers that use MySQL for data warehousing, but I think that even in these cases a choice for Infobright or Kickfire makes more sense.


• Not all problems are database problems - if Oracle does a decent job of supporting and developing MySQL, they may become a respectable enough partner for current (larger) MySQL users to help them solve other problems such as systems integration.


• Instead of looking at the benefits for MySQL customers of using Oracle, look at the benefits for Oracle customers using MySQL. Suddenly Oracle can offer support for the most popular webstack in the world - Now all these enterprise customers running expensive Oracle installations can finally build cheap websites based on MySQL and even get support from Oracle on connecting their backend Enterprise Oracle instances to the MySQL web front ends.


• It's not all about the products. Open Source adds a whole new dynamic to the development process. I'm not just talking about outside developers that offer new features and code patches, as this does not happen too often. There's more to it than code though
  
  In all successful open source projects I know there is a very lively culture of users engaging with developers and voicing their opinion on what is good and what is not so good. There is a very real chance for the user to influence the direction of the development process (although this does not mean everybody gets what they want in equal amounts). Conversely this provides a great opportunity for the development organization to learn about what the users really need and wish for.
  
  In short, Oracle may want to use Sun/MySQL to learn how to do better business with more empowered users.

Of course, its all just my opinion - speculation is free. So you should feel free too to post your ideas on the matter. Go ahead and leave a comment ;)

Not so random slashdot comment Gem

Java 8 will replace String with String2, which will treat empty string and null the same.

By by characterZer0 (138196), on Monday April 20, @08:44AM

Writing a Book: Building Pentaho Solutions

Ok - this has been stewing for some time now, and I think now is the right time to announce that I am working together with Jos van Dongen from Tholis Consulting to create a book for Wiley with the tentative title "Building Pentaho Solutions".

My personal aim is to make this book the primary point of reference for DBAs and Application Developers that are familiar with Open Source products like MySQL and PostgreSQL but have no prior BI skills, as well as BI professionals that are familiar with closed source BI products like Microsoft BI and Business Objects that want to learn how to get things done with Pentaho.

The book will cover all distinct components and sub-products that make up the Pentaho BI Suite. For each component (and where applicable), installation, usage and maintenance are discussed and illustrated.

Background theory is given as needed to provide context for those readers with no prior BI knowledge or experience. This means we will cover topics such as dimensional modeling, data warehousing, data integration and much more. At the same time, the book will have a strong "hands-on" focus, and for that purpose, Jos and I have put together a fairly realistic online DVD Rental Company, www.worldclassmovies.com.

We went as far as creating different database schemas for the operational applications (for customer orders, inventory management, and purchase orders), and generating non-trivial volumes of example data for it (tables with 100k to 10m rows), covering over 7 years of DVD rental business - and this is just the operational system. And although generated, it's not just random data - things like customer age and location distribution, per year and per week ordering behaviour etc. - it's all in our sample data.

In our schemas and sample solutions, we are making an effort to cater to MS SQL, MySQL, Oracle and PostgreSQL users. We will also explore a few highly interesting open source database products like LucidDB and MonetDB.

Anyway - I will soon post some more details about the book and it's contents as we progress. I have taken Baron Schwartz's advice on writing a book to heart, and we are working from a pretty detailed outline trying to meet a darn-tight schedule.

If all goes well, we should end up with a 450-500 page book in a store near you by August 2009.

UPDATE:
Jos and I are in the final stages of writing the book. We will then have to process reviewer comments and set up the book's website (including all code samples).

We've had a few changes to our schedule. Instead of a 400-500 page book, it will be a 500-600 page book. It was due to be available late August, but this will now be Early September.

The book can be pre-ordered on Amazon. More information on the book can be found on the Wiley website

---

UPDATE2: The book is in print and available since late August 2009! So far, we've had a couple of very nice reviews.

The book is available directly from the Wiley site and also from Amazon. Wiley also provides an e-book. If the link to the e-book is not working or claiming the book is not available as e-book then you most likely ended up on the European version of the website. To fix that and order the e-book anyway, click the "choose location" link on the Wiley homepage, and set it to United States and try again.

Random RDBMS and SQL Myths debunked

A few times now, I've been wanting to write this down. I know: a lot of people will go *shrug*. Others may find me pedantic. Some of will say I'm being a smart-ass. Whatever...but I just got to write down a few of these common misconceptions that keep floating around.

None of these misconceptions are really harmful - in most cases, they do not lead to misunderstanding or miscommunication. However, when you are writing about these subjects, you'll often find that a sloppy definition you used in some place will bite you in the tail, and make it harder to explain something later on. So, that is why I from time to time get kind of obsessed with finding just the right words.

I'm not pretending I have the right words though. But there are a few informal ways of saying things that at a glance look right but are in fact wrong. Here's a random list of some of them:

PRIMARY KEY and UNIQUE constraints are unique indexes

Wrong - an index is just a convenient implementation to detect duplicate entries, and this is used by all RDBMS-es I am familiar with to implement PRIMARY KEY and UNIQUE constraints. However, the fact that there is a distinction is evident in for example the Oracle SQL syntax. For example, in ALTER TABLE ... DROP CONSTRAINT you can specify whether the associated index should be kept or also discarded.

Some people argue that it does not make sense to make the distinction in case the RDBMS does not maintain the constraint and index as separate objects. (This is the case in for example MySQL.)

Well, maybe...but I disagree. When I think about constraints, I'm thinking about business rules and guarding them to maintain database integrity. When talking about indexes, I'm thinking about performance and access paths. Quite different things, and in my opinion a shame to throw away the words to express the difference in my opinion.

A table consists of rows and columns

No - there is nothing wrong with an empty table. In other words, it does not consist of rows. It may or may not contain rows, but that is a different story.

A scalar subquery returns one column and one row

Wrong - first of all, a scalar subquery may return zero rows, in which case it evaluates to null, which is perfectly valid. But there is more to it.

Whether something is or is not a subquery is matter of syntaxis. The SQL grammer is defined so that if you encounter a query between parenthesis where a scalar value is appropriate, then that query (including the parentheses) will be parsed as a scalar subquery. In other words, the text satisfies the production rule for the non-terminal symbol "scalar subquery".

The parser will usually be smart enough to verify whether the subquery yields one column, but the number of rows returned is a runtime affair.

Suppose the query that makes up the scalar subquery would in fact return more than one row...would it suddenly not be a scalar subquery anymore? Of course not. It is still a scalar subquery - it just happens to be impossible to execute it. In other words, it violates the semantics of a scalar subquery and is therefore invalid. But the mere fact that we can conlcude that must imply that it is a scalar subquery.

A subquery is a SELECT statement that appears as a part of another SELECT statement

Wrong - For the same reasons as the previous issue. A statement is a syntactical construct. It has to do with discovering a pattern in a piece of text so that it satisfies a particular rule in the SQL grammer. That grammar does not have a rule that allows statements to be nested - not in pure SQL anyway (Of course, in stored procedures, one can have statement blocks like BEGIN...END, IF...END IF etc that really can contain other statements)

Of course, if we would take the SELECT that makes up the subquery and run it in isolation, it would be a SELECT-statement. Bit that is exactly the heart of the matter: because we are regarging it as part of another statement, it cannnot be a statement itself. This is simply a matter of definition of course - most people will immediately understand what is meant.

What would be better to say though is that a subquery is a query or query expression that appears as part of another SQL statement. However, this is also not correct: CREATE VIEW for example does contain a query expression, but this would most likely not be called a subquery. For this particular case, you can argue that there is nothing sub-ish about the query expression, because it is simply an essential part of the CREATE VIEW statement.

But what to think of CREATE TABLE...AS SELECT... and INSERT INTO...SELECT? The query expression is certainly not an essential part of CREATE TABLE and INSERT INTO, and in that sense, the query does look like it is subordinate to the statement it is part of.

You could argue that a query is a subquery if it appears inside another query. That seems sound, but what to think of UPDATE ... SET = (SELECT ...)? Personally I am reluctant to call an UPDATE statement a query - I tend to think of a query as a SELECT statement or sometimes a query expression.

I can think of only one thing that really is a defining characteristic of a subquery though - that is that the query expression must appear within parentheses. So, again, a matter of syntax more than a matter of semantics. I must admit I'm still not very satisfied with this though...What do you think?

NULL is the absence of a value

Variants of this statement go like "NULL is a missing value" or "NULL is not a value".

With slight doubt, I say: wrong. It certainly is true that many people use NULL to convey that something is not there or that something is not applicable. But this is a matter of choice, it does not change the meaning of NULL itself. If we use the same line of reasoning as we used for the subquery myth, we must conclude that NULL is certainly a valid value expression. It can legally appear anywhere where we can put a value. It is IMO also perfectly ok to say things like "...that expression evaluates to NULL".

So what does the SQL standard say? Well, here's a quote:

...the null value is neither equal to any other value nor not equal to any other value — it is unknown
whether or not it is equal to any given value....

So, I'm in that camp too: NULL is a value, and if we have a NULL in say, the integer domain, we just don't know which of all possible integers it is.

Foreign keys must reference a primary key

Wrong - a Unique constraint is mostly just as acceptable.

In MySQLs InnoDB it is even more relaxed - the foreign key only needs to reference the prefix of an index in the parent table, although this is so exotic, it should probably be ignored.

This table is not normalized - it still contains redundancy

Wrong - a table is normalized when it is in the first Normal form. There are a few different opinions what that means exactly, but it usually works to say that a table is not normalized when it contains repeating groups.

A slightly stronger statement is to say that a table is not normalized when it contains data that is not atomic. This is stronger because it does not cover only repeating groups, but also columns that, for a single row, do not contain a single value. For example, a first name/last name combination in one column is not atomic, and therefore, a table that contains such values is not normalized. (There are opinions that require even more than this, but for practical purposes the sense of atomic values works pretty well.)

The source of confusion is in what happens beyond the first normal form. Although a table maybe normalized, it can still contain redundancy. By removing redundancy, you can progressively achieve a higher normal form. In many cases, one would require at least third normal form or the Boyce-Codd normal form for building database schemas. Many people say "normalized" when they actually mean "in at least the third normal form".

So - what do you think? Pedantic? Have other myths? Maybe you have a good, satisfactory definition for subqueries? Or maybe you find an error in my debunkings?

Just drop me a comment on this post - thanks in advance.

MySQL: Transactions and Autocommit

Some people believe that the ability to commit or rollback multiple statements as a single unit is a defining characteristic of the concept of transactions. They draw the -false- conclusion that enabling autocommit is the same as disabling transactions. It is easy to demonstrate why this is not true.

Autocommit

With autocommit enabled, every statement is wrapped within its own transaction. Successful execution of a statement is implicitly followed by a COMMIT, and the occurrence of an error aborts the transaction, rolling back any changes.

By default, autocommit is enabled in MySQL. You can check the current setting by executing the following statement:

mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

The @@ prefix denotes a server variable. The 1 means that autocommit is currently enabled. Let's leave it enabled so we can test the difference with not using transactions.

Transactions and Storage Engines

First, we create a table backed by a storage engine that is capable of handling transactions. I'm using the new FALCON engine in these examples, but you can try it with any of the storage engines that is capable of handling tranactions, such as InnoDB, ndbcluster, PBXT or SolidDB. If you want to try with falcon, please download and install MySQL 5.2.

Let's create that FALCON table in the test database:

mysql> use test;
Database changed
mysql> create table test_falcon(i int primary key) 
    -> engine = falcon
    -> ;
Query OK, 0 rows affected (0.70 sec)

A word of warning: be sure to check the message after creating the table. If you accidentally type the name of the storage engine wrong, a table might be created anyway. If you see a warning after creating the table, always check the warning:

mysql> create table t(i int) engine = ferrari;
Query OK, 0 rows affected, 1 warning (0.14 sec)

mysql> show warnings;
+-------+------+--------------------------------+
| Level | Code | Message                        |
+-------+------+--------------------------------+
| Error | 1286 | Unknown table engine 'ferrari' |
+-------+------+--------------------------------+
1 row in set (0.00 sec)

You can completely avoid this problem by including NO_ENGINE_SUBSTITUTION in the SQL_MODE. (In fact, I recommend to include it and make sure the proper mode is set when starting the server by including it in the [mysqld] section of your my.ini or my.cnf option file)

Testing Atomicity

Now, we execute a single statement that inserts two rows of data into the table:

mysql> insert into test_falcon values (1),(2);
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

The query executed succesfully, so we can assume two rows are now stored in the table. We can of course attempt to roll back to undo these changes:

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

Of course, when we check, the rows are still there:

mysql> select * from test_falcon;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.02 sec)

This is completely normal and expected behaviour. The rows were automatically committed right after the insert statement was executed: that is the meaning of autocommit.

We will now attempt to add three more rows to the table:

mysql> insert into test_falcon values (3),(4),(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Whoops! This statement did not execute succesfully. We attempted to insert three rows, but at least one of them would violate the PRIMARY KEY constraint if it would be inserted. So at least, the violating row is rejected. But what about the other rows? Let's find out:

mysql> select * from test_falcon;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0.00 sec)

Aha! The statement did not execute successfully, and therefore, all changes that would result from executing that statement are undone. With a posh word, we can say that the statement was executed atomically. The property of transactions that allows for atomic execution of statements is called atomicity

To say that the statement was executed atomically just means that the entire statement, and all of the changes it would achieve is executed as one single unit, undividable. If it succeeds, all associated changes on all rows it touches are made permanent. If it fails, none of the changes are made.

So, Atomicity ensures that statement execution is always complete: It either completely succeeds, or it completely fails. How does this compare to not using transactions at all? Well, we can test this with the MyISAM storage engine. The MyISAM storage engine does not implement transactions, and has no understanding of the autocommit setting. It is completely ignorant of it, because it can't commit or rollback anyway.

So, let's create a table again, this time using the MyISAM storage engine:

mysql> create table test_myisam (i int primary key)
    -> engine = myisam
    -> ;
Query OK, 0 rows affected (0.00 sec)

Again, we insert two rows in a single statement.

mysql> insert into test_myisam values (1),(2);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Now for the interesting bit. Again, we execute one statement in order to insert multiple rows, and again, we deliberately construct the statement in a manner to violate the primary key constraint:

mysql> insert into test_myisam values (3),(4),(1),(5);
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

Like with the test_falcon table, this statement is not acceptable, because there is again at least one row that would violate the PRIMARY KEY constraint if it would be inserted. So, how is this different from the previous test? Let's find out by looking what's in the table...

mysql> select * from test_myisam;
+---+
| i |
+---+
| 1 |
| 2 |
| 3 |
| 4 |
+---+
4 rows in set (0.00 sec)

Boom! The last statement attempted to insert no less than four rows, yet it added only two! Why only two? Well, after inserting two rows (3 and 4) succefully, a row was encountered that would violate the primary key constraint if it would be inserted (the value 1 was already present). At that point, an error condition was raised, and execution stopped. The remainder of the rows (5) was not even processed.

It's impossible give a clear answer to the question whether the statement succeeded or failed. We ordered four rows to be inserted, yet it failed to insert two of them. Clearly, the statement did not succeed completely. It did not fail completely either, because it succeeded in inserting two of the four rows.

This indeterminate state is the consequence of the lack of atomicity. The MyISAM engine does not support atomic execution of statements. The statement may succeed for some of the rows, and fail for others. This is simply impossible for the transactional engines. Statements on transactional statements are always executed atomically, regardless of the setting of autocommit.

Disabling Autocommit

So, I hope it's clear now why autocommit has everything to do with transactions. Still for a lot of people, the added value for using transactions is the ability to execute multiple statements within a single transactions. To do that, one needs to disable autocommit.

Within an application's session it is pretty easy to disable autocommit. One just needs to execute the proper SET statement:

SET autocommit=0

However, a lot of people are not really satisfied with this solution. Especially those that are used to Oracle, is seems daft to have to explicitly specify it.

There is a trick one can use to automatically disable autocommit for each newly created session. In the my.ini or my.cnf configuration file, you can specify the init_connect option. This option can be used to automatically execute a statement or sequence of statements at the beginning of each new session.

[mysqld]
init_connect='SET autocommit=0'

It's documented in the manual, and the example happens to use autocommit. Take heed though: the statements specified for the init_connect option are not executed for users that have the SUPER privilege.

Oracle SQL Developer 1.1 Supports MySQL

Being quite tied up in my daily job, I totally missed the that Oracle has been developing a database application development tool, SQL Developer. In fact, this tool was previously known as "project Raptor" and has now matured to version 1.1. The -to me- interesting news is that:

• It's free of charge.


• It runs on Linux, Mac OS/X and Windows


• Besides the Oracle database, it also supports other databases, including MySQL

Ok - I know all free software adepts will probably have left by now for three reasons, being that the title of this blog entry contains "Oracle", the tool is "free as in beer" and of course because the tool runs on Microsoft Windows. But let's not be distracted. Let's just take a few moments and see how to set up this tool to work with MySQL to figure out how this tool can be useful to us. I read that some people are actually using it to work with MySQL databases, so why not give it a try.

I had a quick go and installed the tool on the family Windows XP desktop. For a few sad reasons, I have been using that as my work PC, because the laptop I especially purchased to work on is being repaired at this moment. I might revisit this topic and install this tool on Linux too. It should not be too much of a problem.

Anyway - what do we have to do to get this tool up and running? Here goes:

Download and License

First of all, if you're going to download anything at all from Oracle, you need to sign in using your otn account. You'll find out soon enough because you'll be prompted to sign in. I set up my account years ago, and I can't really remember the details. I'm pretty sure you need at least an e-mail address. I have never received unsollicited emails from Oracle. It's probably due to me not checking or unchecking checkboxes in the account form, but I just want to put you at ease and let you know that the account does not mean that you will be receiving spam.

You can download the tool free of charge from the following location: http://www.oracle.com/technology/software/products/sql/index.html. You will have to accept the license agreement though - this can be done by selecting the radiobutton the very top of the page.

The license is particularly interesting for those that plan to use this tool in a production environment. To a large extent, the content is what I'd expect for a typical "free as in beer" product. I have no principal objections to using software without being able to modify it, and I certainly have no intention of removing any of the logos or whatever. From a practical point of view, it becomes harder that you can't freely distribute the software to third parties, but still does not have to pose a problem if you plan to use this software only for you own business. Or does it?

The programs may be installed on one computer only, and used by one person in the operating environment identified by us.
...
[You may not]...assign this agreement or give or transfer the programs or an interest in them to another individual or entity
...
[You may not]...disclose results of any program benchmark tests without our prior consent

I think that a literal interpretation of these stanzas from the license agreement sums up the major impediments. If my interpretation is correct, it means that the license agreement is between Oracle and an individual, meaning that each developer in your team must download it's own copy. Each individual has to sign the agreement individually too. You cannot dowload one copy and put it on your central file server - not even if the distribution is confined to only your own business. I hope that me blogging about the tool is not interpreted as benchmarking, because that would mean I'm violating the agreement.

Assuming you're willing to accept the license agreement, you should choose the download that's most appropriate for you. Dowloads are available in the following formats:

• .zip file including JDK 1.5, 65.2Mb. This is listed as the "Windows" distribution on the oracle site.


• .zip file without JDK, 38.4Mb if you have a JDK 1.5 (Update 6) installed already. This one is listed twice on the Oracle download site: once as a "Windows" distribution, once as a "Multiple Platforms" distribution. This probably means that this distribution will run on any Windows or *nix system that has some sort of JDK 1.5 installed.


• .tar.gz file for Mac OS/X, 38.3Mb. This also requires you to separately install J2SE 5.0 (Release 3)


• .rpm file for Linux, 38.4Mb. This also requires you to separately install JDK 1.5 (Update 6)

Installation and Configuration

I chose to download the second option - the "Multiple Platforms" distribution. I already had a JDK 1.5 installed so this seemed to be most appropriate for me. This yields a single zip file, sqldeveloper-2364-no-jre.zip which you have to unzip after downloading it. It unzips a sqldeveloper directory wich contains all the software.

Inside the sqldeveloper directory, you'll find a sqldeveloper.exe file and a sqldeveloper.sh file. Windows users double-click the .exe, and although I haven't tried if it works, my bet is that *nix users need to run the .sh bash script. There mey be some hidden magic to discover the java environment on your machine. However, I got a dialog which required me to browse for one:



You must take care to specify the java executable from a java development kit (JDK) - a java runtime environment (JRE) is not sufficient. I tried it, and got this error message:



The download page is pretty clear about it, but because the .zip file name contains no-jre I figured I'd give it a try anyway. So to avoid that problem, make sure you download and install JDK 1.5 (or a compatible implementation) and specify the java executable located in {JDK 1.5 home}/bin.



After correctly specifying the java program in the JDK, sqldeveloper will start. It will pop up the splashscreen, and ask you if you want to migrate your settings froma previous installation. You won't have to specify the location of java hereafter. That is, unless you upgrade your JDK - you might want to respecify it.

Unfortunately, I haven't found out yet where sqldeveloper stores the information. I noticed a jdk.conf file in the sqldeveloper\jdev\bin directory, but that appears to be there for that other Oracle IDE, JDeveloper. At least, I don't see anything being written there so it's probably not what you'd be looking for.

Post-installation tasks

In order to connect a MySQL database, you will need the MySQL jdbc driver, Connector/J. You can download it here.

Download the archive and unpack it at a location you feel is convenient. We then have to tell the SQL Developer tool about it too. To do that, navigate to the "Preferences..." item in the "Tools" menu.



This will open the Preferences dialog. In the treeview on the left side of the dialog, expand the "Databases" node, and activate the "Third Party JDBC Drivers" item beneath it.



Use the "Add Entry" button to open a file browser. From there, locate the MySQL Connector/J .jar file:



This is a one time action too . You won't have to repeat it, unless you upgrade Connector/J. On Linux, I just make a symbolic link to the most recent .jar file, and use instead of the actual .jar file. That way, you never have to worry about upgrades, because you simply re-wire the symbolic link to the newer .jar file. Alas, windows users are not so lucky and have to put up with manually reconfiguring their programs to use the newer driver.

Connecting to MySQL

Now we're ready to actually make a new connection. To do that, make sure you have the "Connections" toolpane activated. By default, it's on the left and active but you can drag and drop the different toolpanes almost everywhere, or you might accidentally have closed it. If you're in doubt, explicitly activate it using the "View" menu:



This should pop up and activate the "Connections" toolpane. Use the mouse to right-click the "Connections" node in the treeview on the "Connections" toolpane to create a new connection. A menu pops up. Although you might be tempted to think that you need the "New" item, you really need the "New Connection" item:



This pops up a dialog where you can specify the connection details. For MySQL, be sure to select the "MySQL" tab. You will see pretty much all the fields that are required to set up a connection:



The "Connection Name" field is just a local name for the connection for use within SQL Developer. The "Username" and "Password" correspond to the credentials corresponding to the database account. In the "MySQL" Tab, you can specify the hostname and port - all the usual data.

On the bottom of the dialog, there is a "Test" button, which is supposedly there in order to test the connection. It does pop up a small dialog, but immediately, it disappears, so I don't really know what it is telling me. I suspect this is by design, because you can see whether the tes was successfull: after hitting the test button, a status message will appear immediately above the Help button. The message will be shown in red if the test does not succeed:



If you want to, you can select a particular database, but you are not required to do so. If you want to, simply hit the "Choose Database" button, and use the combobox to select one of the available databases:



I find it a bit dissapointing that there seems to be no way to specify custom properties for the connection. There is a whole bunch of extra configuration properties implemented by the MySQL Connector/J, but there is no interface to specify them. Maybe this will be solved in a newer version of SQL Developer.

Using SQL Developer with MySQL

I played a bit with SQL developer, and so far, I think that the MySQL Support is rather limited. The tool lets you browse the database and execute SQL statements, but that's about it.

A few things I would expect to be implemented:

Database selection

In the connection dialog, we had an option to select a particular database. Surprisingly, all databases pop up anyway in the treeview. Of coure, if you don't specify a particular database, all databases pop up too, which I would expect. In that case however, I would expect that selecting a particular database would also USE that database. This is not the case: SQL statements will fail unless all identifiers are properly qualified. On the following screenshot, you might notice that the test database was selected, but under the hood, this does not USE the database.



Maybe all this can all be explained because of the different semantics between Oracle and MySQL. As you can see, all MySQL Databases or schemata (a synonymous term in MySQL) appear under the connection. But the icon used to adorn them, is that of a little puppet - a user. This is probably because in Oracle, a schema is not really a separate kind of thing - rather, it is the collection of all objects owned by a particular user, and a database is a container for these schemata.

Schema object editor

One of the first things I tried, was to create a new table. It seemed most reasonable to expand the node corresponding to my "test" database, to right-click the "Tables" node. As expected, a menu pops up, but surprisingly, there is no option to create a new table.



I also tried the marked toolbar buttons as these suggested to be meant to create a new object, but again no luck. I'm pretty sure the functionality is supported for Oracle databases, because the integrated help files do mention dialogs to edit schema objects.

When you try to edit an existing table, a set of tabpages appear that show the structure of the table.



However, in that case too, there is no functionality to modify or alter the table design. The status bar does suggest a real edit mode exists, but I can't seem to unlock or activate it. Assuming that it does work for Oracle databases, it's introspection only for MySQL. Creating and editing tables can be done only through DDL.

Working with Data

It seems that working with table data is largely confined to the same limitations as working with schema objects: existing data is displayed, and you can sort it, but that's as far as it goes.

There is no interface to insert new rows in an existing table; no interface to delete rows. You can move your cursor to a field, and click. A blinking cursor appears in the field, and suggests you can type a new value: you can't. Surprisingly, double-clicking on a field in an existing row does pop up an editor dialog - however, it does not let you modify the value. As you can see, the Ok button is disabled.



This puzzles me enormously. I mean, I can imagine why the developers waited a little before implementing full blown schema editors: I think it's safe to say that DDL is the part of SQL that has most differences between the various dialects. It's quite understandable too, because creating tables involves a specification of datatypes (which tend to differ between SQL dialects) and all kinds of physical properties, such as storage options.

However, once the schema objects are created, the SQL for working with the data is fairly similar for all SQL dialects alike. Ok, there are of course differences, mainly for the built-in functions, but once you have jdbc access, it should not be hard to provide a generic table data editor that works regardless of the underlying SQL dialect. However, it's not in SQL developer at present - at least, not for MySQL.

No resultset for CALL, EXECUTE and SHOW

SQL Developer does not display the resultset(s) returned by SHOW, EXECUTE and CALL statements. This is particularly annoying for CALL, because it makes it pretty hard to debug stored procedures.

An 'orphan' SELECT statement in a MySQL stored procedure will return a resultset to the calling client. Although some procedures return a resultset as part of their intended operation, I find this feature particularly useful for debugging purposes. With SQL Developer, there is no way to see the data returned by those statements, which means that debugging can only be done by setting up a separate table to log the debugging messages.

SET syntax

I mentioned that it is currently not possible to set all kinds of jdbc driver options when configuring a MySQL connection. Luckily, a lot of the options you'd want to set for the driver can normally be controlled also through the MySQL SET syntax. Alas - not with SQL Developer.



The SET keyword happens to identify a command that is implemented by Oracle's SQL*Plus command line query tool. Apparently, there is some logic between the SQL command editor in SQL Developer and the jdbc driver which senses that the SET command is not to be sent to the server, and hence it is skipped.

I can see the usefulness of not letting SQL*Plus commands through to an Oracle server: it makes it possible to use SQL Developer to run legacy SQL*Plus scripts. However, the current implementation blocks a part of the commands in MySQL's SQL dialect. I know how all kinds of people will tell me that you should never use SET commands anyway because they are non-standard and proprietary and all that - fact is that sometimes, you simply need these commands to get some work done. Whatever work that is, you won't get it done with SQL Developer, because it decides to skip these commands.

I think this could easily have been avoided too. The SQL Developer tool can detect which driver it is talking too. That should enable it to block the SET commands from being sent to Oracle servers, and simply allow them to pass through to non-Oracle servers. I know MS SQL Server implements the SET command too - MS SQL Server users will encounter similar problems.

Stored Procedure Parameters

I really don't know how long I can keep this up - my guess is, too long. Therefore, one more to wrap it up.

SQL Developer does not provide a stored procedure editor, wich is rather unsurprising for a tool that does not provide a table editor. However, we can just resign to using DDL statements - a stored procedure editor would not add that much value anyway.



It would be nice to be able to at least retrieve the stored procedure code using SQL Developer, right? No such luck. When you use the treeview to browse for you procedure, you can retrieve the code. Alas, the parameters are missing from the code. They're just not there:



I suspect this is because SQL Developer tries to use the information_schema.PARAMETERS view, which is not implemented in MySQL. Of course, I hope MySQL will eventually implement that, but even then - there is a perfectly good solution to this problem: the SHOW CREATE PROCEDURE command. Of course, this is MySQL proprietary syntax etc, but the point is, if this tool is to support older MySQL releases, it will need to use that method in order to be able to offer the user the possibility to edit stored procedures.

Now what?

Good question. I think it's very encouraging to see Oracle adding connectivity for other database products to their tool. I suspect I would like to use this tool too, provided that it would actually support MySQL Databases at least half as good as it supposedly does so for Oracle Databases.

Frankly, at this point, I wouldn't exactly say SQL Developer supports MySQL. It allows you to connect to it and to issue some queries against it. I can imagine that some people like the ability to use a graphical user interface to browse the database too. But I can't help but conclude that in it's present form, SQL Developer cannot be used for any serious MySQL Application development. The graphical user interface does just not provide enough functionality to perform even the most basic tasks, and even as a Query tool it's lacking too much functionality.

I really hope that what we're seeing now is just the result of a premature release of this functionality. Also, to the best of my knowledge, it's quite uncommon for Oracle to develop tools that even try to support other database products than their own, so I guess we should have a little patience before dismissing this tool alltogether. I will keep an eye on this tool, but my guess it will take at least a year before we'll see it mature enough to be seriously usable.

Is there nothing good to say about SQL Developer in it's present form? Well, so far, it seems to be pretty stable. I found it to be reasonable fast too once it's up and running. As far as it goes, the GUI is pretty intuitive too. But except for the stability, these are all minor pro's compared to the major con's in my opinion. Let's hope it gets better.

Refactoring: Derived table, UNION...WTF?

I greatly admire the works of the Dutch literary author Gerard Reve (1923 - 2006). On many occasions, he was asked whether his stories were real-life stories, and he always answered like this:

If you mean "did this sequence of events factually take place?" then I can be brief: No. And that's a good thing too, because if a writer would describe reality, the result would be very hard to believe, if not completely inconceivable. The course taken by real life is just too crazy. A true description is bound to be seem like a constructed mannerism and no reader can be expected to believe even one word of it.
However, as far as the single events are concerned, I exclusively use only those things bourne from reality.

If you ever read The Daily WTF you will probably agree that this point of view makes a lot of sense. Each time a really crazy or insensible piece of code pops up on the Daily's there are always people commenting, saying something like:

No...this just cannot be true. I refuse to believe that software developers from large professional organisations can deliver this kind of crap..and get away with it.

Well, the odds are that indeed the pieces of code that appear there should not be taken too literally. I mean, for one thing, the code appears with indentation, and sometimes even with comments: no way this is real code. Real Code is Worse than what you'll ever find on The Daily WTF.

My take on WTF

The reason I'm choosing this topic because I was recently asked to take a look at a database query which was giving "...trouble...". Usually this means: "...not performing as fast as we'd like...".

In this case, the people having trouble were actually consultants from a professional IT service provider. They were trying to migrate some enterprise application from a big, operational Oracle database to MySQL 5.0 Server. Their efforts were all experimental, and so ar, they were quite enthusiastic about MySQL. However, there were two troublesome queries performancewise. (I will discuss only one - the other one is also a story on it's own.)

They found that in this particular case, Oracle took 30ms whereas MySQL took a little over one minute. Of course this is a huge difference, and I was immediately challenged.

The query I got was somewhat like this:

select  *
from    (
        select  film_id
        ,       title
        ,       release_year
        ,       'PG'
        from    film
        where   rating in ('PG','PG-13')
        union
        select  film_id
        ,       title
        ,       release_year
        ,       'R'
        from    film
        where   rating in ('R','NC-17')
        union
        select  film_id
        ,       title
        ,       release_year
        ,       rating
        from    film
        where   rating not in ('PG','PG-13','R','NC-17')
        ) film
where   release_year > 2005
and     release_year <= 2006

Well, of course, their enterprise application was not built on the sakila sample database: I stylized the original query to make it tangible. But don't let that distract you - just look.

Now I'm not saying that I'm not guilty of writing bad code. I mean, we all started somewhere, that is: from nothing. We all had to learn, and alas, in a lot of cases, you can only learn by making mistakes. However, things like the Daily WTF should be valuable eye-openers for developers and software engineers to always remain focussed on monitoring the quality of code. Even, or especially if it's their own.

So, instead of taking the chuckly attitude of laughing at someone else's apparent WTF, I'll try and do a step by step refactoring of this query. This will probably not be interesting for advanced users, and they can still have their laugh. If you can't appreciate the WTF-edness, keep reading: this article is for you.

A Simple Query

What we see here is essentially a simple query. A subselect appears in the FROM-clause: this is sometimes referred to as a derived table or an inline-view. If we forget the complexity of the subquery for a while and pretend that it actually is a real view, we can see easily that it really is simple:

select  ...
from    (/*complexity goes here*/) film
where   ...

So, no JOIN, no GROUP BY, and not correlelated subselects. Normally, this query should be quite fast, right? So why is it so slow now?

Well, we could use the basic performance and tuning instincts, and start asking questions like:

• What engine is used for the film table?


• Is there an index on release_year?


• What is the size of the supa_dupa_xyz buffer?

However, I don't think that's really necessary at this point. Right now, we need to know but one thing, and that is that the resultset of an inline view is computed before as a separate step.

Using EXPLAIN

We sure can witness this using EXPLAIN:

explain
select  *            
from    (                    -- 1               
        ...                  --    2
        UNION
        ...                  --    3
        UNION
        ...                  --    4
        ) film               --        union result 
where   ...
;
+----+--------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY      | <derived2>   | ALL  | NULL          | NULL | NULL    | NULL | 1000 | Using where |
|  2 | DERIVED      | film         | ALL  | NULL          | NULL | NULL    | NULL |  952 | Using where |
|  3 | UNION        | film         | ALL  | NULL          | NULL | NULL    | NULL |  952 | Using where |
|  4 | UNION        | film         | ALL  | NULL          | NULL | NULL    | NULL |  952 | Using where |
|    | UNION RESULT | <union2,3,4> | ALL  | NULL          | NULL | NULL    | NULL | NULL |             |
+----+--------------+--------------+------+---------------+------+---------+------+------+-------------+

The explain result is not too hard to interpret: The first row has the select_type equal to PRIMARY and this corresponds to the outer query. The table mentions that it queries <derived2>, which means it queries on the subquery (I don't know why it does not use the subquery alias - it just doesn't).

The second, third and fourth row in the explain result refer to the queries that are UNION-ed together inside the subselect. It's a bit puzzling that the select_type for the second row is marked DERIVED, but it just means it's the first thing that is evaluated as part of the subquery.

The third and fourth row correspond to the sets that are to be UNION-ed to the total result of the subquery, and that's why you see UNION there in the select_type column.

For those that do not know: UNION is a so-called set operator, it takes the resultsets produced by the queries on the left and right hand side, and constructs a new resultset out of it that contains the rows from the resultset operands. So, UNION 'adds up' rows in a vertical manner. (The much used and well known JOIN operator 'adds up' resultsets in the horizontal direction)

The final row represents the actual 'adding up' of the all the rows from the three queries inside the subselect, and that's why it has UNION RESULT in the select_type column. This step is not trivial, because UNION is required to filter out any duplicate rows in the final result after applying the UNION. More about that later.

Do we really need a derived table?

Now, we were about to think whether we really need the derived table. What would happen if we'd simply expand the contents of the subquery? It will require some work, because we would have to duplicate the WHERE clause of the outer query into the individual UNION-ed queries:

select  film_id
,       title
,       release_year
,       'PG'
from    film
where   rating in ('PG','PG-13')
and     release_year > 2005
and     release_year <= 2006
union
select  film_id
,       title
,       release_year
,       'R'
from    film
where   rating in ('R','NC-17')
and     release_year > 2005
and     release_year <= 2006
union
select  film_id
,       title
,       release_year
,       rating
from    film
where   rating not in ('PG','PG-13','R','NC-17')
and     release_year > 2005
and     release_year <= 2006

So, what would this do to the result from EXPLAIN? Well, we don't expect the subquery step again in oour result:

+----+--------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type  | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY      | film         | ALL  | NULL          | NULL | NULL    | NULL |  952 | Using where |
|  2 | UNION        | film         | ALL  | NULL          | NULL | NULL    | NULL |  952 | Using where |
|  3 | UNION        | film         | ALL  | NULL          | NULL | NULL    | NULL |  952 | Using where |
|    | UNION RESULT | <union1,2,3> | ALL  | NULL          | NULL | NULL    | NULL | NULL |             |
+----+--------------+--------------+------+---------------+------+---------+------+------+-------------+

The result does not show a huge difference from what we had: first our PRIMARY step selected from the derived table - now it does so directly from the union result. However, in reality, it can make all the difference.

In our original query, the whole union result was first built, and after that, the WHERE-clause on release_year was applied. This means that the whole of the union result must be scanned row by row to filter for the right release_year.

The modification 'pushed down' the WHERE-clause and applied it directly on the individual sets. Apart from enabling us to cut out the subselect (and thus, the extra scan), a nice side effect is that this could potentially make the UNION result faster too, because each WHERE-clause could filter out rows, leaving less total rows to 'sum up' by the UNION operation.

So, when do I use a derived table?

Why would anybody ever use a inline view like this? What's the point, can't we always expand it into the outer query? Well, No. There is one case where it is literally impossible to expand it. That's when you're aggregating aggregates.

An example: suppose you have a soccer team database, and you want to take the average of the maximum number of goals per season per player. That's two levels of aggregation, first max(no_of_goals) per player per season, and then, the average of that (also per player per season).

You can find another example here on nesting repeated groups.

Why a UNION? Can't we use UNION ALL?

I just mentioned that the UNION operator discards duplicate rows from the final resultset. Although that can be a nice functionality now and then it really is rarely needed.

In most cases, the sets in the union don't have any overlap. Can we say something about this particular query? Well, first of all, the three queries all select the same columns from the film table, so potentially there could be overlap between he resultsets. However, the first two queries each select a different constant in the SELECT-list:

select  ..
,       'PG'
from    ..
where   ..
union
select  ..
,       'R'
from    ..
where   ..

So, by definition, these two queries can never yield duplicates, meaning that we don't need the UNION to discard any duplicate rows: on the contrary, we'd rather have it skip that step because that is most likely faster. We can do that using UNION ALL:

select  ..
,       'PG'
from    ..
where   ..
union all
select  ..
,       'R'
from    ..
where   ..

It's actually a good idea to always write UNION ALL in case you know for sure that there is no everlap. Even if performance is not an issue, it makes the code more explicit. This makes it easier for the database to devise a query plan, and it will also be easier to fellow developers to see the exact intention.

Concerning the third query in the UNION, what is happening with that one? Well, it also selects from the code>filmtable, and again, it uses the same columns. However this one does not select a constant, but the rating column.

Now if we only could somehow prove that the values in the rating column here will never contain either of the values 'PG' or 'R' (the constants selected by the previous two queries) we must conclude that this resultset can never overlap with the resultset from either of the other queries. In other words, we would be able to safely use the UNION ALL trick here too.

The WTF-edness

We can find out if the third query will ever select a 'PG' or 'R' in the rating column by loooking at the WHERE-clause:

where   rating not in ('PG','PG-13','R','NC-17')

Mmm, the WHERE-clause explicitly excludes the 'PG' and 'R' values. At least, that means that we can use the UNION ALL trick here too.

But wait a minute...let's look at the other WHERE-s too, ok?

select  ..
from    ..
where   rating in ('PG','PG-13')
and     ..
union all
select  ..
from    ..
where   rating in ('R','NC-17')
and     ..
union all
select  ..
from    ..
where   rating not in ('PG','PG-13','R','NC-17')
and     ..

Now that's beatiful: the third set selects exactly all the data not selected by the first and second. This means we can skip all the mumbo-jumbo and just write:

select  film_id
,       title
,       release_year
,       case rating
            when 'PG-13' then 'PG'
            when 'NC-17' then 'R'
            else rating
        end
from    film
where   release_year > 2005
and     release_year <= 2006

So the CASE expression now makes up for the rating column.

Needless to say that the explain output has shrunk quite a lot:

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL |  952 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

Finally...

Just a short summary:

• Real-life code can be very bad


• Avoid derived tables


• Use UNION ALL instead of UNION

Sadly I have not been able to test it myself. I'm quite confident that the reasoning behind this refactoring is sound, but you never know...

If you have any questions or suggestions, post a comment to the article.