Subject | RE: [Firebird-Architect] ORs in Where statement |
---|---|
Author | Claudio Valderrama C. |
Post date | 2003-07-29T11:46:33Z |
David Schnepper wrote:
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.
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 think it's a bit of tradeoff to decide whether those cases are better
being reported always at the expense of decreasing performance.
C.
>I think it's vendor specific.
> 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 securityI disagree. By reading the SQL spec, I understood that all paths of
> 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.
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 forThat was verified at compile time.
> 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 handleWe check that access requests issued by the stored procedure at compile
> 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?
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 otherThese are side effects and the SQL spec doesn't require full evaluation, so
> 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!
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.