Is it feasible to prevent the filter push down in cases like this ( detect potential division by zero) ? What other exceptions that could happen?
Should it only push down some filters and preserve the complex filter above the join? Regarding a Jira ticket, generating an exception when it should produce valid results is gotta be a Jira case IMHO. On Thu, Feb 17, 2022, 7:17 AM Stamatis Zampetakis <zabe...@gmail.com> wrote: > Hi all, > > Till today, I believed that pushing a filter below an inner join is not > strictly related to the operators (equals, plus, minus, cast, division) > present in the filter. > > However, the query below will return some results if the filter is executed > after the join or it will fail with an exception if the filter is pushed > below the join. > > EMP [EMPNO, NAME, DEPTNO] > 0 | Alex | 0 > 10 | Bob | 1 > > DEP [DEPTNO] > 1 > > SELECT e.name > FROM emp e > INNER JOIN dep d ON e.deptno = d.deptno > WHERE (10 / e.empno) = 1 > > It seems that SQL standard (Section 7.4 general rule 1) mandates that WHERE > should be applied to the result of FROM so in the case above pushing > filters below the join seems to violate the standard. > > Currently, in Calcite we are going to push the division, cast, and > basically any kind of operator below the join. Many well-known databases > e.g., Postgres, do that as well. > > Should we log it as a bug and try to do something about it or let it be as > it is? > > Best, > Stamatis >
