Incompatible CAST x as DECIMAL()

[zhangysh1995]

Bug Report

The behavior of casting to decimal is documented at https://dev.mysql.com/doc/refman/5.7/en/fixed-point-types.html:

The maximum number of digits for DECIMAL is 65, but the actual range for a given DECIMAL column can be constrained by the precision or scale for a given column. When such a column is assigned a value with more digits following the decimal point than are permitted by the specified scale, the value is converted to that scale. (The precise behavior is operating system-specific, but generally the effect is truncation to the permissible number of digits.)

1. Minimal reproduce step (Required)

create table t1 (a double, b int);
insert into t1 values (1,-1), (0.001,0), (-1, 0);

-- should error                                                                                                  
select cast( (select sum(a) from t1) as decimal(65,65));

2. What did you expect to see? (Required)

mysql> select cast( (select sum(a) from t1) as decimal(65,65));
ERROR 1425 (42000): Too big scale 65 specified for column '(select sum(a) from t1)'. Maximum is 30.

3. What did you see instead (Required)

mysql> select cast( (select sum(a) from t1) as decimal(65,65));
+---------------------------------------------------------------------+
| cast( (select sum(a) from t1) as decimal(65,65))                    |
+---------------------------------------------------------------------+
| 0.00099999999999988990000000000000000000000000000000000000000000000 |
+---------------------------------------------------------------------+
1 row in set (0.01 sec)                       

4. What is your TiDB version? (Required)

mysql> select tidb_version() ;
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                              |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v4.0.0-beta.2-1211-g94704d0cc
Edition: Community
Git Commit Hash: 94704d0cc49343862fc6a59e59a531d38a463a4f
Git Branch: master
UTC Build Time: 2020-09-11 09:50:27
GoVersion: go1.15.1
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[sre-bot]

Integrity check:
RCA symptom trigger_condition affect_version fix_version fields are empty

Please comment /info to get template

[ti-srebot]

Please edit this comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA)

2. Symptom

3. All Trigger Conditions

4. Workaround (optional)

5. Affected versions

6. Fixed versions

[dyzsr]

/assign

[ti-srebot]

( FixedVersions AffectedVersions ) fields are empty.