osdir.com


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: Re-ordering AND clauses


SQL standard seems to allow the re-ordering of AND clauses; basically, it's
"implementation-dependent".

In Drill, the run-time will re-order the branches, regardless whether
planner does the re-order or not. It's mainly for performance benefit;  "
exp1 AND expr2" would be reordered into "expr2 AND expr1", if expr1 is more
expensive to evaluate (i.e, containing some functions which is expensive to
evaluate).  Certainly such re-order would lead to different behavior for
some cases.


1.
https://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated



On Wed, Aug 8, 2018 at 2:07 PM, Julian Hyde <jhyde@xxxxxxxxxx> wrote:

> We have never really made our policy clear about whether it is valid for
> the planner to re-order the clause of an AND. I would like to have a
> discussion about that policy (also see https://issues.apache.org/
> jira/browse/CALCITE-2450 <https://issues.apache.org/
> jira/browse/CALCITE-2450>).  I propose that we can re-order the clauses
> of AND (and OR) but not CASE.
>
> Consider the query Q1:
>
>   SELECT *
>   FROM t
>   WHERE x > 0 AND y / x < 5
>
> And the similar query Q2 that re-orders the AND clause:
>
>   SELECT *
>   FROM t
>   WHERE y / x < 5 AND x > 0
>
> If one of the rows has a x = 0, we would expect Q2 to throw a
> divide-by-zero error. Is it allowed for Q1 to throw? Is it allowed for it
> NOT to throw?
>
> We recognized that sometimes people want to write SQL to guard against bad
> values (like x = 0 above), and so we tacitly assumed that we would not
> re-order AND. Thus in current Calcite, Q1 would never throw, and Q2 would
> always throw.
>
> I think that was a mistake. It ties our hands too much (we are not able to
> move highly selective predicates to the front of the list, for instance)
> and it is inconsistent with SQL semantics.
>
> There is a way to achieve the “guarding” behavior: use CASE (whose clauses
> cannot be re-ordered), in Q3 as follows:
>
>   SELECT *
>   FROM t
>   WHERE CASE WHEN x > 0 THEN y / x < 5 ELSE FALSE END
>
> Julian
>
>