Just because you can use 1000 digits of precision does not mean you should declare values with type DECIMAL(1000, 0). This means: "I expect this number to have 1000 digits before the decimal point".
When you compare such a number with a value DECIMAL(5, 1) the compiler assumes the worst case: there are 1000 decimal digits before the decimal point. And only 1000 digits can be represented in total by the runtime representation. So the only choice is to convert DECIMAL(5, 1) to DECIMAL(1000, 0), losing the digits after the decimal point. However, if you declare your first value DECIMAL(100, 0), then comparison will work by casting both to DECIMAL(100, 1), with no information loss. The summary is: only declare values as precise as really needed. Mihai ________________________________ From: Sylvain Gaultier Sent: Thursday, April 10, 2025 12:23 AM To: [email protected] Subject: Re: Behavior on leastRestrictive RelDataType Thank you for your answers. If I understand correctly: In 1.39 RelDataTypeSystem, maxPrecision is 19. (This had been increased to 1000 for internal needs but that does not change the behavior here) I tested using the default Calcite values So, if we have a comparison like: "c.c_acctbal >= 1002.2", With arg "c_acctbal" , a decimal type, by default we will assign it the maximum possible precision -> DECIMAL(19,0) We will have DECIMAL(19,0) vs. DECIMAL(5,1) And Calcite (in 1.39) keeps DECIMAL(19,0) to cast the numeric part. So we have `C`.`c_acctbal` >= CAST(1002.2 AS DECIMAL(19, 0)) I find it a bit surprising to truncate the numeric part. It means that potentially values like 1002.1 will work. On Wed, Apr 9, 2025 at 7:15 PM Mihai Budiu <[email protected]> wrote: > The default type system RelDataTypeSystemImpl does not even support 1000 > digits of precision. > > In general, if a result needs more digits than available in the type > system, the type inference will prioritize using a higher precision over a > higher scale, since scale specifies "digits after period", which can be > rounded with a smaller relative error. > > Mihai > ________________________________ > From: Sylvain Gaultier > Sent: Wednesday, April 9, 2025 9:33 AM > To: [email protected] > Subject: Re: Behavior on leastRestrictive RelDataType > > Sorry, I'm new to Calcite, I'm not very familiar with it. > Where can I find this information? > > When I tested the two versions of Calcite, it was in this method that the > behavior changed. : > TypeCoercionImpl.binaryComparisonCoercion(SqlCallBinding) > l243 : > if (kind.belongsTo(SqlKind.BINARY_COMPARISON)) { > final RelDataType commonType = commonTypeForBinaryComparison(type1, > type2); > if (null != commonType) { > coerced = coerceOperandsType(binding.getScope(), > binding.getCall(), commonType); > } > } > "commonType" is now non-null which causes the value 1002.2 to be truncated > to 1002 . > Is it normal that the value is truncated? Is there anything I can do to > prevent this from happening? > > On Wed, Apr 9, 2025 at 5:34 PM Mihai Budiu <[email protected]> wrote: > > > the result depends on the type system used, in particular the max > > precision and scale supported > > > > You need to tell us what they are in your case > > > > ________________________________ > > From: Alessandro Solimando <[email protected]> > > Sent: Wednesday, April 9, 2025 7:58:35 AM > > To: [email protected] <[email protected]> > > Subject: Re: Behavior on leastRestrictive RelDataType > > > > Hi Sylvain, > > assuming the type of "customer.c_acctbal" is "DECIMAL(1000, 0)", I think > > the rewrite in 1.39 is correct, the result might have changed from 1.37 > but > > I feel that this is due to a bugfix. > > > > I have quickly verified with Postgres and it seems to agree on what we do > > here (that is, truncating the decimal literal). > > > > What I can suggest is to write a test and use git bisect to find the > commit > > that changed the behaviour and we see from there. > > > > Best regards, > > Alessandro > > > > > > On Wed, 9 Apr 2025 at 15:24, Sylvain Gaultier > > <[email protected]> wrote: > > > > > Hi, > > > Could you please provide me with information on the following behavior? > > > I'm working with the official 1.39 release. > > > I have an operation in Calcite that no longer provides the same result > > (vs. > > > 1.37). > > > It's a simple query based on the TPCH model: "SELECT c.c_name FROM > > customer > > > c WHERE c.c_acctbal >= 1002.2" > > > > > > In TypeCoercionImpl.binaryComparisonCoercion(SqlCallBinding) > > > , for the comparison "c.c_acctbal >= 1002.2", we have two RelDataTypes > > > corresponding to the two arguments: DECIMAL(1000,0) and DECIMAL(5.1). > > > > > > In the 1.39 version, the > > > CteTypeFactoryImpl.leastRestrictive(List<RelDataType>) method considers > > the > > > least restrictive version to be DECIMAL(1000,0). > > > So we keep this version and end up with > > > `C`.`c_acctbal` >= CAST(1002.2 AS DECIMAL(1000, 0)) > > > > > > which is not the desired result. > > > (I saw that there was this ticket CALCITE-6913 since the release but I > > > don't think that will fix this problem ) > > > > > > Thanks for your help. > > > > > >
