Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-6909

ClickHouse dialect should limit the Precision and Scale of the Decimal type to be within 76

    XMLWordPrintableJSON

Details

    • Improvement
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 1.39.0
    • 1.40.0
    • core

    Description

      Currently ClickHouse Dailect can not support accurate precision

      In ClickHouse:

      • P - precision. Valid range: [ 1 : 76 ]. 
      • S - scale. Valid range: [ 0 : P ]. 

      Depending on P parameter value Decimal(P, S) is a synonym for:

      • P from [ 1 : 9 ] - for Decimal32(S)
      • P from [ 10 : 18 ] - for Decimal64(S)
      • P from [ 19 : 38 ] - for Decimal128(S)
      • P from [ 39 : 76 ] - for Decimal256(S)

      we can refer to doc with: https://clickhouse.com/docs/sql-reference/data-types/decimal

       

      eg:select cast(\"product_id\" as decimal(22,20)) from \"product\"

      but transform to: SELECT CAST(`product_id` AS DECIMAL(19, 19)) FROM `foodmart`.`product`

       

      this should be transform to:  SELECT CAST(`product_id` AS DECIMAL(22, 20)) FROM `foodmart`.`product`

      because CK precision is from[1, 76] 

       

      user case: 

      if user want to cast to a decimal type such as :

      select cast('1.322131231313123311312322' as DECIMAL(30,24)) from system.numbers limit 1;

      should be a decimal number of 1.322131231313123311312322

      but currently  calcite ClickHouse dialect can only cast to:1.322131231313123311

      this would cause loss of precision.

       

      Attachments

        Issue Links

          Activity

            People

              xuzifu666 Yu Xu
              xuzifu666 Yu Xu
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: