Re-ordering AND clauses

[Julian Hyde <jh...@apache.org>]

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

Re: Re-ordering AND clauses

[Jinfeng Ni <jn...@apache.org>]

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 <jh...@apache.org> 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
>
>