Having it at the operator table level would make it easier to match each SQL dialect's behavior, as that is currently how operators are selected for different dialects. The API between operator tables and the SqlValidator/SqlToRel supports selecting different operators based on types. Though, the API extending operator tables might not.
On Fri, Oct 4, 2024 at 3:51 PM Stamatis Zampetakis <zabe...@gmail.com> wrote: > For the record here is what the SQL Standard says regarding operations > on numbers: > > "Operations on numbers are performed according to the normal rules of > arithmetic, within implementation-defined limits, except as provided > for in Subclause 6.27, “<numeric value expression>”." > > Section 6.27 defines some errors with respect to division but not much > more than that. > > Best, > Stamatis > > On Fri, Oct 4, 2024 at 10:01 PM Mihai Budiu <mbu...@gmail.com> wrote: > > > > * > > I think "safe" means "no exception", so in case of overflow safe > operators return NULL. But here we are talking about real exceptions. (At > least the existing "safe" operations in Calcite work this way). > > * > > Having an operator table that uses a different implementation for > arithmetic sounds to me like a recipe for confusion. Moreover, this would > not allow mixing checked and non-checked arithmetic in the same program > (not sure whether there are use cases for this, though) > > * > > The visitor proposal would use the same operator table, but switch > operations to checked/unchecked depending on the type system. Moreover, > using this visitor is optional. > > * > > For aggregation we already have two kinds of issues: > > * > > the result type of aggregation is unspecified by the standard; Calcite > uses the same type as the input data type > https://issues.apache.org/jira/browse/CALCITE-6324, and > > * > > The type of intermediate results in aggregates is unspecified > https://issues.apache.org/jira/browse/CALCITE-6516, > https://issues.apache.org/jira/browse/CALCITE-6427 > > * > > Moreover, I think the standard says that the order of aggregation is > unspecified; this already makes a difference in the absence of overflow for > floating point, where addition is not associative > > > > I think the aggregates can be solved in general by allowing them to > compute on a wider data type and then casting the final result. This would > work even if using checked arithmetic. Maybe the problem is that Calcite > already made a choice for both these types (intermediate results, and final > results), and it's not the same as other databases are making. But that's a > separate problem. > > ________________________________ > > From: James Starr <jamesst...@gmail.com> > > Sent: Friday, October 4, 2024 12:09 PM > > To: dev@calcite.apache.org <dev@calcite.apache.org> > > Subject: Re: Checked arithmetic > > > > I am reasonably sure checked arithmetic is left to implementation to > define > > due to the mixed concerns of different DBs. If you define checked > > arithmetic, it limits certain optimizations that may be made, such as > > pushing an agg below a join. For instance, if a rewrite because a > rewrite > > could change the order of operations such that an overflow does or does > not > > happen, what is the correct answer in this case? The error, the garbage > > result from an overflow, or the result that did not hit the overflow? > > > > I believe some systems have CURRENCY and BIG DECIMAL types, and this > might > > be the reason. Maybe the CURRENCY types disallow rewrites to at least > make > > the query repeatable and consistent. > > > > An alternative to a visitor, would be to use a different operator table > > with safe operators. > > > > Considering Calcite as planner, I think the operator/type needs to expose > > if over-follow is defined or not, as opposed to checked or unchecked > which > > is an implementation issue. > > > > James >
