magarity kerns (12/30/2010)
Excellent article - For more fun, check other DBMSes. I checked on Oracle and "select 'A' from dual where 1=0 or 1/0 = 1;" gives a division by zero error. (although it may need to be in a procedure on Oracle to do it with the IF statement). Anyone have DB/2 or Teradata handy?
This is the output of my tests on other RDBMSs. I tested Oracle 11gR2, DB2/400 V5R4M0, Firebird 1.5 and PostgreSQL 8.3. I couldn't find a MySQL instance, I was sure we had one, but I couldn't find it.
I executed the following queries to discover the behaviour of the DB engine:
-- Does the engine short-circuit?
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1 OR 1 = 1/0;
-- Does the engine detect contraddictions?
SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE 1 = 1/0 AND 1 = 0;
I didn't have the time to test other particular things, but this is a good starting point.
Here's the results:
DB2 Oracle Firebird PostgreSQL
------------------------ ---- ------ -------- ----------
Short-circuit no yes yes yes
Contraddiction detection no yes yes no
Just a few words on Oracle's contraddiction detection: under some circumstances, the contraddiction is not detected at all. This doesn't surprise me, as Oracle's optimizer is full loaded of bugs (more features, more things that could go wrong). In particular, when the statement text is VERY big and contains a whole lot of literals, the optimizer goes quirks and tries to produce a plan (tries for several minutes...) even if the statement contains a contraddiction. This could mean that the contraddiction detection is applied as one of the last rules, that could make sense since it also checks for constraints contraddiction.