Re: Storing RexLiteral as BigDecimal values
I think that the problems you raise are solvable. * I want to point out that in the current Calcite parser any literal with exponent (e.g., 5e0) is parsed as an approximate numeric literal, i.e., a literal with type Double. That's the APPROX_NUMERIC_LITERAL grammar production. The actual value, however, is encoded as a BigDecimal in the SqlLiteral representation. So 5.0 is DECIMAL(2,1), while 5.0e0 is DOUBLE. * There are two distinct problems with FP values: parsing and unparsing. * Parsing: all languages that support double literals have the parsing problem, so Calcite can solve it in the same way as e.g., Java. The internal representation and the literal representation as a string may have different values. * Unparsing: since Calcite is often used as a source-to-source translator, when unparsing the resulting string representations may be different from the internal representations. A constant expression with type DOUBLE produced by constant folding is serialized as a literal. toSqlString() could emit a BINARY literal and a custom function that deserializes the literal into a DOUBLE. Ugly, but should always be correct. However, the unparsing problem already exists, and not just for FP values: https://issues.apache.org/jira/browse/CALCITE-5987: SqlImplementor loses type information for literal. Mihai From: Julian Hyde Sent: Wednesday, January 31, 2024 3:46 PM To: dev@calcite.apache.org Subject: Re: Storing RexLiteral as BigDecimal values Mihai, I accept your point that literals may have values that are binary floating point. (As we discussed, they are impossible to create via SQL, but may be created via constant reduction.) But I still have a concern that developers will accidentally create numeric literals with double values. Due to how Java rounds binary floating point values when it prints them, even with exhaustive testing we may not notice that literals are not exact. How to we protect against this? One idea is to create a class BigBinary (as BigDecimal, but exponents that are powers of two rather than of ten, and also allowing IEEE special values NaN, +Inf, -Inf, -0). Internally it could contain a double, or be extended to support binary floating point values with more than 64 bits. Julian > On Jan 30, 2024, at 4:13 PM, Mihai Budiu wrote: > > Highly relevant discussion: https://issues.apache.org/jira/browse/CALCITE-2067 > > This change would indeed enable representing other DOUBLE values which cannot > be represented by BigDecimal, such as NaN, Infinities, and -0. It looks like > the evaluator does not reduce such expressions. > > That issue also had a PR, which was rejected at the time, but I think a new > PR would look relatively similar. Julian's objection at the time was that > this changes the SQL language; however, this doesn't change the syntax of the > language, just the representation that the evaluator uses internally. This > would enable changes in the syntax too, but they can be done separately if > desired. > > Mihai > > > From: Mihai Budiu > Sent: Tuesday, January 30, 2024 4:00 PM > To: dev@calcite.apache.org > Subject: Re: Storing RexLiteral as BigDecimal values > > > * > Regarding [1], it looks like the power function implementations with DECIMAL > arguments return DOUBLE. Where the BigDecimal Java type has custom functions > that have potentially higher accuracy than FP versions, using them could be > beneficial. Related to this, Calcite uses POWER to represent SQRT, but I > noticed that SQRT gives more precise results. However, the Java > implementation of Math.pow is very careful, and seems to be optimized for the > case of integer arguments: > https://docs.oracle.com/javase/8/docs/api/java/lang/Math.html#pow-double-double- > > > * This is what POWER looks like in the SqlStdOperatorTable, which suggests > that even the type checker assumes that this function always returns DOUBLE: > > /** The {@code POWER(numeric, numeric)} function. > * > * The return type is always {@code DOUBLE} since we don't know > * what the result type will be by just looking at the operand types. For > * example {@code POWER(INTEGER, INTEGER)} can return a non-INTEGER if the > * second operand is negative. > */ > public static final SqlBasicFunction POWER = > SqlBasicFunction.create("POWER", > ReturnTypes.DOUBLE_NULLABLE, > OperandTypes.NUMERIC_NUMERIC, > SqlFunctionCategory.NUMERIC); > > > * Using a string representation for double literals, as you propose, still > introduces rounding problems, when the Double value is converted to a string. > So I think this will just replace one problem with another. > > > * >
Re: Storing RexLiteral as BigDecimal values
Mihai, I accept your point that literals may have values that are binary floating point. (As we discussed, they are impossible to create via SQL, but may be created via constant reduction.) But I still have a concern that developers will accidentally create numeric literals with double values. Due to how Java rounds binary floating point values when it prints them, even with exhaustive testing we may not notice that literals are not exact. How to we protect against this? One idea is to create a class BigBinary (as BigDecimal, but exponents that are powers of two rather than of ten, and also allowing IEEE special values NaN, +Inf, -Inf, -0). Internally it could contain a double, or be extended to support binary floating point values with more than 64 bits. Julian > On Jan 30, 2024, at 4:13 PM, Mihai Budiu wrote: > > Highly relevant discussion: https://issues.apache.org/jira/browse/CALCITE-2067 > > This change would indeed enable representing other DOUBLE values which cannot > be represented by BigDecimal, such as NaN, Infinities, and -0. It looks like > the evaluator does not reduce such expressions. > > That issue also had a PR, which was rejected at the time, but I think a new > PR would look relatively similar. Julian's objection at the time was that > this changes the SQL language; however, this doesn't change the syntax of the > language, just the representation that the evaluator uses internally. This > would enable changes in the syntax too, but they can be done separately if > desired. > > Mihai > > > From: Mihai Budiu > Sent: Tuesday, January 30, 2024 4:00 PM > To: dev@calcite.apache.org > Subject: Re: Storing RexLiteral as BigDecimal values > > > * > Regarding [1], it looks like the power function implementations with DECIMAL > arguments return DOUBLE. Where the BigDecimal Java type has custom functions > that have potentially higher accuracy than FP versions, using them could be > beneficial. Related to this, Calcite uses POWER to represent SQRT, but I > noticed that SQRT gives more precise results. However, the Java > implementation of Math.pow is very careful, and seems to be optimized for the > case of integer arguments: > https://docs.oracle.com/javase/8/docs/api/java/lang/Math.html#pow-double-double- > > > * This is what POWER looks like in the SqlStdOperatorTable, which suggests > that even the type checker assumes that this function always returns DOUBLE: > > /** The {@code POWER(numeric, numeric)} function. > * > * The return type is always {@code DOUBLE} since we don't know > * what the result type will be by just looking at the operand types. For > * example {@code POWER(INTEGER, INTEGER)} can return a non-INTEGER if the > * second operand is negative. > */ > public static final SqlBasicFunction POWER = > SqlBasicFunction.create("POWER", > ReturnTypes.DOUBLE_NULLABLE, > OperandTypes.NUMERIC_NUMERIC, > SqlFunctionCategory.NUMERIC); > > > * Using a string representation for double literals, as you propose, still > introduces rounding problems, when the Double value is converted to a string. > So I think this will just replace one problem with another. > > > * > Finally, regarding the subject of this email, the right solution seems to > allow RexLiteral with a DOUBLE type (and similarly for REAL) to store values > either as BigDecimal, which is suitable when the literals come from the > parser, or as Double, which is suitable when values come from the evaluator. > I plan to file a JIRA issue to enable this feature. > > Thank you, > Mihai > > > From: Julian Hyde > Sent: Tuesday, January 30, 2024 3:42 PM > To: dev@calcite.apache.org > Subject: Re: Storing RexLiteral as BigDecimal values > > 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 wrote: >> >> Well, irrespective of the type of the arguments, the POWER function returns >> a double value. >> >>
Re: Storing RexLiteral as BigDecimal values
I think we should start a new thread. Mihai From: Stephen Carlin Sent: Tuesday, January 30, 2024 5:19 PM To: dev@calcite.apache.org Subject: Re: Storing RexLiteral as BigDecimal values I don't know if this is hijacking or adding to the discussion, but figured I'd write here. I'm currently implementing Calcite on my database, but I'm actually running into a problem where my db would rather have exact types on some of the lower integers. LIke, if we have the value 38, I need this to be set as a TINYINT, not an INT. I guess Calcite doesn't really care if things are TINYINT or INT because of it treats NUMERIC classes the same, but my database does care. One example is that my "=" function needs both parameters to be TINYINT or INT (or the other integer values), and spits up otherwise.I currently have a crazy type coercion rule to get around this limitation. I know there are existing type coercions built in for the "=" function and others, but it doesn't cover all my cases and it seems too hacky and piecemeal to implement it that way. Would this be something I should add as some kind of Dialect? Or is the Calcite community against these type of additions. On Tue, Jan 30, 2024 at 4:13 PM Mihai Budiu wrote: > Highly relevant discussion: > https://issues.apache.org/jira/browse/CALCITE-2067 > > This change would indeed enable representing other DOUBLE values which > cannot be represented by BigDecimal, such as NaN, Infinities, and -0. It > looks like the evaluator does not reduce such expressions. > > That issue also had a PR, which was rejected at the time, but I think a > new PR would look relatively similar. Julian's objection at the time was > that this changes the SQL language; however, this doesn't change the syntax > of the language, just the representation that the evaluator uses > internally. This would enable changes in the syntax too, but they can be > done separately if desired. > > Mihai > > ________ > From: Mihai Budiu > Sent: Tuesday, January 30, 2024 4:00 PM > To: dev@calcite.apache.org > Subject: Re: Storing RexLiteral as BigDecimal values > > > * > Regarding [1], it looks like the power function implementations with > DECIMAL arguments return DOUBLE. Where the BigDecimal Java type has custom > functions that have potentially higher accuracy than FP versions, using > them could be beneficial. Related to this, Calcite uses POWER to represent > SQRT, but I noticed that SQRT gives more precise results. However, the Java > implementation of Math.pow is very careful, and seems to be optimized for > the case of integer arguments: > https://docs.oracle.com/javase/8/docs/api/java/lang/Math.html#pow-double-double- > > > * This is what POWER looks like in the SqlStdOperatorTable, which > suggests that even the type checker assumes that this function always > returns DOUBLE: > > /** The {@code POWER(numeric, numeric)} function. >* >* The return type is always {@code DOUBLE} since we don't know >* what the result type will be by just looking at the operand types. For >* example {@code POWER(INTEGER, INTEGER)} can return a non-INTEGER if > the >* second operand is negative. >*/ > public static final SqlBasicFunction POWER = > SqlBasicFunction.create("POWER", > ReturnTypes.DOUBLE_NULLABLE, > OperandTypes.NUMERIC_NUMERIC, > SqlFunctionCategory.NUMERIC); > > > * Using a string representation for double literals, as you propose, > still introduces rounding problems, when the Double value is converted to a > string. So I think this will just replace one problem with another. > > > * > Finally, regarding the subject of this email, the right solution seems to > allow RexLiteral with a DOUBLE type (and similarly for REAL) to store > values either as BigDecimal, which is suitable when the literals come from > the parser, or as Double, which is suitable when values come from the > evaluator. I plan to file a JIRA issue to enable this feature. > > Thank you, > Mihai > > > From: Julian Hyde > Sent: Tuesday, January 30, 2024 3:42 PM > To: dev@calcite.apache.org > Subject: Re: Storing RexLiteral as BigDecimal values > > 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 di
Re: Storing RexLiteral as BigDecimal values
I don't know if this is hijacking or adding to the discussion, but figured I'd write here. I'm currently implementing Calcite on my database, but I'm actually running into a problem where my db would rather have exact types on some of the lower integers. LIke, if we have the value 38, I need this to be set as a TINYINT, not an INT. I guess Calcite doesn't really care if things are TINYINT or INT because of it treats NUMERIC classes the same, but my database does care. One example is that my "=" function needs both parameters to be TINYINT or INT (or the other integer values), and spits up otherwise.I currently have a crazy type coercion rule to get around this limitation. I know there are existing type coercions built in for the "=" function and others, but it doesn't cover all my cases and it seems too hacky and piecemeal to implement it that way. Would this be something I should add as some kind of Dialect? Or is the Calcite community against these type of additions. On Tue, Jan 30, 2024 at 4:13 PM Mihai Budiu wrote: > Highly relevant discussion: > https://issues.apache.org/jira/browse/CALCITE-2067 > > This change would indeed enable representing other DOUBLE values which > cannot be represented by BigDecimal, such as NaN, Infinities, and -0. It > looks like the evaluator does not reduce such expressions. > > That issue also had a PR, which was rejected at the time, but I think a > new PR would look relatively similar. Julian's objection at the time was > that this changes the SQL language; however, this doesn't change the syntax > of the language, just the representation that the evaluator uses > internally. This would enable changes in the syntax too, but they can be > done separately if desired. > > Mihai > > ________ > From: Mihai Budiu > Sent: Tuesday, January 30, 2024 4:00 PM > To: dev@calcite.apache.org > Subject: Re: Storing RexLiteral as BigDecimal values > > > * > Regarding [1], it looks like the power function implementations with > DECIMAL arguments return DOUBLE. Where the BigDecimal Java type has custom > functions that have potentially higher accuracy than FP versions, using > them could be beneficial. Related to this, Calcite uses POWER to represent > SQRT, but I noticed that SQRT gives more precise results. However, the Java > implementation of Math.pow is very careful, and seems to be optimized for > the case of integer arguments: > https://docs.oracle.com/javase/8/docs/api/java/lang/Math.html#pow-double-double- > > > * This is what POWER looks like in the SqlStdOperatorTable, which > suggests that even the type checker assumes that this function always > returns DOUBLE: > > /** The {@code POWER(numeric, numeric)} function. >* >* The return type is always {@code DOUBLE} since we don't know >* what the result type will be by just looking at the operand types. For >* example {@code POWER(INTEGER, INTEGER)} can return a non-INTEGER if > the >* second operand is negative. >*/ > public static final SqlBasicFunction POWER = > SqlBasicFunction.create("POWER", > ReturnTypes.DOUBLE_NULLABLE, > OperandTypes.NUMERIC_NUMERIC, > SqlFunctionCategory.NUMERIC); > > > * Using a string representation for double literals, as you propose, > still introduces rounding problems, when the Double value is converted to a > string. So I think this will just replace one problem with another. > > > * > Finally, regarding the subject of this email, the right solution seems to > allow RexLiteral with a DOUBLE type (and similarly for REAL) to store > values either as BigDecimal, which is suitable when the literals come from > the parser, or as Double, which is suitable when values come from the > evaluator. I plan to file a JIRA issue to enable this feature. > > Thank you, > Mihai > > > From: Julian Hyde > Sent: Tuesday, January 30, 2024 3:42 PM > To: dev@calcite.apache.org > Subject: Re: Storing RexLiteral as BigDecimal values > > 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://iss
Re: Storing RexLiteral as BigDecimal values
Highly relevant discussion: https://issues.apache.org/jira/browse/CALCITE-2067 This change would indeed enable representing other DOUBLE values which cannot be represented by BigDecimal, such as NaN, Infinities, and -0. It looks like the evaluator does not reduce such expressions. That issue also had a PR, which was rejected at the time, but I think a new PR would look relatively similar. Julian's objection at the time was that this changes the SQL language; however, this doesn't change the syntax of the language, just the representation that the evaluator uses internally. This would enable changes in the syntax too, but they can be done separately if desired. Mihai From: Mihai Budiu Sent: Tuesday, January 30, 2024 4:00 PM To: dev@calcite.apache.org Subject: Re: Storing RexLiteral as BigDecimal values * Regarding [1], it looks like the power function implementations with DECIMAL arguments return DOUBLE. Where the BigDecimal Java type has custom functions that have potentially higher accuracy than FP versions, using them could be beneficial. Related to this, Calcite uses POWER to represent SQRT, but I noticed that SQRT gives more precise results. However, the Java implementation of Math.pow is very careful, and seems to be optimized for the case of integer arguments: https://docs.oracle.com/javase/8/docs/api/java/lang/Math.html#pow-double-double- * This is what POWER looks like in the SqlStdOperatorTable, which suggests that even the type checker assumes that this function always returns DOUBLE: /** The {@code POWER(numeric, numeric)} function. * * The return type is always {@code DOUBLE} since we don't know * what the result type will be by just looking at the operand types. For * example {@code POWER(INTEGER, INTEGER)} can return a non-INTEGER if the * second operand is negative. */ public static final SqlBasicFunction POWER = SqlBasicFunction.create("POWER", ReturnTypes.DOUBLE_NULLABLE, OperandTypes.NUMERIC_NUMERIC, SqlFunctionCategory.NUMERIC); * Using a string representation for double literals, as you propose, still introduces rounding problems, when the Double value is converted to a string. So I think this will just replace one problem with another. * Finally, regarding the subject of this email, the right solution seems to allow RexLiteral with a DOUBLE type (and similarly for REAL) to store values either as BigDecimal, which is suitable when the literals come from the parser, or as Double, which is suitable when values come from the evaluator. I plan to file a JIRA issue to enable this feature. Thank you, Mihai From: Julian Hyde Sent: Tuesday, January 30, 2024 3:42 PM To: dev@calcite.apache.org Subject: Re: Storing RexLiteral as BigDecimal values 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 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 > Sent: Tuesday, January 30, 2024 3:03 PM > To: 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 wrote: >> >> e0 shows that these are DOUBLE values. >> Moreover, power returns a DOUBLE value. >> In FP the result is the wrong one, but tha
Re: Storing RexLiteral as BigDecimal values
* Regarding [1], it looks like the power function implementations with DECIMAL arguments return DOUBLE. Where the BigDecimal Java type has custom functions that have potentially higher accuracy than FP versions, using them could be beneficial. Related to this, Calcite uses POWER to represent SQRT, but I noticed that SQRT gives more precise results. However, the Java implementation of Math.pow is very careful, and seems to be optimized for the case of integer arguments: https://docs.oracle.com/javase/8/docs/api/java/lang/Math.html#pow-double-double- * This is what POWER looks like in the SqlStdOperatorTable, which suggests that even the type checker assumes that this function always returns DOUBLE: /** The {@code POWER(numeric, numeric)} function. * * The return type is always {@code DOUBLE} since we don't know * what the result type will be by just looking at the operand types. For * example {@code POWER(INTEGER, INTEGER)} can return a non-INTEGER if the * second operand is negative. */ public static final SqlBasicFunction POWER = SqlBasicFunction.create("POWER", ReturnTypes.DOUBLE_NULLABLE, OperandTypes.NUMERIC_NUMERIC, SqlFunctionCategory.NUMERIC); * Using a string representation for double literals, as you propose, still introduces rounding problems, when the Double value is converted to a string. So I think this will just replace one problem with another. * Finally, regarding the subject of this email, the right solution seems to allow RexLiteral with a DOUBLE type (and similarly for REAL) to store values either as BigDecimal, which is suitable when the literals come from the parser, or as Double, which is suitable when values come from the evaluator. I plan to file a JIRA issue to enable this feature. Thank you, Mihai From: Julian Hyde Sent: Tuesday, January 30, 2024 3:42 PM To: dev@calcite.apache.org Subject: Re: Storing RexLiteral as BigDecimal values 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 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 > Sent: Tuesday, January 30, 2024 3:03 PM > To: 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 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 >> Sent: Tuesday, January 30, 2024 2:50 PM >> To: 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 wrote: >>> >>> I am evaluating this expression: SELECT power(1004.3e0, 2e0) >>> The result in Java, or Postgres, when formatted as a string, is >>> 1008618.48 >>> The result produced by the Calcite simplificat
Re: Storing RexLiteral as BigDecimal values
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 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 > Sent: Tuesday, January 30, 2024 3:03 PM > To: 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 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 >> Sent: Tuesday, January 30, 2024 2:50 PM >> To: 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 wrote: >>> >>> I am evaluating this expression: SELECT power(1004.3e0, 2e0) >>> The result in Java, or Postgres, when formatted as a string, is >>> 1008618.48 >>> 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 >>> Sent: Tuesday, January 30, 2024 2:40 PM >>> To: 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 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 >>> >> >
Re: Storing RexLiteral as BigDecimal values
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 Sent: Tuesday, January 30, 2024 3:03 PM To: 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 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 > Sent: Tuesday, January 30, 2024 2:50 PM > To: 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 wrote: >> >> I am evaluating this expression: SELECT power(1004.3e0, 2e0) >> The result in Java, or Postgres, when formatted as a string, is >> 1008618.48 >> 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 >> Sent: Tuesday, January 30, 2024 2:40 PM >> To: 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 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 >> >
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 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 > Sent: Tuesday, January 30, 2024 2:50 PM > To: 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 wrote: >> >> I am evaluating this expression: SELECT power(1004.3e0, 2e0) >> The result in Java, or Postgres, when formatted as a string, is >> 1008618.48 >> 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 >> Sent: Tuesday, January 30, 2024 2:40 PM >> To: 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 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 >> >
Re: Storing RexLiteral as BigDecimal values
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 Sent: Tuesday, January 30, 2024 2:50 PM To: 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 wrote: > > I am evaluating this expression: SELECT power(1004.3e0, 2e0) > The result in Java, or Postgres, when formatted as a string, is > 1008618.48 > 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 > Sent: Tuesday, January 30, 2024 2:40 PM > To: 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 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 >
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 wrote: > > I am evaluating this expression: SELECT power(1004.3e0, 2e0) > The result in Java, or Postgres, when formatted as a string, is > 1008618.48 > 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 > Sent: Tuesday, January 30, 2024 2:40 PM > To: 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 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 >
Re: Storing RexLiteral as BigDecimal values
I am evaluating this expression: SELECT power(1004.3e0, 2e0) The result in Java, or Postgres, when formatted as a string, is 1008618.48 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 Sent: Tuesday, January 30, 2024 2:40 PM To: 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 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
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 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