Short Circuit Evaluation + Runtime errors.

Hi All,

Judging from a bit of searching, some databases seem to do short circuit evaluation of predicates (which has been discussed here before)

SQL92 doesn't say either way whether this is valid - my reading of section 8.12 of the standard implies that it's not (I would have expected a X in the truth table for true OR), but I guess you can read it either way. (it certainly allows for re-ordering of predicates, which I'm not arguing).

But if re-ordering + short circuiting is valid, then how are run-time errors coped with?

select * From a where 1=0 and a1 < (select b1 from b)

(b has 2 rows -> scalar subquery cardinality error).

Section 7.11 of SQL92 mandates the cardinality error.

SQL server seems to ignore the run-time error, and return 0 rows, but I can't see this as being correct! Surely an optimisation, in order to be correct, should not allow the result (or lack of) of a problem set to be changed?!

Cheers,

Lee Benfield Received on Wed Feb 19 2003 - 05:45:31 CST