Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Do
-
1.1.5
-
None
-
CentOS 7.4 ; MCS Single-Server
Description
Select math on cast as decimal returns value with significant deviation with enabled infinidb_use_decimal_scale
With disabled infinidb_use_decimal_scale, the query below returns
52.68389900 from cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
52.6838986 is the expected value
Then when try to improve precision and enable mcs decimal math , the precision become worst with the default decimal_scale=8 or set smaller or bigger one.
| infinidb_use_decimal_scale | decimal_scale | cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100 |
| 0 | infinidb_use_decimal_scale=0 | 52.68389900 |
| 1 | infinidb_decimal_scale=8 | 52.68312200 |
| 1 | infinidb_decimal_scale=7 | 52.68386000 |
| 1 | infinidb_decimal_scale=9 | 52.675646200 |
query61 from tpcds suite, run against tpcds_1 database loaded with scale factor 1
|
 |
select promotions,total,cast(promotions as decimal(15,4))/cast(total as decimal(15,4))*100
|
from
|
(select sum(ss_ext_sales_price) promotions,"a" as j
|
from store_sales
|
,store
|
,promotion
|
,date_dim
|
,customer
|
,customer_address
|
,item
|
where ss_sold_date_sk = d_date_sk
|
and ss_store_sk = s_store_sk
|
and ss_promo_sk = p_promo_sk
|
and ss_customer_sk= c_customer_sk
|
and ca_address_sk = c_current_addr_sk
|
and ss_item_sk = i_item_sk
|
and ca_gmt_offset = -5
|
and i_category = 'Jewelry'
|
and (p_channel_dmail = 'Y' or p_channel_email = 'Y' or p_channel_tv = 'Y')
|
and s_gmt_offset = -5
|
and d_year = 1998
|
and d_moy = 11) promotional_sales,
|
(select sum(ss_ext_sales_price) total,"a" as j
|
from store_sales
|
,store
|
,date_dim
|
,customer
|
,customer_address
|
,item
|
where ss_sold_date_sk = d_date_sk
|
and ss_store_sk = s_store_sk
|
and ss_customer_sk= c_customer_sk
|
and ca_address_sk = c_current_addr_sk
|
and ss_item_sk = i_item_sk
|
and ca_gmt_offset = -5
|
and i_category = 'Jewelry'
|
and s_gmt_offset = -5
|
and d_year = 1998
|
and d_moy = 11) all_sales
|
where promotional_sales.j = all_sales.j
|
order by promotions, total
|
limit 100;
|
|
*no* further _formatting_ is done here
|