You should definitely log a bug, even if there are no plans to fix it. It is a violation of the standard, and therefore it is a bug, and therefore we should document that it exists.
Can you do some research on the right terminology. You use the term ‘unsafe’. I think the PL community uses the term ’strict’ for expressions that must be evaluated (and therefore if they throw, the query is sure to abort). We have other related concepts floating around in Calcite - dynamic functions, deterministic operators - and standardizing terminology is key if we are to keep the menagerie in order. There might be a way to have our cake and eat it too. We could push down the division, and if there is a division-by-zero we return a special value such as NULL. If the special value is not eliminated by the join then it is promoted to a throw. The “cake” here is the performance benefit of pushing down a filter (that may never or rarely throw); the “eating it” is safety on the occasion that the filter does throw. Even if that strategy doesn’t work, maybe we could have a flag that says whether to push down conditions that might throw. Julian > On Feb 17, 2022, at 8:07 AM, Scott Reynolds <sdrreyno...@gmail.com> wrote: > > 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 >>
