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
