[
https://issues.apache.org/jira/browse/CALCITE-6909?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17939186#comment-17939186
]
xiong duan commented on CALCITE-6909:
-------------------------------------
I have noticed that you actually intend to impose restrictions on the precision
and scale of the decimal type in ClickHouse.
The reason why the generated Decimal type has the maximum Precision of 19 is
due to the restrictions imposed by Calcite. You can refer to
RelDataTypeSystemImpl#getMaxPrecision and RelDataTypeSystemImpl#getMaxScale for
more details.
For the test cases, you can refer to
RelToSqlConverterTest#withPostgresqlModifiedDecimalTypeSystem to understand how
to implement custom Precision and Scale.
Regarding the Jira's summary, I prefer to "ClickHouse dialect should limit the
Precision and Scale of the Decimal type to be within 79"
> ClickHouse Dailect can not support accurate precision
> -----------------------------------------------------
>
> Key: CALCITE-6909
> URL: https://issues.apache.org/jira/browse/CALCITE-6909
> Project: Calcite
> Issue Type: Improvement
> Components: core
> Affects Versions: 1.39.0
> Reporter: Yu Xu
> Assignee: Yu Xu
> Priority: Major
> Labels: pull-request-available
> Fix For: 1.40.0
>
>
> 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, 79]
>
> *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.
>
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
