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: The maximum value of an integer

Did you ever have the need to find the maximum value of an integer in MySQL? Yeah, me neither. Anyway, some people seem to need this, and this is what I came up with:

SELECT ~0 as max_bigint_unsigned
,      ~0 >> 32 AS max_int_unsigned
,      ~0 >> 40 AS max_mediumint_unsigned
,      ~0 >> 48 AS max_smallint_unsigned
,      ~0 >> 56 AS max_tinyint_unsigned
,      ~0 >> 1  AS max_bigint_signed
,      ~0 >> 33 AS max_int_signed
,      ~0 >> 41 AS max_mediumint_signed
,      ~0 >> 49 AS max_smallint_signed
,      ~0 >> 57 AS max_tinyint_signed
\G

*************************** 1. row ***************************
   max_bigint_unsigned: 18446744073709551615
      max_int_unsigned: 4294967295
max_mediumint_unsigned: 16777215
 max_smallint_unsigned: 65535
  max_tinyint_unsigned: 255
     max_bigint_signed: 9223372036854775807
        max_int_signed: 2147483647
  max_mediumint_signed: 8388607
   max_smallint_signed: 32767
    max_tinyint_signed: 127
1 row in set (0.00 sec)

In case you're wondering how it works, read up on what the tilde (~) does: it peforms a bitwise negation. In other words, if flips bits that are 1 to 0, and vice versa. So, ~0 means, set all the bits to 1, because in the integer one 0, all the bits are a binary 0. Now, in MySQL, at runtime, there is only one integer type, which is an 8-byte integer value or a bigint. Therefore, ~0 is by definition the largest possible integer value.

MySQL defines a family of integer types for storage: bigint (8 bytes), int (4 bytes), mediumint (3 bytes), smallint (2 bytes) and tinyint (1 byte). To find the maximum values of those types, we can use the right-bitshift operator >> to push the most-significant bits at the left side of ~0 down to the right, for the appropriate number of bytes to get the maximum values of the other integer flavors. So,

int type: big                                int      medium    small    tiny
bit #:    64       56       48       40       32       24       16        8      1  
~0       = 11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111111 = 18446744073709551615
~0 >> 32 = 00000000 00000000 00000000 00000000 11111111 11111111 11111111 11111111 = 4294967295
~0 >> 40 = 00000000 00000000 00000000 00000000 00000000 11111111 11111111 11111111 = 16777215
~0 >> 48 = 00000000 00000000 00000000 00000000 00000000 00000000 11111111 11111111 = 65535
~0 >> 56 = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 11111111 = 255

