sqllogictest

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

The following differences have been found by the sqllogictest utility.

• "Deep" Joins

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.

• OFFSET / LIMIT clauses

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

• INTERSECT precedence

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.

• ( ) on FROM clauses

SELECT col0 FROM ( tab0 )


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

• arithmetic exceptions

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.

• integer division or perhaps operator precedence issue

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.

• ON clause on NATURAL JOIN

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.

• supported types for CAST ( x as type )

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).

• method of conversion for CAST

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>".

• renaming columns

SELECT col1 AS x FROM tab1 SELECT col1 x FROM tab1

The 'AS' keyword is optional in the SQL92 standard. PostgreSQL requires it.

• integer division

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 database engines do integer division, and return an integer result.

The equivalent functionality in MySQL is achieved with the DIV operator.

SELECT 1 DIV 2