Incompatible with MySQL 8.0 about decimal truncate

[lonng]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?
   If possible, provide a recipe for reproducing the error.

➜  ~ tidiff select 163585273409850809968011255877234629633094624779352518618441553657572897147135360553499480433030391348391690719534799010900398570378470003899837991878694011218651963309356255321688614631601533479175603508137169276827637180801063442877431907302534035121352827985983573079532645288562448964138247704595392364544
MySQL(127.0.0.1:3306)> select 163585273409850809968011255877234629633094624779352518618441553657572897147135360553499480433030391348391690719534799010900398570378470003899837991878694011218651963309356255321688614631601533479175603508137169276827637180801063442877431907302534035121352827985983573079532645288562448964138247704595392364544
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 163585273409850809968011255877234629633094624779352518618441553657572897147135360553499480433030391348391690719534799010900398570378470003899837991878694011218651963309356255321688614631601533479175603508137169276827637180801063442877431907302534035121352827985983573079532645288562448964138247704595392364544 |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 99999999999999999999999999999999999999999999999999999999999999999                                                                                                                                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

TiDB(127.0.0.1:4000)> select 163585273409850809968011255877234629633094624779352518618441553657572897147135360553499480433030391348391690719534799010900398570378470003899837991878694011218651963309356255321688614631601533479175603508137169276827637180801063442877431907302534035121352827985983573079532645288562448964138247704595392364544
Error 1105: line 1 column 316 near ""decimal literal: [types:1690]%s value is out of range in '%s' (total length 316)

2. What did you expect to see?

The same as MySQL 8.0.

3. What did you see instead?

See above.

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

TiDB(127.0.0.1:4000)> select tidb_version()
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                                            |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v2.1.8-24-g7b36cdfb6-dirty
Git Commit Hash: 7b36cdfb6e8a4c279fc360ed79cf1c499b553688
Git Branch: failpoint-2.1
UTC Build Time: 2019-04-30 08:26:17
GoVersion: go version go1.12 darwin/amd64
Race Enabled: false
TiKV Min Version: 2.1.0-alpha.1-ff3dd160846b7d1aed9079c389fc188f7f5ea13e
Check Table Before Drop: false |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.007 sec)

[yangwenmai]

in github.com/pingcap/parser/yy_parser.go#214

// TODO: toDecimal maybe out of range still.
// This kind of error should be throw to higher level, because truncated data maybe legal.
// For example, this SQL returns error:
// create table test (id decimal(30, 0));
// insert into test values(123456789012345678901234567890123094839045793405723406801943850);
// While this SQL:
// select 1234567890123456789012345678901230948390457934057234068019438509023041874359081325875128590860234789847359871045943057;
// get value 99999999999999999999999999999999999999999999999999999999999999999

so We must be modify the parser?

[lonng]

@yangwenmai Yes.

[ghost]

I think this issue warrants a 'compatibility' flag, but not a bug. MySQL doesn't exactly have a better behavior here, since it just returns 99999999999999999999999999999999999999999999999999999999999999999 and a warning about truncation.

Here is confirmation it still exists in master:

select 163585273409850809968011255877234629633094624779352518618441553657572897147135360553499480433030391348391690719534799010900398570378470003899837991878694011218651963309356255321688614631601533479175603508137169276827637180801063442877431907302534035121352827985983573079532645288562448964138247704595392364544;

..

mysql> select 163585273409850809968011255877234629633094624779352518618441553657572897147135360553499480433030391348391690719534799010900398570378470003899837991878694011218651963309356255321688614631601533479175603508137169276827637180801063442877431907302534035121352827985983573079532645288562448964138247704595392364544;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 316 near "163585273409850809968011255877234629633094624779352518618441553657572897147135360553499480433030391348391690719534799010900398570378470003899837991878694011218651963309356255321688614631601533479175603508137169276827637180801063442877431907302534035121352827985983573079532645288562448964138247704595392364544"decimal literal: [types:1690]%s value is out of range in
mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-762-g77aecd4b2
Edition: Community
Git Commit Hash: 77aecd4b27e79a97215eb4fdd68f68f2ddf67d21
Git Branch: master
UTC Build Time: 2020-07-13 01:43:31
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[aytrack]

Maybe this error is also caused by the decimal truncate?
MySQL :

test > create table t1(a double, b double, c double as (a div b));
test> insert into t1 (a, b) values (6.528889484749926e+307,-1.5);
(1292, "Truncated incorrect DECIMAL value: '6.528889484749926'")

TiDB:

tidb > create table t1(a double, b double, c double as (a div b));
tidb > insert into t1 (a, b) values (6.528889484749926e+307,-1.5);
(1690, "%s value is out of range in '%s'")

select tidb_version()\G
***************************[ 1. row ]***************************
tidb_version() | Release Version: v4.0.10
Edition: Community
Git Commit Hash: dbade8cda4c5a329037746e171449e0a1dfdb8b3
Git Branch: heads/refs/tags/v4.0.10
UTC Build Time: 2021-01-15 02:59:27
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false