In the case of POWER, Postgres has both a decimal and double version[1], and Calcite should do the same.
However, I take your point that if the function returns a double, then constant-reduction should create a double literal. I am concerned that developers will accidentally create double literals, when double literals are not possible in SQL (until we implement DOUBLE ‘3.14’ syntax, as discussed in [2]). Until then, is it better to store the Rex equivalent of CAST(‘3.14’ AS DOUBLE)? Julian [1] https://www.postgresql.org/docs/8.2/functions-math.html [2] https://issues.apache.org/jira/browse/CALCITE-6052 > On Jan 30, 2024, at 3:21 PM, Mihai Budiu <mbu...@gmail.com> wrote: > > Well, irrespective of the type of the arguments, the POWER function returns a > double value. > > These are the 4 implementations in SqlFunctions: > > public static double power(double b0, double b1) { > return Math.pow(b0, b1); > } > > public static double power(double b0, BigDecimal b1) { > return Math.pow(b0, b1.doubleValue()); > } > > public static double power(BigDecimal b0, double b1) { > return Math.pow(b0.doubleValue(), b1); > } > > public static double power(BigDecimal b0, BigDecimal b1) { > return Math.pow(b0.doubleValue(), b1.doubleValue()); > } > > The problem is that, for the query below, the optimizer will then convert the > double result of power into a BigDecimal, rounding it in the process. > > Mihai > ________________________________ > From: Julian Hyde <jhyde.apa...@gmail.com> > Sent: Tuesday, January 30, 2024 3:03 PM > To: dev@calcite.apache.org <dev@calcite.apache.org> > Subject: Re: Storing RexLiteral as BigDecimal values > > It’s surprising to me that 1004.3e0 should have type DOUBLE or REAL; I would > expect it to have type DECIMAL(5, 1), where it can be represented exactly. > >> On Jan 30, 2024, at 2:53 PM, Mihai Budiu <mbu...@gmail.com> wrote: >> >> e0 shows that these are DOUBLE values. >> Moreover, power returns a DOUBLE value. >> In FP the result is the wrong one, but that's the semantics of the power >> function in FP. >> >> Mihai >> ________________________________ >> From: Julian Hyde <jhyde.apa...@gmail.com> >> Sent: Tuesday, January 30, 2024 2:50 PM >> To: dev@calcite.apache.org <dev@calcite.apache.org> >> Subject: Re: Storing RexLiteral as BigDecimal values >> >> The inputs are decimals, and the correct answer is 1008618.49, also a >> decimal, and cannot be exactly represented as a binary floating point. I’m >> not sure why in this case you want a binary floating point. >> >>> On Jan 30, 2024, at 2:46 PM, Mihai Budiu <mbu...@gmail.com> wrote: >>> >>> I am evaluating this expression: SELECT power(1004.3e0, 2e0) >>> The result in Java, or Postgres, when formatted as a string, is >>> 1008618.4899999999 >>> The result produced by the Calcite simplification code is 1008618.49 >>> The simplification code can produce RexLiterals - that's where this would >>> be useful. >>> This rounding error is not really necessary. >>> >>> Mihai >>> ________________________________ >>> From: Julian Hyde <jhyde.apa...@gmail.com> >>> Sent: Tuesday, January 30, 2024 2:40 PM >>> To: dev@calcite.apache.org <dev@calcite.apache.org> >>> Subject: Re: Storing RexLiteral as BigDecimal values >>> >>> Can you give a scenario where a RexLiteral should have a double value? >>> >>>> On Jan 30, 2024, at 2:36 PM, Mihai Budiu <mbu...@gmail.com> wrote: >>>> >>>> Hello, >>>> >>>> I have a question about the representation of RexLiteral values. >>>> Currently DOUBLE-valued literals are represented using a BigDecimal. >>>> This causes small rounding errors, introduced in the RexBuilder.clean() >>>> function. >>>> This causes FP expressions that are evaluated at compilation-time to >>>> produce results that are slightly off from the same expressions that may >>>> be evaluated at runtime, for no real reason. For example, I am running >>>> some Postgres tests for FP values, and they fail because of this small >>>> difference. >>>> >>>> I know that FP values cannot be compared for equality, and tests are >>>> supposed to have some slack, but I think that this particular rounding >>>> error is not necessary. >>>> >>>> Why can't RexLiteral actually store a Double value internally when the >>>> type is Double? >>>> Is this a bug or is there a deeper reason for this representation? >>>> If it's a bug I can file a JIRA issue and probably fix it. >>>> >>>> Thank you, >>>> Mihai >>> >> >
