Wrong result for cast expr when value exceeds range

[guo-shaoge]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

drop table if exists test.t1;
create table test.t1(c1 float, c2 double);
insert into test.t1 values(999.99, 999.99);
select cast(test.t1.c1 as decimal(4, 1)) from test.t1;

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

mysql> select cast(test.t1.c1 as decimal(4, 1)) from test.t1;
+-----------------------------------+
| cast(test.t1.c1 as decimal(4, 1)) |
+-----------------------------------+
|                             999.9 |
+-----------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+----------------------------------------------------------------------------+
| Level   | Code | Message                                                                    |
+---------+------+----------------------------------------------------------------------------+
| Warning | 1264 | Out of range value for column 'cast(test.t1.c1 as decimal(4, 1))' at row 1 |
+---------+------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

3. What did you see instead (Required)

mysql> select cast(test.t1.c1 as decimal(4, 1)) from test.t1;
+-----------------------------------+
| cast(test.t1.c1 as decimal(4, 1)) |
+-----------------------------------+
|                            1000.0 |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

4. What is your TiDB version? (Required)

mysql> select tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()
                          |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v5.5.0-alpha-146-g7fd298d916
Edition: Community
Git Commit Hash: 7fd298d916c30ed0060db3581be58b448325819e
Git Branch: master
UTC Build Time: 2022-02-08 06:57:26
GoVersion: go1.16.4
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[guo-shaoge]

Also exists for decimal to decimal:

drop table if exists test.t1;
create table test.t1(c1 decimal(6, 4));
insert into test.t1 values(99.9999);
select cast(test.t1.c1 as decimal(5, 3)) from test.t1;

tidb:

mysql> select cast(test.t1.c1 as decimal(5, 3)) from test.t1;
+-----------------------------------+
| cast(test.t1.c1 as decimal(5, 3)) |
+-----------------------------------+
|                           100.000 |
+-----------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select version();
+------------------------------------------+
| version()                                |
+------------------------------------------+
| 5.7.25-TiDB-v5.5.0-alpha-146-g7fd298d916 |
+------------------------------------------+
1 row in set (0.00 sec)

mysql:

mysql> select cast(test.t1.c1 as decimal(5, 3)) from test.t1;
+-----------------------------------+
| cast(test.t1.c1 as decimal(5, 3)) |
+-----------------------------------+
|                            99.999 |
+-----------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.23    |
+-----------+
1 row in set (0.00 sec)

[gengliqi]

This bug has existed for a long time. The oldest version I have tested is v3.0.(Maybe this bug exists on all versions)
So change the severity to major.

[gengliqi]

/severity major

[gengliqi]

I wasted some time comparing the round function implementation(a little bit complex) between tidb and MySQL.
I think the bug isn't here.

Finally, I found the bug after comparing the code between https://github.com/mysql/mysql-server/blob/0cd98bdf981583a1cf4cb526581fc16e23bb839b/sql/item_func.cc#L1725-L1751 and

tidb/types/datum.go

Lines 1426 to 1459 in e130e52

	func ProduceDecWithSpecifiedTp(dec *MyDecimal, tp *FieldType, sc *stmtctx.StatementContext) (_ *MyDecimal, err error) {
	flen, decimal := tp.Flen, tp.Decimal
	if flen != UnspecifiedLength && decimal != UnspecifiedLength {
	if flen < decimal {
	return nil, ErrMBiggerThanD.GenWithStackByArgs("")
	}
	prec, frac := dec.PrecisionAndFrac()
	if !dec.IsZero() && prec-frac > flen-decimal {
	dec = NewMaxOrMinDec(dec.IsNegative(), flen, decimal)
	// select (cast 111 as decimal(1)) causes a warning in MySQL.
	err = ErrOverflow.GenWithStackByArgs("DECIMAL", fmt.Sprintf("(%d, %d)", flen, decimal))
	} else if frac != decimal {
	old := *dec
	err = dec.Round(dec, decimal, ModeHalfEven)
	if err != nil {
	return nil, err
	}
	if !old.IsZero() && frac > decimal && dec.Compare(&old) != 0 {
	sc.AppendWarning(ErrTruncatedWrongVal.GenWithStackByArgs("DECIMAL", &old))
	err = nil
	}
	}
	}
	if ErrOverflow.Equal(err) {
	// TODO: warnErr need to be ErrWarnDataOutOfRange
	err = sc.HandleOverflow(err, err)
	}
	unsigned := mysql.HasUnsignedFlag(tp.Flag)
	if unsigned && dec.IsNegative() {
	dec = dec.FromUint(0)
	}
	return dec, err
	}

.

The root cause is that the judgment of digit length should be after calling round function, not before because the decimal may have a longer digit length after calling round.