MQL-to-SQL: A JSON-based query language for your favorite RDBMS - Part I

Yesterday, I wrote about how I think this year's MySQL conference will differ from prior editions. I also wrote that I will attend and that I will be speaking on MQL-to-SQL.

I promised I would explain a little bit more background about my talk, so here's the first installment.

Abstract: MQL is a JSON-based database query language that has some very interesting features as compared to SQL, especially for modern (AJAX) web-applications. MQL is not a standard database query language, and currently only natively supported by Freebase. However, with MQL-to-SQL, a project that provides a SQL adapter for MQL, you can run MQL queries against any RDBMS with a SQL interface.

This article covers mostly background information on modern web applications, JavaScript and JSON. This background information should help you understand why a JSON-based database query language is a good fit for modern web applications. A following installment will discuss the MQL database query language and how it relates to SQL. A third article will cover the mql-to-sql project itself.

MQL, a JSON-based database query language

MQL (pronounced as Mickle) is an abbreviation of Metaweb Query Language, which is the JSON-based database query language natively supported by Freebase. If you're unfamiliar with the terms Metaweb and Freebase, see this quote from Wikipedia about Metaweb:

Metaweb Technologies, Inc. is a United States company based in San Francisco that is developing Freebase, described as an "open, shared database of the world's knowledge".

...and this quote about Freebase:

On March 3, 2007 Metaweb publicly announced Freebase, described by the company as "an open shared database of the world's knowledge," and "a massive, collaboratively-edited database of cross-linked data." Often understood as a wikipedia-turned-database, Freebase provides an interface that allows non-programmers to fill in structured, or 'meta-data', of general information, and to categorize or connect data items in meaningful, or 'semantic' ways.

So, Freebase is a database of just about everything (think of it as a machine readable version of wikipedia), and MQL is its query language.

As a quick primer on the MQL query language, here's a simple MQL query (taken from the Freebase manual)

{
  "query": {
    "type":"/music/artist",
    "name":"The Police",
    "album":[]
  }
}

...and here's the result:

{
  "status": "200 OK", 
  "code": "/api/status/ok", 
  "transaction_id":"cache;cache01.p01.sjc1:8101;2008-09-18T17:56:28Z;0029",
  "result": {
    "type": "/music/artist", 
    "name": "The Police",
    "album": [
      "Outlandos d'Amour", 
      "Reggatta de Blanc", 
      "Zenyatta Mondatta", 
      "Ghost in the Machine", 
      "Synchronicity"
    ]
  }
}

I won't discuss these samples just yet - the MQL query language is the topic of the next installment. This is just a quick sample to give you an idea what MQL queries and their results look like. If you're didn't recognize the syntax already - read on, the JSON syntax is covered in the next section of this article.

The collection of data accumulated in Freebase is impressive - you should check it out sometime. However, this post is not so much about Freebase - it's the MQL query language I want to discuss here.

JSON

MQL is based on JSON (pronounced as Jason), which is an abbreviation of JavaScript Object Notation. If you're not familiar with JSON, hang on and read all of this section - it provides just enough background to understand why it is highly relevant for modern web-applications, and it provides a good-enough description of the JSON syntax for you to read and write MQL queries. You might also want to check out the Wikipedia entry on JSON for an objective overview.

If you are already familiar with JSON, then you can safely skip through to the next section. But please do take a minute to review the JSON object code sample - it is used as basis for developing simple MQL queries in the next article in this series.

Now, back to JSON.

What is JSON?

JSON is a data exchange format. Syntactically, JSON is a proper subset of JavaScript, the de-facto web browser scripting language. The JSON JavaScript subset is defined in such a way that it can express JavaScript values and objects of arbitrary complexity. However, JSON cannot be used to create an executable program - all dynamic, executable elements (functions) have been stripped away.

Because JSON literally is JavaScript (just not the full set), JavaScript programs can easily read and write data expressed as JSON. For this reason, JSON is often characterized as a JavaScript object serialization format.

