D 2008-12-06T17:42:00 L Differences\sBetween\sEngines P 5ef90625334e1bccf8513e56a445fa25d8428396 U shane W 5934 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 [http://www.sqlite.org/cvstrac/tktview?tn=3515 | #3515]. * ( ) on FROM clauses SELECT col0 FROM ( tab0 )
 SQLite currently does not support this fully, although check-in [http://www.sqlite.org/cvstrac/chngview?cn=5973 | [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 

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, "".
* 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 Z 44b55f78226a70457b84ae7d54d0aa8d