> -----Original Message-----
> From: Ann W. Harrison [mailto:aharrison@...]
> Sent: Friday, June 13, 2003 7:15 AM
> To: Firebird-Architect@yahoogroups.com
> Subject: Re: [Firebird-Architect] ORs in Where statement
>
>
> A==
> > > > Chris Meelhuysen wrote:
> > > > >Is Firebird constructed in such a way that when one OR
> condition is true
> > > > >that it doesn't calculate the rest since we already know
> it can be used?
> > > >
> >Ann wrote:
> > >
> > >In the interim, it occurred to someone
> > >that the evaluation of a boolean might have side-effects that
> > >altered the state of the database, so full evaluation was done
> > >even after the result was determined.
> Helen Borrie wrote:
>
> >I have to confess that I've read and re-read the note in firebird.conf,
> >cudgelled my tiny brain, and can't come up with any scenario where
> >subsequent OR conditions could override a preceding True...somebody must
> >have thought of one, I suppose.
>
> Sorry, I was unclear. The concern was not that subsequent expressions
> could change the result of the boolean operation, but that they could
> have side-effects in the database. For example, part of the boolean
> could invoke a stored procedure and that procedure might do some logging
> or other operations. There would be no change in the current statement,
> but subsequent statements could get different results depending on what
> parts of the boolean expression were evaluated.
>
> Jim will probably have something to say about that kind of programming.
>
> Regards,
>
>
> Ann
>

This thread of discussion triggered something in my somewhat addled
brain cells.

I was part of the Borland-Interbase team when the full-evaluation of
booleans
was put in - and it was in response to becoming more SQL compatible.
(This was perhaps about 1997/1998).

There's been mention of the side effect of UDF or Stored procedures
already (which I agree is very poor practice), but here's another
case when it matters.

CREATE TABLE foo (a integer, b integer);
INSERT INTO foo(a,b) VALUES (1,0);
GRANT SELECT ON foo(a) TO PUBLIC;
--- Login as another user.
SELECT "oops" FROM foo WHERE a=1 OR b=0;

By SQL, the SELECT statement must return the same result,
regardless of whether "a=1" or "b=0" is evaluated first.
The order of evaluation is specified as indeterminate
by SQL (I'm not even sure if SQL allows the order of evaluation
to be vendor-determined).

If "b=0" is evaluated first, the result is a security
exception (no SELECT rights on foo(b)).
If "a=1" is evaluated first, with early exit,
the result is the row (1,0).
If the expression is fully evaluated the result is
security exception.

This violates the requirement that the result of the
statement does not depend on the order of evaluation.

The same issue applies for
WHERE a=0 AND b=1;
The AND must be fully evaluated to ensure any
exceptions due to access of b will be reported.

Now, of course, there are other ways to handle
security exceptions (check all ACL before any
evaluation of any part of the statement) -
but what if the access were conditional within
a stored procedure?

I also have a vague memory regarding other
exceptions that could occur during boolean
evaluation (concat two strings to make
a string longer than the max string length,
integer over/underflow, NaN), oh, and the classic:

SELECT * FROM FOO WHERE a=0 OR 1/a < 1;

For any row where a==0 this statement should
return a DIV#0 exception!

Dave Schnepper