An excellent description of the JSON syntax can be found at the JSON homepage. But if you don't feel like reading up on the details of JSON, no worries - you just need to remember a few JSON features to read and write MQL queries:

• 3 scalar* data types:
  • strings - for example: "Oxygen", or 'O'
  • numbers - like 8 (integer), 13.6181 (float) and -2.1835e+2 (float)
  • booleans - true and false are the only possible boolean values
• 2 composite data types:
  • arrays - an ordered comma-separated list of values, enclosed in square braces: ["Oxygen-16", "Oxygen-17", "Oxygen-18"]. The individual values in an array are usually referred to as elements
  • objects - an unordered comma-separated list of key/value pairs (which are uniquely named items), enclosed in curly braces:

    {
      "name": "Oxygen",
      "symbol": 'O',
      "atomic_number": 8,
      "ionization_energy": 13.6181,
      "melting_point": -2.1835e+2,
      "isotopes": ["Oxygen-16", "Oxygen-17", "Oxygen-18"]
    }​

    The key/value pairs of an object are usually called properties or members. Note that name and value are separated by a colon (:). Keys are strings, and have to be quoted. Values can be of any of the data types described above, including arrays (like the "isotopes" property in the example above) and objects.
    
• A special null-value (which actually constitutes a data type of its own)

(* I should point out that JavaScript is an object-oriented language. At runtime, all data types described above, including the array and "scalar" types, are in fact objects. Objects are by definition composite, and not scalar. However, the list above is about JSONs syntactical constructs, not about their runtime JavaScript representation)

As you can see, JSON provides a few very simple rules. Yet it allows you to build data structures of arbitrary complexity: the object example above illustrates how you can bundle a collection of named values together to represent structures of a higher order. In this particular case, we denoted an object that represents the chemical element Oxygen. And, we needn't have stopped here: we could've added more key/value pairs to represent other complex properties of the element Oxygen, representing things like the person who first discovered it, a list of well known substances that contain Oxygen and so on and so forth.

AJAX and JSON

I already mentioned something about JSON in relation to JavaScript and modern web applications. By modern web applications, I mean web applications that offer a rich and highly interactive user interface that is based on AJAX (An Acronym for Asynchronous JavaScript And XML) technology.

A key feature of AJAX is the usage of client-side JavaScript code for maintaining non-blocking background communication with the web server. This is typically done using a specialized object called the XMLHttRequest. AJAX applications also tend to use JavaScript to dynamically change the contents or appearance of the page (a technique called dynamic HTML or DHTML). This allows web developers to create applications that can avoid a relatively slow page reload most of the time, making the application appear more responsive.

Going by the meaning of the AJAX acronym, you may be under the impression that AJAX applications are all about using asynchronous XML data exchange using the XMLHttpRequest, and thus the relevance or need for JSON might be lost on you. I would say that, yes, you're not wrong: AJAX is often implemented by using the XMLHttpRequest object to communicate with the server using XML messages. But for several reasons, JSON is gaining popularity as data exchange format instead of XML, and techniques like JSONP are used in addition to the XMLHttpRequest object. This technique actually has an advantage over using the XMLHttpRequest because it can be used to do requests to services that reside on another domain than the current web page, whereas this is not allowed with the XMLHttpRequest (at least, not by default and not without explicitly asking the user for confirmation). This makes JSONP a great tool for creating mash-up applications.

I'm sure you're not surprised to hear from me that the internet offers many places where you can get your XML vs JSON brawl on - I'm not particularly interested in that discussion, and I don't feel anybody has to choose sides. The point I want to bring across is that for AJAX applications, JSON is a respected data exchange format. It is excellently supported by both web browsers as well as popular AJAX frameworks, and companies like Amazon, YAHOO! and Google deliver more and more webservices that use JSON as data exchange format.

Next time

