Re: Short Circuit Evaluation + Runtime errors.

Lee,

  While the wording suggests it, I don't think SQL-92 mandates an error for a subquery that does not need to be evaluated. Elsewhere in the standard is the following statement:

If the result of an expression or search condition can be determined  without completely evaluating all parts of the expression or search condition, then the parts of the expression or search condition  whose evaluation is not necessary are called the inessential parts. If the Access Rules pertaining to inessential parts are not satisfied, then the syntax error or access rule violation exception condition is raised regardless of whether or not the inessential parts are actually evaluated. If evaluation of the inessential parts would cause an exception condition to be raised, then it is implementation-dependent whether or not that exception condition is raised.

The exception in your example results only results if an "inessential part" is evaluated, and the last sentence here indicates that the exception condition may or may not be raised, according to the implementation. In other words, and consistent with the degree to which SQL statements simply describe results without mandating procedure, the standard is silent about whether inessential parts are evaluated, and whether they are or not, whether exceptions are raised if those parts would generate an exception condition if evaluated.

Steve Kass
Drew University

leeSILLYBIT_at_benf.org wrote:

>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 - 15:37:41 CET