sqllogictest

Differences Between Engines
Login

Differences Between Engines

Help

The following differences have been found by the sqllogictest utility.

SQLite supports up to 64-way joins. Such a "deep" join has caused problems with many of the DB engines during testing, including MySQL and PostgreSQL. MSSQL was able to handle this case, but the test case caused the "tempdb" storage area to increase to over 3gb.

Despite it being supported by virtually all DB engines, including SQLite, there are still some that do not support this syntax, most notably MSSQL.

All historical versions of SQLite have given INTERSECT, EXCEPT, and UNION operators in a compound query equal precedence - they group from left to right. Oracle does the same thing. But the SQL92 standard says that INTERSECT should have higher precedence than EXCEPT and UNION. All database engines other than Oracle and SQLite appear to work according to the standard. And Oracle says that they will change to conform in future releases. So SQLite should probably change too.

Turns out MySQL doesn't support EXCEPT or INTERSECT at all. Perhaps the reason nobody has yet complained about me getting the precedence of INTERSECT wrong in SQLite is that nobody has noticed because nobody ever uses INTERSECT or EXCEPT...

This is a bug in SQLite and will be fixed. Ticket #3515.

SELECT col0 FROM ( tab0 )

SQLite currently does not support this fully, although check-in [5973] adds partial support.

SELECT col0 / 0 FROM tab0

SQLite does not raise arithmetic exceptions (eg. divide by zero, 1/0). SQLite returns a NULL value for 1/0.

MSSQL aborts the query and returns an error. The SQL92 standard implies this is correct. 

ISO/IEC 9075:1992, Database Language SQL- July 30, 1992:
6.12 <numeric value expression>


If the value of a divisor is zero, then an exception
            condition is raised: data exception-division by zero.

CREATE TABLE tab0(col0 INTEGER)
INSERT INTO tab0 VALUES(25)
SELECT col0 * + 33 / col0 FROM tab0

MSSQL returns 25. It's probably interpreting it as col0 * (+33/col0)
MySQL returns 33.
SQLite returns 33. Probably (col0 * +33)/col0.
PostgreSQL gives 33.

ISO/IEC 9075:1992, Database Language SQL- July 30, 1992:


Subclause 3.3.4.4, "Rule evaluation order": It is implementation-
            dependent whether expressions are actually evaluated left-to-
            right when the precedence is not otherwise determined by the
            Formats or by parentheses.

SELECT ALL * FROM tab0 NATURAL INNER JOIN tab1 ON NULL BETWEEN NULL AND NULL

Not sure if this is valid syntax, but the SQL92 BNF grammar seems to suggest that it is.

MSSQL accepts this as valid SQL, but returns no rows. SQLite, MySQL, and PostgreSQL all report errors.

In SQLite, there might be semantic constraints affecting this. SQLite accesses an ON clause on a NATURAL JOIN syntactically, but prunes it at the semantic level.

SELECT CAST ( col1 as TEXT )

SQLite: SQLite is very flexible in the types of cast it will allow. For more information, see http://www.sqlite.org/datatype3.html.

MSSQL: Without pre-defining custom user types, MSSQL will only allow you to cast a value to a system defined type (http://msdn.microsoft.com/en-us/library/aa258271(SQL.80).aspx). Additionally, certain casts are not allowed, for example expressions of type 'int' to 'text'. For a complete list of what is allowed, see http://msdn.microsoft.com/en-us/library/aa226054(SQL.80).aspx.

MySQL: MySQL will only allow you to cast a value to a system defined type (http://dev.mysql.com/doc/refman/5.1/en/cast-functions.html).

It should also be noted that MySQL does not allow any whitespace between 'CAST' and '('.

The SQL92 standard leaves it as "implementation-defined rounding or truncating".

SELECT CAST(-75/78 as INTEGER)

MSSQL and SQLite use "truncation". The above code returns 0 in both MSSQL and SQLite.

MySQL uses rounding. The above code returns -1 in MySQL.

Even though an "explicit" cast was used above, this is true for "implicit" as well. 

ISO/IEC 9075:1992, Database Language SQL- July 30, 1992:


 4.6  Type conversions and mixing of data types


Values of the data types NUMERIC, DECIMAL, INTEGER, SMALLINT,
         FLOAT, REAL, and DOUBLE PRECISION are numbers and are all mutually
         comparable and mutually assignable. If an assignment would result
         in a loss of the most significant digits, an exception condition
         is raised. If least significant digits are lost, implementation-
         defined rounding or truncating occurs with no exception condition
         being raised. The rules for arithmetic are generally governed by
         Subclause 6.12, "<numeric value expression>".

SELECT col1 AS x FROM tab1
SELECT col1 x FROM tab1

The 'AS' keyword is optional in the SQL92 standard, so the two selects above should be equivalent. PostgreSQL requires the AS keyword, and will error on the second form.

SELECT 1 / 2

MySQL by default does floating point division, even if both operators are of type INTEGER, so the above would return 0.5 in MySQL. All of the other database engines tested do integer division, and return an integer result.

The equivalent functionality in MySQL is achieved with the DIV operator. http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html#operator_divide

SELECT 1 DIV 2

SQLite allows the following non-standard form of a GROUP BY query:

SELECT * FROM tab1 GROUP BY col1

The above query generates the following error in MSSQL: 

Msg 8120, Level 16, State 1, Line 1
Column 'tab1.col0' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

For MSSQL, when using a GROUP BY clause every field in the SELECT list must be either:
One of the GROUP BY terms - in this case region
An aggregate function - for example SUM or COUNT
An expression based on the above

You would have to rewrite the above query as:

SELECT col1 FROM tab1 GROUP BY col1

RIGHT and FULL OUTER joins are not supported by SQLite.

RIGHT and FULL OUTER joins are not supported by SQLite.

SELECT COALESCE(1)

MySQL accepts the above. SQLite and MSSQL do not. The SQL92 standard implies that COALESCE should take at least 2 parameters so the above should probably fail on MySQL.