Now that you've seen how JSON works, and how it relates to AJAX web applications, you're ready to take a look at MQL queries. This is the topic of the next blog post.

Speaking at the MySQL conference 2011

I just received a confirmation that my presentation proposal for the MySQL user conference 2011 was accepted! The title for my proposal is MQL-to-SQL: a JSON-based Query Language for RDBMS Access from AJAX Applications, and it covers pretty much everything implied by the title.

As always, the Hyatt Regency Hotel in Santa Clara, California serves as the venue. The conference will be held from April 11-14. Except for the venue and period, I think this year's conference will bear few similarities to previous editions. Let me try and explain.

This year's theme is "MySQL, the ecosystem and Beyond". This means that the conference is using MySQL as an anchor for a myriad of topics which are of interest to a large majority of MySQL users. This explicitly leaves room for subjects that may not be directly related to the MySQL product proper.

So, not only products with a direct link to MySQL, such as drizzle and MariaDB are covered; NoSQL databases like CouchDB, MongoDB and Cassandra are quite well represented and the conference committee actively reached out to the PostgreSQL community to submit proposals. Traditional topics like scalability, performance and tuning remain strongly present, just like high availability, failover, and replication. As always, some of the world experts in this field will be speaking. In addition, infrastructural topics like virtualization and cloud computing are well represented (but of course, especially with regard to database management); One of the things I'm thrilled about is the presence of developer and applicattion centric topics like GIS, rapid application development, and object relational mapping.

Just take a look at the full schedule to get a taste of what this event will be offering. Personally, I think it's a great setup, and I'm happy and honored to attend!

In a series of upcoming blog posts, I plan to explain some of the subject matter regarding my own talk. But for now, I just want to tell you that I think this is going to be a great conference! I'm looking forward to attending a lot of high quality sessions, and meeting world leading experts in the MySQL and open source database ecosystem. I hope to see you there!

Substituting variables in Kettle Parameter values

Kettle (a.k.a. Pentaho Data Integration) jobs and transformations offers support for named parameters (as of version 3.2.0). Named parameters form a special class of ordinary kettle variables and are intended to clearly and explicitly define for which variables the caller should supply a value.

One of my pet projects, the pentaho auto-documentation solution kettle-cookbook, uses two named parameters called INPUT_DIR and OUTPUT_DIR. These allow you to specify the directory that contains the BI content that is to be documented (such as kettle transformation and job files, action sequence files and mondrian schema files), and the directory to store the generated documentation.

Several kettle-cookbook users ran into problems attempting to use variable references in the values they supplied for the INPUT_DIR and OUTPUT_DIR variables. In this case, the variables referenced in the supplied parameter values would be set by adding entries in kettle.properties file. I just committed revision 64 of kettle-cookbook which should fix this problem. In this article I briefly discuss the solution, as I think it may be useful to other kettle users.

Substituting Kettle Variable References

Kettle doesn't automatically substitute variable references in parameter values (nor in ordinary variable values). So, if you need to support variable references inside parameter values, you have to substitute the variables yourself.

Variable substitution in Kettle 4.01 and up

As of Kettle version 4.01, the Calculator step supports a calculation type called "variable substitution in string A" that is intended exactly for that purpose. I have tested this but unfortunately in 4.01 it doesn't seem to work, at least not for the built-in variable ${Internal.Transformation.Filename.Directory} which I used in my test. In the latest stable version, Kettle 4.10 it does work as advertised, I would recommend using this method if you're a user of Kettle 4.10 (or later).

Variable substitution in earlier Kettle versions

I have committed myself to making kettle-cookbook work on kettle 3.2.0, as my sources tell me that this is still an often-used version in many production environments. I'm even prepared to make kettle-cookbook work on Kettle versions earlier than 3.20, should there be sufficient demand for that. Anyway, the bottom line is, these versions do not support the "variable substitution in string A" calculation in the Calculator step, so you have to resort to a little trick.

