David Schnepper wrote:

>
> 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).

I think it's vendor specific.

> 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.

I disagree. By reading the SQL spec, I understood that all paths of
execution must be verified at compile time, call it PREPARE_STATEMENT phase
if you like. Verification includes rights over SQL objects and since IB/FB
go to check in rdb$security_classes when statements are prepared, that's not
an actual issue.

> 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.

That was verified at compile time.

> 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?

We check that access requests issued by the stored procedure at compile
time, as demanded by the standard, so no problem. If an access path that
won't execute is forbidden by SQL rights, the statement won't be compiled
for the requesting user.

> 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!

These are side effects and the SQL spec doesn't require full evaluation, so
I think it's a bit of tradeoff to decide whether those cases are better
being reported always at the expense of decreasing performance.

C.