The Structured Query Language (SQL) has been an international standard for more than three decades, and it is now supported in all major RDBMSs. While it is well known that database vendors typically implement custom extensions of SQL that are not part of the ISO Standard, it is reasonable to expect statements that use only “standard” features of the language to behave the same regardless of which system they are executed on; after all, this is the whole point of having a standard. However, the reality is that even simple statements may compile in some systems and not in others, or produce different results. To illustrate how puzzling and counterintuitive things may get, let us consider a base table R with one column named A, and the query In all of the most popular RDBMSs on the market today - Oracle, SQL Server, DB2, MySQL and PostgreSQL - Q will give the same answer. But now consider where the expression for Q is plugged in as a subquery. All of the above systems, except PostgreSQL, will report a compilation error, even though Q0 simply asks to print the result of the query Q that, on its own, runs without problems. PostgreSQL, on the other hand, compiles Q0 and outputs the same answer produced by Q. Then it gets even more interesting. Consider Q00 = SELECT * FROM R WHERE EXISTS ( Q0 )
where we plug in the expression for Q0 that did not compile on all systems except PostgreSQL. Now this query suddenly works, even on those systems that did not compile Q0, with the sole exception of MySQL that keeps reporting error.
Understanding what is going on here is not straightforward. Part of the issue is due to the fact that the only official specification of SQL is the ISO Standard, which is a massive document entirely written in natural language. Aside from it, there are no other tools, such as a reference implementation or a standardized set of test cases, to assess the level of compliance of an RDBMS w.r.t. the Standard.
In this short paper, we describe some of the discrepancies that exist between
different SQL implementations, and we discuss our recent and ongoing efforts [
The simplest discrepancies between SQL implementations are due to operators or constructs missing from the language, or having different syntactic forms than in the Standard. While still important for portability, such kind of differences are typically well documented and relatively easy to deal with.
More problematic are the cases in which a feature is supported by a system, but it does not behave as expected. Examples are integer division in MySQL and Oracle, which produces a decimal number in place of an integer, and division by zero in MySQL, which returns NULL instead of throwing a runtime error. Sometimes, this can be partly mitigated by modifying the system’s configuration; for instance, MySQL has a “strict mode” that forces a runtime error for division by zero in data-modification statements but has no effect in queries, and an “ANSI mode” that restores some sanity by enforcing standard quotation marks, pipes for string concatenation, and fully-specified GROUP BY clauses. On the other hand, this also means that the behavior w.r.t. the Standard depends on the particular mode the system runs in, which further complicates things.
Some behaviors are deeply built into individual systems as a result of poor or fundamentally flawed design choices, which cannot be changed via configuration parameters. One instance of this is Oracle’s astonishing choice of implementing NULL as the empty string. For example, in Oracle, the query
SELECT * FROM R WHERE 'a' <> '' produces an empty table even if R is non-empty, because '' is NULL and every comparison involving NULL evaluates to the truth value unknown, so no rows are returned. In a world where text processing is very common and SQL statements are often machine-generated, it is not hard to imagine scenarios where this could have devastating effects: e.g., a large, complex transaction may be aborted simply because it attempted to insert the result of a string operation – which happened to produce the empty string – into a column declared as NOT NULL.
A similar shortsighted implementation choice can be found in MySQL, which construes TRUE and FALSE as the integers 1 and 0, respectively. But the world’s most popular open source database truly surpasses itself with its type checking system and implicit casting rules, which result in meaningless queries like
SELECT * FROM R WHERE TRUE + 4 - '5' to successfully compile and execute: TRUE is 1, the string '5' is implicitly converted to the integer 5, hence the WHERE condition above evaluates to 0, which is FALSE, and so no rows are returned.
There are then cases when RDBMSs deviate from the Standard for sensible reasons. To illustrate this point, let us go back to the queries Q0 and Q00 given in the introduction. According to the Standard, the behavior of SELECT * depends on the context in which it occurs: under EXISTS, the star amounts to having an arbitrary constant in its place, while everywhere else it is the same as explicitly listing all attributes. This means that the semantics of SQL is non-compositional (the same query can behave differently in different contexts) and it is why DB2, Oracle and SQL Server – which follow the Standard in this respect – report error for Q0 but not for Q00. Indeed, replacing * in Q0 with an explicit list of attributes results in a reference to A that is ambiguous, because there are two columns with that name in R1. This does not happen in Q00, where * is replaced by a constant.
MySQL and PostgreSQL take different routes that deviate from the Standard but restore compositionality: the former always turns the star into an explicit list of attributes, even under EXISTS; the latter interprets it as “return all columns without referencing them by name” (which in our opinion is the most reasonable choice). This is why MySQL reports error for both Q0 and Q00, while PostgreSQL executes them successfully. 3
The SQL Standard is written in natural language, which is inherently ambiguous, and to think that it may be the source of misunderstandings among developers, implementers, as well as database researchers, is not implausible. Moreover, from a practical point of view, a natural language specification is harder to implement and maintain, and it does not lend itself to formal reasoning, which is necessary to derive language equivalences and optimization rules.
The need for a formal semantics of SQL is witnessed by the fact that several
attempts at providing one have been made in the past (see [
In SQL, queries Q and conditions are defined by mutual recursion: queries have conditions in the WHERE clause, and a condition may involve a query within EXISTS or IN. The semantic function J K we define takes different inputs depending on the syntactic construct under consideration: for conditions, the inputs are a database D and an environment that binds attribute references to actual values; for queries we additionally need a Boolean flag x that indicates whether we are within an EXISTS predicate. Then, the behavior of * in SELECT is given by: 8><JSELECT c AS N FROM
KD; ;x u SELECT * } v FROM
~ = if x = 1 if x = 0 D; ;x:>JSELECT `( ) FROM
KD; ;x
where c is an arbitrary constant and N is an arbitrary name, while ` is a labeling
function producing the list of attributes in (the Cartesian product of) the tables
in the FROM clause. The Boolean flag x is set to 1 in EXISTS conditions as follows:
JEXISTS QKD; =
(
t if JQKD; ;1 6= ?
f if JQKD; ;1 = ?
and it is reset to 0 in other rules of the semantics (see [
Our formal semantics can also be easily adapted from one system to another. For example, in PostgreSQL and MySQL, the semantic function for queries will be defined only w.r.t. a database D and an environment , while the flag x is no longer needed, because in these two systems the behavior of * is independent of the context. Then, the semantics of SELECT * queries in MySQL is given by
KD; = JSELECT `( ) FROM while for PostgreSQL we have
KD; = JFROM
Observe that the absence of SELECT in the r.h.s. of the above equation is not a
typo; refer to [
The semantics was experimentally validated with PostgreSQL and Oracle on a large number of randomly generated queries. Its immediate applications were theoretical: providing the first formal proof that basic SQL queries and relational algebra have the same expressive power, and showing that – contrary to common belief – three-valued logic is not really necessary for handling nulls.
KD; KD; 4
The goal of the SQL Standard is to guarantee interoperability between RDBMSs, but the discrepancies existing in current implementations are significant enough to severely affect the portability of SQL code. The problems we discussed here are just the tip of the iceberg, as we have not even touched on grouping, aggregation, constraints, transactions, data definition statements, recursive queries, common table expressions, updates, and many other features of the language.
Our ambitious goal is to make the Standard more clear, formal and accessible,
in the interest of researchers and practitioners alike. The semantics we presented
in [
This work has already yielded very positive outcomes. First, it has led to our
cooperation with Neo4j, a world-leading company in field of graph databases, to
formalize the semantics of the graph query language Cypher [