A Kettle 3.2.0 transformation to substitute variables in parameters

For kettle-cookbook, I added a single transformation called substitute-variables-in-parameters.ktr as the first transformation of the main job.



The substitute-variables-in-parameters.ktr transformation uses a "Get Variables" step to read the values of the INPUT_DIR and OUTPUT_DIR parameters. The values are then processed by a javascript function which substitutes all variable references with their values. Finally, a "Set Variables" step overwrites the original value of the variables with their replaced value.

The code for the JavaScript step is shown below:

function replace_variables(text){
    var re = /\$\{([^\}]+)\}|%%([^%]+)%%/g,
        match, from = 0,
        variable_name, variable_value, 
        replaced_text = ""
    ;

    while ((match = re.exec(text)) !== null) {
        variable_name  = match[1] ? match[1] : match[2];
        variable_value = getVariable(variable_name, "");
        replaced_text += text.substring(from, match.index);
        replaced_text += variable_value;
        from = match.index + match[0].length;
    }
    replaced_text += text.substring(from, text.length);
    return replaced_text;
}

var replaced_input_dir = replace_variable(input_dir);
var replaced_output_dir = replace_variable(output_dir);

The script first defines function replace_variables(text) which accepts the parameter value, and returns the substituted value. Then it calls the function, applying it to the input_dir and output_dir fields from the incoming stream. These fields originate in the preceding "Get variables" step which assigns them the value of the INPUT_DIR and OUTPUT_DIR variables. The output of the replace_variables() function is assigned to the replaced_input_dir and replaced_output_dir javascript variables, which leave the JavaScript step as fields of the outgoing stream. In the final "Set variables" step, the replaced_input_dir and replaced_output_dir fields are used to overwrite the original value of the INPUT_DIR and OUTPUT_DIR values.

The replace_variables() function

Let's take a closer look at the replace_variables() function.

The heart of the function is formed by a while loop that executes a javascript regular expression called re that matches variable references.

The regular expression itself is defined in the top of the function:

    var re = /\$\{([^\}]+)\}|%%([^%]+)%%/g,

It's intention is to recognize variable references of the form ${NAME} and %%NAME%%. The part of the pattern for the name is enclosed in parenthesis to form a capturing group. As we shall see later on, this allows us to extract the actual name of the referenced variable. The trailing g indicates that the pattern should be matched anywhere in the string. This is necessary because we want to replace all variable references in the input text, not just the first one.

The regular expression object is used to drive the while loop by calling its exec() method. In case of a match, the exec() returns an array that describes the text matched by the regular expression. If there's no match, exec() returns null

    while ((match = re.exec(text)) !== null) {
       ...
    }

If there is a match, we first extract the variable name:

variable_name  = match[1] ? match[1] : match[2];

The first entry of the match array (at index 0) is the text that was matched by the pattern as a whole. The array contains subsequent elements for each capturing group in the regular expression. Because our regular expression re has 2 capturing groups, the match array contains two more elements. If the variable is of the form ${NAME}, the element at index=1 contains the variable name. If it's of the form %%NAME%%, it will be contained in the element at index=2.

Once we have the variable name, we can use the getVariable() javascript function to obtain its value:

        variable_value = getVariable(variable_name, "");

The getVariable() is not a standard javascript function, but supplied by the kettle javascript step.

To perform the actual substitution, we take the substring of the original text up to the location where the variable reference was matched. This location is conveniently supplied by the match array:

        replaced_text += text.substring(from, match.index);

Right after that location, we need to put the variable value instead of its name:

        replaced_text += variable_value;

The last action in the loop is to remember to location right behind the last replaced variable reference, so we can pick up at the right location in the original value the next time we match a variable:

        from = match.index + match[0].length;

Right after the loop, we need to copy the final piece of original text occurring right behind the last variable reference to yield the complete replaced text:

    replaced_text += text.substring(from, text.length);

Odds and Ends