Now, for each of the integer flavors, MySQL lets you define them to be either signed or unsigned. This is implemented using a so-called sign bit. The sign bit is the most significant bit (so, bit #64 in a bigint, bit #32 in an int, and so on and so forth). If the sign bit equals 0, the integer is positive and if it equals 1, the integer is negative. So, to get the maximum values for the signed integer flavors, we can use the same recipe, we just need to push the bits even one more bit to the right, like so:

int type: big                                int      medium    small    tiny
bit #:    64       56       48       40       32       24       16        8      1  
~0 >>  1 = 01111111 11111111 11111111 11111111 11111111 11111111 11111111 11111111 = 9223372036854775807
~0 >> 33 = 00000000 00000000 00000000 00000000 01111111 11111111 11111111 11111111 = 2147483647
~0 >> 41 = 00000000 00000000 00000000 00000000 00000000 01111111 11111111 11111111 = 8388607
~0 >> 49 = 00000000 00000000 00000000 00000000 00000000 00000000 01111111 11111111 = 32767
~0 >> 57 = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 01111111 = 127

Restoring XML-formatted MySQL dumps

EAVB_VFZUHIARHI To whom it may concern -

The mysqldump program can be used to make logical database backups. Although the vast majority of people use it to create SQL dumps, it is possible to dump both schema structure and data in XML format. There are a few bugs (#52792, #52793) in this feature, but these are not the topic of this post.

XML output from mysqldump

Dumping in XML format is done with the --xml or -X option. In addition, you should use the --hex-blob option otherwise the BLOB data will be dumped as raw binary data, which usually results in characters that are not valid, either according to the XML spec or according to the UTF-8 encoding. (Arguably, this is also a bug. I haven't filed it though.)

For example, a line like:

mysqldump -uroot -pmysql -X --hex-blob --databases sakila

dumps the sakila database to the following XML format:

<?xml version="1.0"?>
<mysqldump xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <database name="sakila">
    <table_structure name="actor">
      <field Field="actor_id" Type="smallint(5) unsigned" Null="NO" Key="PRI" Extra="auto_increment" />
      <field Field="first_name" Type="varchar(45)" Null="NO" Key="" Extra="" />
      <field Field="last_name" Type="varchar(45)" Null="NO" Key="MUL" Extra="" />
      <field Field="last_update" Type="timestamp" Null="NO" Key="" Default="CURRENT_TIMESTAMP" Extra="on update CURRENT_TIMESTAMP" />
      <key Table="actor" Non_unique="0" Key_name="PRIMARY" Seq_in_index="1" Column_name="actor_id" Collation="A" Cardinality="200" Null="" Index_type="BTREE" Comment="" />
      <key Table="actor" Non_unique="1" Key_name="idx_actor_last_name" Seq_in_index="1" Column_name="last_name" Collation="A" Cardinality="200" Null="" Index_type="BTREE" Comment="" />
      <options Name="actor" Engine="InnoDB" Version="10" Row_format="Compact" Rows="200" Avg_row_length="81" Data_length="16384" Max_data_length="0" Index_length="16384" Data_free="233832448" Auto_increment="201" Create_time="2009-10-10 10:04:56" Collation="utf8_general_ci" Create_options="" Comment="" />
    </table_structure>
    <table_data name="actor">
      <row>
        <field name="actor_id">1</field>
        <field name="first_name">PENELOPE</field>
        <field name="last_name">GUINESS</field>
        <field name="last_update">2006-02-15 03:34:33</field>
      </row>

...many more rows and table structures...

  </database>
</mysqldump>

I don't want to spend too much time discussing why it would be useful to make backups in this way. There are definitely a few drawbacks - for example, for sakila, the plain SQL dump, even with --hex-blob is 3.26 MB (3.429.358 bytes), whereas the XML output is 13.7 MB (14,415,665 bytes). Even after zip compression, the XML formatted dump is still one third larger than the plain SQL dump: 936 kB versus 695 kB.

Restoring XML output from mysqldump

A more serious problem is that MySQL doesn't seem to offer any tool to restore XML formatted dumps. The LOAD XML feature, kindly contributed by Erik Wetterberg could be used to some extent for this purpose. However, this feature is not yet available (it will be available in the upcoming version MySQL 5.5), and from what I can tell, it can only load data - not restore tables or databases. I also believe that this feature does not (yet) provide any way to properly restore hex-dumped BLOB data, but I really should test it to know for sure.

Anyway.

In between sessions of the past MySQL users conference I cobbled up an XSLT stylesheet that can convert mysqldump's XML output back to SQL script output. It is available under the LGPL license, and it is hosted on google code as the mysqldump-x-restore project. To get started, you need to download the mysqldump-xml-to-sql.xslt XSLT stylesheet. You also need a command line XSLT processor, like xsltproc. This utility is part of the Gnome libxslt project, and is included in packages for most linux distributions. There is a windows port available for which you can download the binaries.

Assuming that xsltproc is in your path, and the XML dump and the mysqldump-xml-to-sql.xslt are in the current working directory, you can use this command to convert the XML dump to SQL:

xsltproc mysqldump-xml-to-sql.xslt sakila.xml > sakila.sql

On Unix-based systems you should be able to directly pipline the SQL into mysql using

mysql -uroot -pmysql < `xsltproc mysqldump-xml-to-sql.xslt sakila.xml`

The stylesheet comes with a number of options, which can be set through xsltproc's --stringparam option. For example, setting the schema parameter to N will result in an SQL script that only contains DML statements:

xsltproc --stringparam schema N mysqldump-xml-to-sql.xslt sakila.xml > sakila.sql

Setting the data option to N will result in an SQL script that only contains DDL statements:

xsltproc --stringparam data N mysqldump-xml-to-sql.xslt sakila.xml > sakila.sql

. There are additional options to control how often a COMMIT should be issued, whether to add DROP statements, whether to generate single row INSERT statements, and to set the max_allowed_packet size.

What's next?

Nothing much really. I don't really recommend people to use mysqldump's XML output. I wrote mysqldump-x-restore for those people that inherited a bunch of XML formatted dumps, and don't know what to do with them. I haven't thouroughly tested it - please file a bug if you find one. If you actually think it's useful and you want more features, please let me know, and I'll look into it. I don't have much use for this myself, so if you have great ideas to move this forward, I'll let you have commit access.

That is all.

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: Partition-wise backups with mysqldump

To whom it may concern,

in response to a query from André Simões (also known as ITXpander), I slapped together a MySQL script that outputs mysqldump commands for backing up individual partitions of the tables in the current schema. The script is maintained as a snippet at MySQL Forge a gist on github.

How it works

The script works by querying the information_schema.PARTITIONS system view to generate an appropriate expression for mysqldump's --where option. The generated command also redirects the output to a file with this name pattern:

<schema>.<table>.<partition-name>.sql

For example, for this table (taken from the MySQL reference manual):

CREATE TABLE members (
    firstname VARCHAR(25) NOT NULL,
    lastname VARCHAR(25) NOT NULL,
    username VARCHAR(16) NOT NULL,
    email VARCHAR(35),
    joined DATE NOT NULL
)
PARTITION BY RANGE( YEAR(joined) ) (
    PARTITION p0 VALUES LESS THAN (1960),
    PARTITION p1 VALUES LESS THAN (1970),
    PARTITION p2 VALUES LESS THAN (1980),
    PARTITION p3 VALUES LESS THAN (1990),
    PARTITION p4 VALUES LESS THAN MAXVALUE
);

the script generates the following commands:

mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) < 1960" test members > test.members.p0.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1960 and  YEAR(joined) < 1970" test members > test.members.p1.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1970 and  YEAR(joined) < 1980" test members > test.members.p2.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1980 and  YEAR(joined) < 1990" test members > test.members.p3.sql
mysqldump --user=username --password=password --no-create-info --where=" YEAR(joined) >= 1990 and  YEAR(joined) < 18446744073709551615" test members > test.members.p4.sql

Tip: in order to obtain directly executable output from the mysql command line tool, run the script with the --skip-column-names (or -N) option.

Features

Currently, the script supports the following partitioning methods:

• HASH


• LIST


• RANGE

Limitations

The LINEAR HASH method is currently not supported, but I may implement that in the future.

Currently I do not have plans to implement the KEY and LINEAR KEY partitioning methods, but I may reconsider if and when I have more information about the storage-engine specific partitioning functions used by these methods.

Finally, I should point out that querying the information_schema.PARTITIONS table is dog-slow. This may not be too big of an issue, however it is pretty annoying. If anybody has some tips to increase performance, please let me know.

Acknowledgements

Thanks to André for posing the problem. I had a fun hour of procrastination to implement this, and it made me read part of the MySQL reference manual on partitioning.

I also would like to thank Giuseppe Maxia (the Datacharmer) for providing valuable feedback. If you're interested in either partitioning or the mysql command line, you should visit his tutorials at the MySQL conference, april 12-15, 2010.

Greatest N per group: top 3 with GROUP_CONCAT()

In my opinion, one of the best things that happened to Planet MySQL lately, is Explain Extended, a blog by Alex Bolenok (also known as Quassnoi on Stackoverflow).

I never had the pleasure of meeting Alex in person, but his articles are always interesting and of high quality, and the SQL wizardry he pulls off is downright inspiring. I really feel humbled by the creativity of some of his solutions and his apparent experience with multiple RDBMS products.

Alex' most recent post is about aggregation, and finding a top 3 based on the aggregate:

In MySQL I have a table called meanings with three columns: word, meaning, person. word has 16 possible values, meaning has 26. A person assigns one or more meanings to each word. In the sample above, person 1 assigned two meanings to word 2. There will be thousands of persons. I need to find the top three meanings for each of the 16 words, with their frequencies. Is it possible to solve this with a single MySQL query?

Alex presents a solution that uses GROUP_CONCAT basically as a poor man's windowing function, a technique I have described on several occasions in the past for ranking, median and percentile solutions in MySQL.

Now, Alex' solution is very clever and there are some elements that I think are very creative. That said, I think his solution can be improved still. Normally I wouldn't write a blog about it, and simply leave a comment on his blog, but his blog supports comments only for general articles, which is why I present it here:

SELECT  word
,       CONCAT(
            SUBSTRING_INDEX(
                GROUP_CONCAT(meaning ORDER BY num DESC), ',', 1
            )
        ,   ' ('
        ,   SUBSTRING_INDEX(
                GROUP_CONCAT(num ORDER BY num DESC), ',', 1
            ) / SUM(num) * 100
        ,   '%)'
        ) rank1
,       CONCAT(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    GROUP_CONCAT(meaning ORDER BY num DESC), ',', 2
                ), ',', -1
            )
        ,   ' ('
        ,   SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    GROUP_CONCAT(num ORDER BY num DESC), ',', 2
                ), ',', -1
            ) / SUM(num) * 100
        ,   '%)'
        ) rank2
