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 <[email protected]> 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 > >