While the substitute-variables-in-parameters.ktr transformation works great for its intended purpose, substituting variables in the known parameters INPUT_DIR and OUTPUT_DIR, it is not really applicable beyond kettle cookbook. What you'd really want to have is a job that replaces variables in all parameters, not just those that are known in advance.

As it turns out this is actually almost trivial to achieve, however to solution is a bit too long-winded for this post. If anyone is interested in such a solution, please post a comment and let me know, and I'd be happy to provide it.

UPDATE: A solution that substitutes all variable references occurring in the parameter values of the containing job is now available at the kettle exchange area in the Pentaho wiki.

Parameterizing SQL statements in the Kettle Table Input step: Variables vs Parameters

I get this question on a regular basis, so I figured I might as well blog it, in the hope it will be useful for others. Here goes:

Let's say that I want to delete all records that match an id from a set of tables. The table names come in as rows into the Execute SQL Script step (check execute for every row). Next I write:

DELETE FROM {table_name} WHERE id = {identifier}

as the SQL to execute. In the parameters grid at the bottom right, I have two fields: table_name and identifier. What is the syntax for substituting the table_name and identifier parameters in the sql script?

(Although this particular question focuses on the "Execute SQL Script" step, it also applies to the "Table Input" step, and probably a few more steps I can't recall right now.)

The parameters grid can be used for prepared statement value placeholders. In the SQL statement these placeholders are denoted as questionmarks (?). These are positional parameters: they get their value from those fields in the incoming stream that are entered in the parameters grid, in order.
Here's an example of the correct usage of these placeholders:

DELETE FROM myTable WHERE id = ?

Here, the ? in the WHERE clause will be bound to the value of the first field from the incoming stream entered in the parameters grid. Because there is only one such placeholder, there can be only one field in the parameters grid.

An important thing to realize is that these parameters can only be used to parameterize value expressions. So, this kind of parameter does not work for identifiers, nor do they work for structural elements of the SQL statement, such as keywords. So this kind of parameter cannot be used to parameterize the table name which seems to be the intention in the original example posed in the question.

There is a way to parameterize the structural elements of the SQL statement as well as the parameters. You can apply variable substitution to the SQL statetment.

Kettle Variables can be defined by a Set Variables step, or by specifying parameters at the transformation level. They get their value from "the environment": for example, parameters get their value initially when the transformation is started, and regular variables are typically set somewhere in the job that is calling your transformation.

In text fields, including the SQL textarea of the Table input step or the Execute SQL Script step, you denote those variables with this syntax: ${VARIABLE_NAME}. So to parameterize the table name we could use something like this:

DELETE FROM ${TABLE_NAME}

In order to force kettle to apply variable substitution to the SQL statement, you have to check the "variable substitution" checkbox. If this checkbox is checked, then all variables are simply substituted with their (string)value during transformation initialization. This is a lot like the way macro's are substituted by the pre-processor in C/C++ code.

When comparing variables with parameters, two important things should be mentioned here:

• Unlike value placeholders, variables can be used to manipulate any aspect of the SQL statement, not just value expressions. The variable value will simply become the text that makes up the SQL statement, it is your responsibility it results in a syntactically valid and correct SQL statement.


• Variables are evaluated once during transformation initalization. So if you want to vary the variable value, you'll have to call the transformation again for the change to take effect. For the same reasons, you cannot set the value of a variable and read it within the same transformation: setting the variable value occurs at runtime, but evaluating it occurs at initialization time.

Finally, here's a screenshot that summarizes these different ways to parameterize SQL statements in kettle:

If you want to read more about this topic, it's covered in both our books Pentaho Solutions and Pentaho Kettle Solutions. Another title you might be interested in is Maria Roldan's Pentaho 3.2 Data Integration: Beginner's Guide.

Back to blogging....

It has been a while since I posted on my blog - in fact, I believe this is the first time ever that more than one month passed between posts since I started blogging. There are a couple of reasons for the lag:

• Matt Casters, Jos van Dongen and me have spent a lot of time finalizing our forthcoming book, Pentaho Kettle Solutions (Wiley, ISBN: 978-0-470-63517-9). The book is currently being produced, and should be available according to schedule in early September 2010. If you're interested, you might like to read one of my earlier posts that explains the organization and outline of the book.
  
  (I should point out that we have reorganized the outline as the project progressed, so the final result will not have all the chapters mentioned in that post. We do however cover most of the topics mentioned.)


• I have been checking out Quipu, a promising Open Source data warehouse management solution. Quipu provides a repository-based extensible code-generator that allows you to generate and maintain a data warehouse based on the Data Vault model. One of the things I want to do in the short term is write templates that allows it to work for MySQL, and after that, I want to see if I can get Quipu to generate Kettle Jobs and transformations.


• I have been working on a couple of software projects. Two of them are currently available as open source on google code:

  
  

  mql-to-sql

  This allows you to use the Metaweb Query Language (MQL) to query a RDBMS. If you're wondering what this is all about: MQL is the query language used by Freebase (which is a collaborative "database-of-everything" or "wikipedia-gone-database").
  
  While Freebase is interesting in its own right, I am particularly enthused about the MQL query language. I feel that MQL is an exceptionally good solution for flexible, expressive and secure data access for modern (AJAX) Web applications. Even though MQL was not developed with relational database systems in mind, I think it is a pretty good fit.
  
  Anyway, this is very much a work in progress, and I appreciate your feedback on it. If you're interested, you can read a bit more about my take on RDBMS data access for web applications and MQL on the mql-to-sql project home page. I have also put up an online demo that allows you to query the sakila MySQL sample database using MQL.

  
  

  kettle-cookbook

  This is a project that provides auto-documentation for Kettle (a.k.a. Pentaho Data Integration).
  
  The project consists of a bunch of Kettle Jobs and transformations (as well as some XSLT stylesheets) that extract data from Kettle Jobs and transformation and transform it into a collection of human-readable HTML documents along with a table of contents. The resulting documentation looks and feels a bit like JavaDoc documentation.
  
  If all goes well, I will be presenting kettle-cookbook in a Pentaho web seminar, which is currently scheduled for September 15, 2010

  
  



• I've been enjoying 4 weeks of vacation (I started working this week). There's not much to tell about that, other than that it was great spending a lot of time with my family. I plan to do this more often, and now that Kettle Solutions is finished I should be able to find more time to do it.


• I've been looking at two emerging HTML5 APIs for client-side structured storage, the Web SQL Database API and the Indexed DB API.
  
  I have developed a few thoughts about the ongoing debate (see this and article for some background) about which one is better and I see a role for something like MQL here too. I will probably write something about this in the next few weeks


• Yesterday, I got wind of the JS1k contest! Basically, the challenge is to write an interesting standalone JavaScript demo program that must be no larger than 1024 bytes. It is amazing and inspiring to see what people manage to do with a modern browser in 1k of JavaScript code.
  
  I decided to try it myself, and you can find my submission here: An interactive SQL query tool for the Web SQL DB API.
  
  Essentially, you get a textarea where you can enter arbitrary SQLite queries, and button to execute the SQL, and a result area that will print a feedback message and a result table (if applicable). As a bonus, there's a button to get a listing of the available database objects (using a query on sqlite_master) and an explain button to show the query plan of the current SQL statement.
  
  The demo works on recent versions of Google Chrome, Apple Safari and Opera. It can run offline, and does not require any plugins. I should say that I expect my submission will be rejected by the judges since the demo is not functional on Mozilla Firefox, which is a requirement. (That is, the script will detect that the Web SQL Database API is not supported and print a message to that effect). However, it was still fun to try my hand at it.
  

Ok - that's it for now. I will try and post more regularly and write about these and other things in the near future. Don't hesitate to leave a comment if you have any questions or suggestions.

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.