,       CONCAT(
            SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    GROUP_CONCAT(meaning ORDER BY num DESC), ',', 3
            ), ',', -1)
        ,   ' ('
        ,   SUBSTRING_INDEX(
                SUBSTRING_INDEX(
                    GROUP_CONCAT(num ORDER BY num DESC), ',', 3
                ), ',', -1
            ) / SUM(num) * 100
        ,   '%)'
        ) rank3
FROM    (
        SELECT      word, meaning, COUNT(*) num
        FROM        t_meaning m
        GROUP BY    word,meaning
        ) a
GROUP BY word

This gives me output like this:

+------+--------------+--------------+--------------+
| word | rank1        | rank2        | rank3        |
+------+--------------+--------------+--------------+
|    1 | 16 (3.9728%) | 17 (3.9648%) | 12 (3.9632%) |
|    2 | 9 (3.9792%)  | 10 (3.9632%) | 20 (3.9328%) |
|    3 | 20 (3.9744%) | 13 (3.968%)  | 1 (3.9648%)  |
|    4 | 26 (3.952%)  | 7 (3.9456%)  | 17 (3.9424%) |
|    5 | 9 (4.008%)   | 21 (3.9824%) | 20 (3.936%)  |
|    6 | 19 (3.9504%) | 10 (3.9488%) | 13 (3.9408%) |
|    7 | 23 (4.0464%) | 12 (3.976%)  | 19 (3.9648%) |
|    8 | 23 (4.0112%) | 3 (4.0096%)  | 8 (3.9328%)  |
|    9 | 10 (4.016%)  | 19 (3.984%)  | 15 (3.9616%) |
|   10 | 10 (4.0304%) | 14 (3.9344%) | 11 (3.9312%) |
|   11 | 16 (3.9584%) | 6 (3.9296%)  | 19 (3.9232%) |
|   12 | 7 (3.9968%)  | 1 (3.9392%)  | 26 (3.9264%) |
|   13 | 8 (4.048%)   | 25 (3.9712%) | 23 (3.9616%) |
|   14 | 16 (3.9936%) | 26 (3.9632%) | 4 (3.9536%)  |
|   15 | 22 (4.0608%) | 12 (4.0048%) | 1 (3.9632%)  |
|   16 | 14 (4.0032%) | 18 (3.9712%) | 4 (3.9488%)  |
+------+--------------+--------------+--------------+
16 rows in set (0.63 sec)

On my laptop, my solution is about 30% faster than the one presented by Alex. Personally I think mine is easier to understand too, but that is a matter of taste.

Anyway, I'm just posting this to share my solution - I do not intend to downplay the one presented by Alex. Instead, I invite everyone interested in SQL, MySQL and PostgreSQL to keep an eye on Alex' blog as well as his excellent answers on Stackoverflow. He's an SQL jedi master in my book :)

Of course, if you have a better solution to crack this problem in MySQL, please leave a comment. I'd love to hear what other people are doing to cope with these kinds of queries.