[CALCITE-5860] Decimal type conversion missing scale

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.34.0
• Component/s: core
• Labels: pull-request-available
• Assignee: Pengfei Zhan
• Reporter: Guoliang Sun
• Created: 19/Jul/23 03:54
• Updated: 30/Dec/24 03:38

Description

Take the following SQL as an example

SELECT CAST(((2.0) / SQRT(3.0)) AS DECIMAL(18, 0)) * SQRT(3.0) 

The result of the SQL calculation should be SQRT(3.0).However, the actual result is 2.0, which is not meet expectations.

 

The following is the code generated by Janino

public Object[] apply(Object root0) {
  final java.math.BigDecimal literal_value = new java.math.BigDecimal(
    "2.0");
  final java.math.BigDecimal literal_value0 = new java.math.BigDecimal(
    "3.0");
  final java.math.BigDecimal literal_value1 = new java.math.BigDecimal(
    "0.5");
  final double method_name_call_value = org.apache.calcite.runtime.SqlFunctions.power(literal_value0, literal_value1);
  final java.math.BigDecimal cast_value = new java.math.BigDecimal(
    literal_value.doubleValue() / method_name_call_value);
  return new Object[] {
      cast_value == null ? 0.0D : cast_value.doubleValue() * method_name_call_value};
} 

We can see ((2.0) / SQRT(3.0)) AS DECIMAL(18, 0) lost the scale.

Issue Links

• Is contained by: CALCITE-6322 Casts to DECIMAL types are ignored
• is related to: CALCITE-6073 Documentation does not specify CAST behavior
• is related to: CALCITE-6078 Explicit cast to DECIMAL types do not check for overflow
• links to: GitHub Pull Request #3326